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).
server_lifetime
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
When the backend connection is returned to the pool.
transaction: released after each transaction. session: held until client disconnects.
Same as PgBouncer's pool_mode.
Default: "transaction".
log_client_parameter_status_changes
Log information about any SET command in the log.
Default: false.
cleanup_server_connections
Controls whether pg_doorman resets session state when a connection is returned to the pool.
When enabled and the session was modified, pg_doorman sends: RESET ROLE, plus conditionally
RESET ALL (if SET was used), DEALLOCATE ALL (if PREPARE was used), CLOSE ALL (if cursors
were opened). Note: ROLLBACK for open transactions is always executed regardless of this setting.
Disable only if your application never uses SET, prepared statements, or cursors and you want
to save the cleanup roundtrip.
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.
max_db_connections
Hard cap on the total number of server connections to this database, shared across all user
pools. When the limit is reached and a new connection is needed, the coordinator first tries
to evict idle connections from other users (respecting their min_pool_size), then waits
for a connection to be returned, and finally falls back to the reserve pool. Set to 0
(or omit) to disable coordination — each user pool works independently, capped only by its
own pool_size. Similar to PgBouncer's max_db_connections.
Default: 0 (disabled).
min_connection_lifetime
Minimum age (in milliseconds) a connection must reach before it can be evicted by the
pool coordinator. Prevents cyclic reconnect between user pools that share the same
database: without this gate, one user's idle slot becomes evictable the moment its
peer asks for a permit, and under sustained multi-user load each pool steals a slot
back from its neighbour every few seconds. Only relevant when max_db_connections > 0.
Default: 30000 (30 seconds).
reserve_pool_size
Number of extra connections allowed beyond max_db_connections as a last resort. When
eviction fails and no connections are returned within reserve_pool_timeout, a reserve
connection is granted to the highest-priority requester. Users below their min_pool_size
get absolute priority. Only relevant when max_db_connections > 0.
Default: 0.
reserve_pool_timeout
How long (in milliseconds) to wait for a regular connection to become available before
falling back to the reserve pool. During this window the coordinator listens for returned
connections. Only relevant when max_db_connections > 0 and reserve_pool_size > 0.
Default: 3000 (3 seconds).
min_guaranteed_pool_size
Pool-level default for the minimum number of connections per user that are protected from coordinator eviction. When the coordinator needs to free a connection slot for another user, it will not evict connections from a user who is at or below this count.
This is separate from min_pool_size (user-level): min_pool_size controls prewarm
and replenish (proactively creating connections), while min_guaranteed_pool_size
only affects eviction decisions (never creates connections).
The effective protection for a user is max(user.min_pool_size, pool.min_guaranteed_pool_size).
Set to 0 (or omit) for no eviction protection. Only relevant when max_db_connections > 0.
Default: 0 (no protection).
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_useris set): All dynamically authenticated users share a single connection pool that connects to PostgreSQL asserver_user. This is the simplest setup and works well when all users need the same backend access. - Passthrough mode (
server_useris 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.
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).
workers
Number of persistent connections to PostgreSQL dedicated to running the auth_query SQL.
These connections are opened at startup and kept alive. They handle credential lookups only —
client data traffic goes through separate data pool connections (pool_size).
Increase if you see auth latency spikes under high connection rates.
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.
pool_size
Maximum number of backend connections per data pool created by auth_query.
Same concept as users[].pool_size for statically defined users.
How many pools are created depends on the mode: server_user controls whether
all dynamic users share one pool or each gets their own.
Default: 40.
min_pool_size
Minimum number of backend connections to maintain per dynamic user pool in passthrough mode. Connections are prewarmed when the pool is first created and replenished by the retain cycle. Set to 0 to disable (default). Note: pools with min_pool_size > 0 are never garbage-collected, and total backend connections scale as active_users × min_pool_size.
Default: 0.
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
The username that clients use to connect to this pool. Must be unique within the pool.
password
Password verifier for client authentication. Supports MD5, SCRAM-SHA-256, and JWT formats.
You can copy password hashes directly from PostgreSQL: 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_usernameandserver_password - pg_doorman reuses the client's auth proof to connect to PostgreSQL
- For MD5: the hash from
passwordis used directly - For SCRAM: the ClientKey is extracted from the client's first SCRAM auth and cached
- Requirement: the
passwordverifier must matchpg_authidon the backend (same salt/iterations for SCRAM, same hash for MD5)
Explicit credentials mode (when identities differ):
- Set
server_usernameandserver_passwordto the actual PostgreSQL credentials server_passwordrequiresserver_usernameto be setserver_usernamealone (withoutserver_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
Maximum number of backend connections to PostgreSQL for this user. In transaction mode, connections are shared across clients, so this is usually much less than the number of clients. Similar to PgBouncer's default_pool_size, but configured per-user rather than globally.
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).
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