Pool Settings

Each record in the pool is the name of the virtual database that the pg-doorman client can connect to.

[pools.exampledb] # Declaring the 'exampledb' database

server_host

The directory with unix sockets or the IPv4 address of the PostgreSQL server that serves this pool.

Example: "/var/run/postgresql" or "127.0.0.1".

Default: "127.0.0.1".

server_port

The port through which PostgreSQL server accepts incoming connections.

Default: 5432.

server_database

Optional parameter that determines which database should be connected to on the PostgreSQL server.

application_name

Parameter application_name, is sent to the server when opening a connection with PostgreSQL. It may be useful with the sync_server_parameters = false setting.

connect_timeout

Maximum time to allow for establishing a new server connection for this pool, in milliseconds. If not specified, the global connect_timeout setting is used.

Default: None (uses global setting).

idle_timeout

Close idle connections in this pool that have been opened for longer than this value, in milliseconds. If not specified, the global idle_timeout setting is used.

Default: None (uses global setting).

Close server connections in this pool that have been opened for longer than this value, in milliseconds. Only applied to idle connections. If not specified, the global server_lifetime setting is used.

Default: None (uses global setting).

pool_mode

session — Server is released back to pool after client disconnects.
transaction — Server is released back to pool after transaction finishes.

Default: "transaction".

log_client_parameter_status_changes

Log information about any SET command in the log.

Default: false.

cleanup_server_connections

When enabled, the pool will automatically clean up server connections that are no longer needed. This helps manage resources efficiently by closing idle connections.

Default: true.

scaling_warm_pool_ratio

Override global scaling_warm_pool_ratio for this pool. If not specified, the global setting is used.

scaling_fast_retries

Override global scaling_fast_retries for this pool. If not specified, the global setting is used.

scaling_cooldown_sleep

Override global scaling_cooldown_sleep for this pool. If not specified, the global setting is used. Accepts duration strings like "10ms", "1s", or plain numbers (milliseconds).

Auth Query Settings

The auth_query section enables dynamic user authentication by querying a PostgreSQL database for credentials at connection time. This allows pg_doorman to authenticate users without listing them statically in the configuration file.

pools:
  mydb:
    auth_query:
      query: "SELECT passwd FROM pg_shadow WHERE usename = $1"
      user: "doorman_auth"
      password: "auth_password"

There are two modes of operation:

Dedicated mode (server_user is set): All dynamically authenticated users share a single connection pool that connects to PostgreSQL as server_user. This is the simplest setup and works well when all users need the same backend access.
Passthrough mode (server_user is not set): Each dynamically authenticated user gets their own connection pool that connects to PostgreSQL using their own credentials (MD5 pass-the-hash or SCRAM ClientKey passthrough). This preserves per-user identity on the backend.

Static users (defined in the users section) are always checked first. The auth_query is only used when the username is not found among static users.

Security Recommendation

The user that runs auth queries needs access to password hashes (e.g. from pg_shadow). Do not use a superuser for this purpose. Instead, create a SECURITY DEFINER function owned by a superuser and a dedicated role with minimal privileges:

```sql -- Create a dedicated role for auth queries CREATE ROLE doorman_auth LOGIN PASSWORD 'strong_password';

-- Create a SECURITY DEFINER function (runs with owner's privileges) CREATE OR REPLACE FUNCTION pg_doorman_get_auth(p_usename TEXT) RETURNS TABLE (usename name, passwd text) LANGUAGE sql SECURITY DEFINER SET search_path = pg_catalog AS $$ SELECT usename, passwd FROM pg_shadow WHERE usename = p_usename; $$;

-- Grant execute only to the dedicated role REVOKE ALL ON FUNCTION pg_doorman_get_auth(TEXT) FROM PUBLIC; GRANT EXECUTE ON FUNCTION pg_doorman_get_auth(TEXT) TO doorman_auth; ```

Then use this function in the query parameter: ```yaml auth_query: query: "SELECT * FROM pg_doorman_get_auth($1)" user: "doorman_auth" password: "strong_password" ```

query

SQL query to fetch credentials. Must return a column named passwd or password containing the MD5 or SCRAM hash. If the query returns exactly one column, it is used regardless of name. Any extra columns are ignored. Use $1 as the placeholder for the username parameter.

Example: "SELECT passwd FROM pg_shadow WHERE usename = $1"

user

PostgreSQL username for the executor connection that runs auth queries.

password

Password for the executor user (plaintext). Can be empty if the PostgreSQL server uses trust authentication for this user.

Default: "".

database

Database for executor connections. If not specified, the pool name is used.

Default: None (uses pool name).

pool_size

Number of executor connections (connections used to run auth queries, not data connections).

Default: 2.

server_user

Backend PostgreSQL user for data connections in dedicated mode. When set, all dynamically authenticated users share one connection pool that connects as this user. When not set, passthrough mode is used.

Default: None (passthrough mode).

server_password

Plaintext password for the server_user. Only meaningful when server_user is set.

Default: None.

default_pool_size

Pool size for dynamic user data connections (per-user in passthrough mode, shared in dedicated mode).

Default: 40.

cache_ttl

Maximum cache age for successfully fetched credentials. Accepts duration strings like "1h", "30m", "300s".

Default: "1h".

cache_failure_ttl

Cache TTL for "user not found" entries (negative cache). Prevents repeated queries for non-existent users.

Default: "30s".

min_interval

Minimum interval between re-fetches for the same username after an authentication failure. Protects the backend from excessive queries during brute-force attempts.

Default: "1s".

Pool Users Settings

[pools.exampledb.users.0]
username = "exampledb-user-0" # A virtual user who can connect to this virtual database.

username

A virtual username who can connect to this virtual database (pool).

password

The password for the virtual pool user. Password can be specified in MD5, SCRAM-SHA-256, or JWT format. Also, you can create a mirror list of users using secrets from the PostgreSQL instance: select usename, passwd from pg_shadow.

auth_pam_service

The pam-service that is responsible for client authorization. In this case, pg_doorman will ignore the password value.

server_username

The real PostgreSQL username used to connect to the database server.

By default, PgDoorman uses the same username for both client authentication and server connections, using passthrough authentication: the cryptographic material from the client's authentication (MD5 hash or SCRAM ClientKey) is reused to authenticate to the backend. This eliminates the need for plaintext server_password.

Passthrough mode (recommended for identity-matching users):

Omit both server_username and server_password
pg_doorman reuses the client's auth proof to connect to PostgreSQL
For MD5: the hash from password is used directly
For SCRAM: the ClientKey is extracted from the client's first SCRAM auth and cached
Requirement: the password verifier must match pg_authid on the backend (same salt/iterations for SCRAM, same hash for MD5)

Explicit credentials mode (when identities differ):

Set server_username and server_password to the actual PostgreSQL credentials
server_password requires server_username to be set
server_username alone (without server_password) is allowed for trust authentication

server_password

The plaintext password for the PostgreSQL server user specified in server_username.

When server_password is not set and the user is passthrough-eligible (no server_username or server_username equals username), PgDoorman uses passthrough authentication instead: the cryptographic material from the client's authentication is reused for the backend connection. This eliminates plaintext passwords from config files.

server_password requires server_username to be set.

pool_size

The maximum number of simultaneous connections to the PostgreSQL server available for this pool and user.

Default: 40.

min_pool_size

The minimum number of connections to maintain in the pool for this user. Connections are prewarmed at startup (before the first retain cycle) and then maintained by periodic replenishment. If specified, it must be less than or equal to pool_size.

Default: None.

server_lifetime

Close server connections for this user that have been opened for longer than this value, in milliseconds. Only applied to idle connections. If not specified, the pool's server_lifetime setting is used.

Default: None (uses pool setting).

Passthrough Authentication

By default, PgDoorman uses passthrough authentication: the client's cryptographic proof (MD5 hash or SCRAM ClientKey) is automatically reused to authenticate to PostgreSQL. No plaintext passwords in config needed.

Set server_username and server_password only when the backend PostgreSQL user differs from the pool username (e.g., username mapping or JWT auth):

users:
  - username: "app_user"              # client-facing name
    password: "md5..."                # hash for client authentication
    server_username: "pg_app_user"    # different backend PostgreSQL user
    server_password: "plaintext_pwd"  # plaintext password for that user