Preparing the PEM database server v8

Suggest edits

You must configure the PEM database server to work with PgBouncer. This example shows how to configure the PEM database server.

Create a dedicated user named pgbouncer on the PEM database server:
```
CREATE USER pgbouncer PASSWORD 'ANY_PASSWORD' LOGIN;
```
Output
```
CREATE ROLE
```
Create a user named pem_admin1 (not a superuser) with pem_admin and pem_agent_pool role membership on the PEM database server:
```
CREATE USER pem_admin1 PASSWORD 'ANY_PASSWORD' LOGIN CREATEROLE;
```
Output
```
CREATE ROLE
```
```
GRANT pem_admin, pem_agent_pool TO pem_admin1;
```
Output
```
GRANT ROLE
```
Grant CONNECT privileges to the pgbouncer user on the pem database:
```
GRANT CONNECT ON DATABASE pem TO pgbouncer;
```
Output
```
GRANT
```
Grant USAGE privileges to the pgbouncer user for the pem schema on the pem database:
```
GRANT USAGE ON SCHEMA pem TO pgbouncer;
```
Output
```
GRANT
```
Grant EXECUTE privileges to the pgbouncer user on the pem.get_agent_pool_auth(text) function in the pem database. For example:
```
GRANT EXECUTE ON FUNCTION pem.get_agent_pool_auth(text) TO pgbouncer;
```
Output
```
GRANT
```
Use the pem.create_proxy_agent_user(varchar) function to create a user named pem_agent_user1 on the PEM database server:
```
SELECT pem.create_proxy_agent_user('pem_agent_user1');
```
Output
```
create_proxy_agent_user
-------------------------
(1 row)
```
The function creates a user with the same name with a random password and grants pem_agent and pem_agent_pool roles to the user. This approach allows pgBouncer to use a proxy user on behalf of the agent.
Add the following entries to the start of the pg_hba.conf file of the PEM database server. These entries allow the pgBouncer user to connect to the pem database using the md5 authentication method.
```
# Allow the PEM agent proxy user (used by
# pgbouncer) to connect the to PEM server using
# md5

local pem pgbouncer,pem_admin1 md5
```

← Prev

PEM server and agent connection management mechanism

Configuring PgBouncer

Preparing the PEM database server v8

← Prev

Next →