I’m running the kubernetes operator. I am following all the steps for Install Percona Distribution for PostgreSQL on Kubernetes
I am able to create all the pods and I am able to login to pgdb as pguser (step 6). However, when I try to login as postgres (superuser), I get an error that says no such user.
psql: error: connection to server at "cluster1-pgbouncer" (XX.XXX.XXX.XXX), port 5432 failed: FATAL: no such user
Also, when I alter the pguser role to superuser, I can no longer login with pguser anymore. Then, when I remove the superuser privilege, I can login again to pguser. Can someone help with this? Thanks.
Hello @jason123 ,
we already had this concern raised by the user here: [K8SPG-111] Cannot connect to pgsql through pgbouncer using postgres user - Percona JIRA
This is done for security reasons. Superuser should not be connecting to the database through pgbouncer. There is a workaround though.
Do you mind sharing your use case here?
I wanted to deploy everything from terraform, which I got running. After the pods are running, I created a kubernetes job with perconalab/percona-distribution-postgresql and want to run some steps automatically like:
psql -h cluster1-pgbouncer -p 5432 -U postgres/any superuser postgres -c 'CREATE EXTENSION IF NOT EXISTS extension1, extension2, etc.. Then I would want to automatically run some commands from those extensions.
So I need to be able to log into pgbouncer with the superuser to create those extensions. I could do it manually if I exec into the pod, but I want to automate the database functions through terraform.