Can't login as superuser to pgbouncer

PostgreSQL Percona Operator for PostgreSQL
1

Hi all,

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.

1 Like
2

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?

1 Like
3

Hi @Sergey_Pronin,

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.

1 Like
4

@Sergey_Pronin So the workaround is to modify the pgbouncer-users.txt field with the additional postgres user in the pgbouncer secret created by the operator?

Is there a way I can configure the operator to add multiple users, to pgbouncer-users.txt?
Right now the default behavior of the operator seems to be to only add one user to pgbouncer-users.txt.

But if in yaml I define a PerconaPGCluster with multiple users:

spec:
  users:
    - name: user1
      databases:
        - mydb
    - name: user2
      databases:
        - mydb

Then only one user seems to be written to pgbouncer-users.txt (this is with the 2.0.0 version of the operator)

5

Hi @jason123 if we are talking about PGv1 operator you need to use pgBouncer.exposePostgresUser option to allow access superuser via pgbouncer.

6

Hi @Craig_Rodrigues, you want to be able to use several users with superuser privileges and allow them access via pgbouncer. And it is for PGv2. Am I right?