Preparing the PEM Server for pgBouncer Connections v8
You must configure the PEM database server to accept connections from pgBouncer; the following example demonstrates the steps required to prepare the PEM database server.
Create a dedicated user named pgbouncer on the PEM database server. For example:
pem=# CREATE USER pgbouncer PASSWORD 'ANY_PASSWORD' LOGIN; CREATE ROLE
Create a user named pem_admin1 (a non-super user) with pem_admin and pem_agent_pool role membership on the PEM database server. For example:
pem=# CREATE USER pem_admin1 PASSWORD 'ANY_PASSWORD' LOGIN CREATEROLE; CREATE ROLE pem=# GRANT pem_admin, pem_agent_pool TO pem_admin1; GRANT ROLE
Grant CONNECT privilege to the pgbouncer user on the pem database. For example:
pem=# GRANT CONNECT ON DATABASE pem TO pgbouncer ;GRANT USAGE ON SCHEMA pem TO pgbouncer; GRANT
Grant USAGE privilege to the pgbouncer user for the pem schema on the pem database. For example:
pem=# GRANT USAGE ON SCHEMA pem TO pgbouncer; GRANT
Grant EXECUTE privilege to the pgbouncer user on the pem.get_agent_pool_auth(text) function in the pem database. For example:
pem=# GRANT EXECUTE ON FUNCTION pem.get_agent_pool_auth(text) TO pgbouncer; GRANT
Use the pem.create_proxy_agent_user(varchar) function to create a user named pem_agent_user1 on the PEM database server. The function will create a user with the same name with a random password, and grant
pem_agent
andpem_agent_pool
roles to the user. This allows pgBouncer to use a proxy user on behalf of the agent. For example:pem=# SELECT pem.create_proxy_agent_user('pem_agent_user1'); create_proxy_agent_user ------------------------- (1 row)
Add the following entries to the start of the
pg_hba.conf
file of the PEM database server; this will allow pgBouncer user to connect to the pem database using the md5 authentication method. For example:# Allow the PEM agent proxy user (used by # pgbouncer) to connect the to PEM server using # md5 local pem pgbouncer,pem_admin1 md5
After configuring the PEM server, you should configure pgBouncer.