Preparing the PEM Database Server v8.0

Edit this page

You must configure the PEM database server to work with PgBouncer; the following example demonstrates the steps required to configure the PEM database server.

  1. Create a dedicated user named pgbouncer on the PEM database server. For example:

    pem=# CREATE USER pgbouncer PASSWORD 'ANY_PASSWORD' LOGIN;
    CREATE ROLE
  2. 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
  3. Grant CONNECT privileges 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
  4. Grant USAGE privileges to the pgbouncer user for the pem schema on the pem database. For example:

    pem=# GRANT USAGE ON SCHEMA pem TO pgbouncer;
    GRANT
  5. Grant EXECUTE privileges 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
  6. Use the pem.create_proxy_agent_user(varchar) function to create a user named pem_agent_user1 on the PEM database server. For example:

    pem=# SELECT pem.create_proxy_agent_user('pem_agent_user1');
    create_proxy_agent_user
    -------------------------
    (1 row)

    The function will create a user with the same name with a random password, and grant pem_agent and pem_agent_pool roles to the user. This allows pgBouncer to use a proxy user on behalf of the agent.

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