Preparing the PEM database server v9

To enable connection pooling for PEM with PgBouncer, you must configure dedicated users and create an SSL key and certificate on the PEM database server.

This example shows how to prepare the PEM database server with the enterprisedb user on a RHEL-based operating system with EDB Postgres Advanced Server version 16. The location of your data, the configuration and key files, and the user you use to perform the configuration may differ depending on your OS and Postgres distribution.

Prerequisites

  • Connect to the pem database of the PEM database server.

  • Connect as the enterprisedb or postgres user based on your Postgres distribution.

    Postgres distributionUser
    EDB Postgres Advanced Serverenterprisedb
    EDB Postgres Extended Serverpostgres
    PostgreSQLpostgres

Creating users and roles for PgBouncer-PEM connections

  1. Create a dedicated user named pgbouncer with pem_agent_pool membership. This user will serve connections from PgBouncer to the PEM database by forwarding all agent database queries.

    CREATE ROLE pgbouncer PASSWORD 'ANY_PASSWORD' LOGIN;
    Output
    CREATE ROLE
    GRANT pem_agent_pool TO pgbouncer;
    Output
    GRANT ROLE
  2. Create a user named pem_admin1 (not a superuser) with pem_admin and pem_agent_pool role membership. This user registers the agent to the PEM server and manages access to the PEM database.

    CREATE ROLE pem_admin1 PASSWORD 'ANY_PASSWORD' LOGIN CREATEROLE;
    Output
    CREATE ROLE
    GRANT pem_agent_pool TO pem_admin1;
    Output
    GRANT ROLE
    GRANT pem_agent TO pem_admin1 WITH ADMIN OPTION;
    Output
    GRANT ROLE
  3. Grant CONNECT privileges to the pgbouncer user:

    GRANT CONNECT ON DATABASE pem TO pgbouncer;
    Output
    GRANT
  4. Grant USAGE privileges to the pgbouncer user for the pem schema:

    GRANT USAGE ON SCHEMA pem TO pgbouncer;
    Output
    GRANT
  5. Grant EXECUTE privileges to the pgbouncer user on the pem.get_agent_pool_auth(text) function. For example:

    GRANT EXECUTE ON FUNCTION pem.get_agent_pool_auth(text) TO pgbouncer;
    Output
    GRANT
  6. Use the pem.create_proxy_agent_user(varchar) function to create a user named pem_agent_user1. This proxy user will serve connections between all Agents and PgBouncer.

    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 and 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.

Updating the configuration files to allow PgBouncer-PEM connections

  1. Allow the pgbouncer user to connect to the pem database using the SSL authentication method by adding the hostssl pem entry in the pg_hba.conf file of the PEM database server.

    In the list of rules, ensure you place the hostssl pem entry before any other rules assigned to the +pem_agent user.

    # Allow the PEM agent proxy user (used by pgbouncer) 
    # to connect the to PEM server using SSL
    
    hostssl pem     +pem_agent_pool 127.0.0.1/32  cert map=pem_agent_pool
  2. Allow the PEM server to map all users involved in PgBouncer-PEM connections by adding these lines to the $PGDATA/pg_ident.conf user mapping file:

    pem_agent_pool  pem_agent_pool  pem_agent_user1
    pem_agent_pool  pem_agent_pool  pem_admin1
    pem_agent_pool  pem_agent_pool  pgbouncer
  3. Restart the Postgres service. Replace <postgres_service> with the name of the Postgres instance systemd service name:

    systemctl restart <postgres_service> 

Creating the SSL key and certificate for PgBouncer-PEM authentication

Create a key and certificate for the pem_agent_pool group role. Then, move the files to the PgBouncer instance to allow authentication between the PEM database server and PgBouncer.

This example runs EDB Postgres Advanced Server on RHEL. When setting your environment variables, choose the correct directories according to your operating system and Postgres distribution.

  1. Set the $DATA_DIR environment variable to your data directory:

    export DATA_DIR=/var/lib/edb/as16/data
    Data directories per OS and Postgres version
    Here are some examples of other default data directories per operating system and Postgres version.
    Postgres versionRHEL/Rocky Linux/AlmaLinux/SLESDebian/Ubuntu
    EDB Postgres
    Advanced Server 16
    /var/lib/edb/as16/data/var/lib/edb-as/16/main
    EDB Postgres
    Extended Server 16
    /var/lib/edb/edb-pge/16/data/var/lib/edb-pge/16/main
    PostgreSQL 16/var/lib/edb/pgsql/16/data/etc/postgresql/16/main

  2. Set the $USER_HOME environment variable to the home directory accesible to the user:

    export USER_HOME=/var/lib/edb
    User home directories per OS and Postgres version
    Here are some examples of other default home directories per operating system and Postgres version.
    Postgres versionRHEL/Rocky Linux/AlmaLinux/SLESDebian/Ubuntu
    EDB Postgres
    Advanced Server 16
    /var/lib/edb/var/lib/edb-as
    EDB Postgres
    Extended Server 16
    /var/lib/pgsql/var/lib/postgresql
    PostgreSQL 16/var/lib/pgsql/var/lib/postgresql

  3. Create the signing key with openssl:

    openssl genrsa -out pem_agent_pool.key 4096
  4. Create a certificate-signing request (CSR). Replace the -subj attributes in <...> as required. Ensure the common name (CN) is set to the pem_agent_pool group role name:

    openssl req -new -key pem_agent_pool.key -out pem_agent_pool.csr -subj '/C=<COUNTRY>/ST=<STATE>/L=<LOCATION>/O=<ORGANISATION>/CN=pem_agent_pool'
  5. Use the PEM CA and key to sign the CSR:

    openssl x509 -req -days 365 -in pem_agent_pool.csr -CA $DATA_DIR/ca_certificate.crt -CAkey $DATA_DIR/ca_key.key -CAcreateserial -out pem_agent_pool.crt
  6. Move the created key and certificate to a path the enterprisedb user can access.

    In this example, create a folder called ~/.postgresql in the home directory of the enterprisedb user and ensure it has permissions:

    mkdir -p $USER_HOME/.postgresql
    mv pem_agent_pool.key pem_agent_pool.crt $USER_HOME/.postgresql
    chmod 0600 $USER_HOME/.postgresql/pem_agent_pool.key
    chmod 0644 $USER_HOME/.postgresql/pem_agent_pool.crt
    chown enterprisedb:enterprisedb $USER_HOME/.postgresql/pem_agent_pool.*