Unable to connect to the database using pgbouncer with any user that is not a superuser

Products

• VMware Tanzu Greenplum

Issue/Introduction

The customer is able to connect with PSQL from the coordinator but when connecting from any other host, they get invalid server parameter errors in the pgbouncer logs.

To fix this they can grant SUPERUSER access to that user account but would prefer not to do that for obvious reasons.

Coordinator logs

2021-02-10 11:25:35.939426 UTC,...,"execute : SELECT usename, passwd FROM pg_shadow WHERE usename=$1","parameters: $1 = 'abc123'",,,,,"SELECT usename, passwd FROM pg_shadow WHERE usename=$1",0,,"postgres.c",2756,
2021-02-10 11:25:36.583134 UTC,...,"statement: SET application_name='pgAdmin 4 - DB:our_db';",,,,,,"SET application_name='pgAdmin 4 - DB:our_db';",0,,"postgres.c",1590,
2021-02-10 11:25:37.277285 UTC,...,"The previous session was reset because its gang was disconnected (session id = 8865). The new session id = 8866",,,,,,"SET application_name='pgAdmin 4 - DB:our_db';",0,,"cdbgang.c",1710,
2021-02-10 11:25:37.277336 UTC,...,"ERROR","58M01","failed to acquire resources on one or more segments","FATAL: Invalid input for statement_mem. Must be less than max_statement_mem (2048000 kB). (cdbvars.c:1335) (seg2 10.149.16.219:40000)",,,,,"SET application_name='pgAdmin 4 - DB:our_db';",0,,"cdbgang_async.c",200,
2021-02-10 11:25:37.277360 UTC,...,"An exception was encountered during the execution of statement: SET application_name='pgAdmin 4 - DB:our_db';",,,,,,,0,,,,  

pgbouncer logs

2021-02-11 15:04:44.598 1610 LOG C-0x1698c70: our_db/user@host:22184 login attempt: db=our_db user=user tls=TLSv1.2/ECDHE-RSA-AES128-GCM-SHA256/ECDH=prime256v1
2021-02-11 15:04:44.598 1610 LOG S-0x894150: our_db/[email protected]:5432 new connection to server (from 127.0.0.1:52051)
2021-02-11 15:05:02.626 1610 ERROR varcache_apply failed: ERROR: failed to acquire resources on one or more segments
2021-02-11 15:05:02.626 1610 LOG S-0x894150: our_db/[email protected]:5432 closing because: invalid server parameter (age=18)
2021-02-11 15:05:02.626 1610 LOG C-0x1698c70: our_db/user@host:22184 closing because: invalid server parameter (age=18)
2021-02-11 15:05:02.626 1610 WARNING C-0x1698c70: our_db/user@host:22184 Pooler Error: invalid server parameter
2021-02-11 15:05:02.634 1610 LOG C-0x1698c70: our_db/user@host:22220 login attempt: db=our_db user=user tls=TLSv1.2/ECDHE-RSA-AES128-GCM-SHA256/ECDH=prime256v1 

Resolution

Upon viewing the errors relating to the statment_mem, the customer changed the PARAM max_statement_mem by manually editing the postgres.conf file and restarting the database. This caused a consistency problem across the database cluster. This is not recommended.

To resolve this issue, use the gpconfig command:

gpconfig -c max_statement_mem -v 2048000