Database roles and connection budgets

Give each role a purpose

A login role is an identity; privileges determine what it may do. Separate migration ownership, application writes, and diagnostic reads. Using the postgres superuser in this isolated lab is convenient for setup; applications should not inherit that authority. Database roles↗, privileges↗.

Open psql against the existing isolated study database as the fixture administrator, as in the first bite. Run the examples outside any failed transaction; use ROLLBACK first if your prompt shows one.

Practice with a non-login reader role in the fixture database:

CREATE ROLE study_reader NOLOGIN;
GRANT USAGE ON SCHEMA study TO study_reader;
GRANT SELECT ON study.accounts, study.entries TO study_reader;
SET ROLE study_reader;
SELECT account_id, label FROM study.accounts ORDER BY account_id;
RESET ROLE;

In a separate interactive test, expect a permission error, then reset:

SET ROLE study_reader;
UPDATE study.accounts SET label = 'not-allowed' WHERE account_id = 1;
RESET ROLE;

SET ROLE from the fixture administrator tests authorization, not password authentication. This role has no login capability. Creating real login credentials, verifying TLS, and configuring server access rules are separate tasks. Schema usage does not grant table access. Grants on existing tables do not automatically cover future tables; default privileges depend on the role creating the objects. Default privileges↗.

Diagnose the connection layer

For a networked client, use a driver-supported TLS configuration that verifies the server identity; libpq's verify-full is one example. The socket-only fixture does not demonstrate TLS or network authentication. Host authentication↗, libpq SSL↗.

Count more than replicas

For a hypothetical example, four API replicas × a pool maximum of ten = forty possible connections, before consumers, migrations, operators, and overlapping rollout replicas. A pool maximum is a ceiling, not proof of demand. Raising every pool or max_connections can increase contention and memory use.

Inspect the fixture's limit:

SHOW max_connections;
SELECT usename, application_name, state, count(*)
FROM pg_stat_activity
WHERE datname = current_database()
GROUP BY usename, application_name, state
ORDER BY usename, application_name, state;

Choose a budget from measured concurrency, query duration, and operational headroom. A pooler such as PgBouncer is optional depth; check whether the application’s behavior is compatible with transaction pooling before adopting it. It is not installed by this module. Connection settings↗.

Checkpoint

Prove the reader can select but cannot update. Draft a connection budget that includes deployment surge and recovery access. Apply it separately to Accounts and Ledger; they use separate databases and application pools.

Check access and the pool arithmetic

The reader needs both schema USAGE and table SELECT; it has neither table UPDATE nor login capability. An expected denied write demonstrates a permission rule, not a broken server. Reset the role afterwards so the next administration command uses the intended identity.

Extend the hypothetical four-replica example: a rollout adds one overlapping API replica with the same pool maximum of ten. The API ceiling becomes 50 connections before consumer, migration, and recovery access. If the service uses several worker processes each with a pool, count those too. Actual occupancy may be lower; keep the ceiling and measurement separate. Next, inspect activity to see who is using connections and what they are waiting for.