Atlassian uses cookies to improve your browsing experience, perform analytics and research, and conduct advertising. Accept all cookies to indicate that you agree to our use of cookies on your device. Atlassian cookies and tracking notice, (opens new window)
Create a new DB from template0: CREATE DATABASE new TEMPLATE template0;
Connect to the freshly created db (in my case, I used port-forwarding to access the pgbouncer service on localhost: kubectl port-forward svc/postgresql-lyw-pgbouncer 5432:5432 -n everest)
% psql -h localhost -p 5432 -U postgres -d new -W
Password:
psql: error: connection to server at "localhost" (::1), port 5432 failed: FATAL: bouncer config error
connection to server at "localhost" (::1), port 5432 failed: FATAL: SSL required
However, here are the workarounds how one could connect to this freshly created db:
connect to the db directly, so not via pgbouncer
use _crunchypgbouncer user instead of postgres user
patch the pg cluster to disable the exposeSuperusers field: kubectl -n everest patch pg/postgresql-lyw -p '{"spec":{"proxy":{"pgBouncer":{"exposeSuperusers":false}}}}' --type=merge. In case of Everest, the everest-operator will immediately reconcile the value back to the true value, however the patch triggers some kind of an update process that makes the new DB accessible. Each time a new DB is created from template0 this patch helps to get access to it:
% psql -h localhost -p 5432 -U postgres -d new -W
Password:
psql (17.2, server 16.4 - Percona Distribution)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: none)
Type "help" for help.
new=# exit;
Note: There is no problem with connecting to the DBs created from template1 (which is used by default when you just CREATE DATABASE new). All of the users:
postgres
_crunchypgbouncer
a user created manually (CREATE USER new_user WITH PASSWORD 'password')
Problem:
Can't connect to a DB created from
template0STR:
Connect to a PG cluster with
postgresuserCreate a new DB from
template0:CREATE DATABASE new TEMPLATE template0;Connect to the freshly created db (in my case, I used port-forwarding to access the pgbouncer service on localhost:
kubectl port-forward svc/postgresql-lyw-pgbouncer 5432:5432 -n everest)% psql -h localhost -p 5432 -U postgres -d new -W Password: psql: error: connection to server at "localhost" (::1), port 5432 failed: FATAL: bouncer config error connection to server at "localhost" (::1), port 5432 failed: FATAL: SSL requiredHowever, here are the workarounds how one could connect to this freshly created db:
connect to the db directly, so not via pgbouncer
use
_crunchypgbounceruser instead ofpostgresuserpatch the pg cluster to disable the
exposeSuperusersfield:kubectl -n everest patch pg/postgresql-lyw -p '{"spec":{"proxy":{"pgBouncer":{"exposeSuperusers":false}}}}' --type=merge. In case of Everest, the everest-operator will immediately reconcile the value back to thetruevalue, however the patch triggers some kind of an update process that makes the new DB accessible. Each time a new DB is created fromtemplate0this patch helps to get access to it:% psql -h localhost -p 5432 -U postgres -d new -W Password: psql (17.2, server 16.4 - Percona Distribution) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: none) Type "help" for help. new=# exit;Note: There is no problem with connecting to the DBs created from
template1(which is used by default when you justCREATE DATABASE new). All of the users:postgres_crunchypgbouncera user created manually (
CREATE USER new_user WITH PASSWORD 'password')can connect to such DBs without any issue.