Skip to content

Database-Side Controls

The previous chapters secure the host the Processor runs on and the network between it and the database. This chapter is the other end of that connection: what the database itself should enforce so that the pgrelay credential is only usable from the right place, over an encrypted link, and can do nothing beyond its job even if it is.

Everything here is ordinary PostgreSQL administration; pg_relay adds no mechanism of its own. What it adds is the reason each setting matters, given what the previous chapter on compromise established.

pg_hba.conf: the credential works from one place only

The single most valuable line in this chapter. Restrict the pgrelay role to the Processor hosts' addresses, require TLS, and require SCRAM:

# TYPE    DATABASE       USER      ADDRESS            METHOD
hostssl   your_database  pgrelay   10.20.30.11/32     scram-sha-256
hostssl   your_database  pgrelay   10.20.30.12/32     scram-sha-256
host      all            pgrelay   all                reject
  • hostssl — the connection must be TLS. A plaintext attempt from the right address is refused.
  • One line per Processor host, /32 (or /128) — not the subnet, not all. A stolen .pgpass is useless from any other machine.
  • scram-sha-256 — never md5, never password, never trust. Set password_encryption = scram-sha-256 in postgresql.conf before creating or resetting the role's password so the stored verifier is SCRAM.
  • The final reject line makes the intent explicit and catches any later, broader rule that would otherwise match.

On a managed cloud database you do not edit pg_hba.conf; the equivalent is the security group / NSG / authorised-network rule allowing 5432 from the Processor's own security group or subnet only, plus the provider's TLS-enforcement setting (rds.force_ssl, Azure's require_secure_transport, Cloud SQL's "Allow only SSL connections" — or the Auth Proxy, which makes plaintext impossible). Identity-based auth (Cloud-Managed Databases) further binds the credential to the Processor's cloud identity, which no address rule can match.

Even with the co-located sandbox tier this rule has a lesson: the shipped example connects over the Unix socket as postgres with peer auth, which is the one arrangement that makes the Processor a superuser. Production never uses the socket.

TLS with verification, both ends

The server side is ssl = on with a certificate issued by a CA you control (or the managed provider's certificate). The client side is the Processor's environment:

PGSSLMODE=verify-full
PGSSLROOTCERT=/etc/pg_relay/ca.pem

verify-full checks that the server's certificate chains to that CA and that its name matches PGHOST. It is the only mode that defends against an impostor on the path; require merely encrypts to whoever answers, and prefer — the sandbox example's setting — will silently downgrade to plaintext if TLS fails. With hostssl on the server and verify-full on the client, neither side can be talked into a plaintext session.

If you want the credential to be a certificate rather than a password — the strongest option on-premises — PostgreSQL's cert authentication method does exactly that: the Processor presents a client certificate (PGSSLCERT/PGSSLKEY, the key file pgrelay:pgrelay 0600 like .pgpass), pg_hba.conf maps its CN to the pgrelay role, and there is no password at all. This is the on-premises analogue of the cloud identity-token approach.

The shape of the pgrelay role

The extension creates pgrelay as NOLOGIN and grants it only the operating set. Turning it into a working Processor role should add only LOGIN and a credential, and nothing that widens its reach:

ALTER ROLE pgrelay LOGIN PASSWORD 'from-a-generator, into .pgpass only';
ALTER ROLE pgrelay NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT NOBYPASSRLS NOREPLICATION;
ALTER ROLE pgrelay CONNECTION LIMIT 8;   -- workers × instances, plus the debug/exec connections, plus headroom
  • NOINHERIT is worth calling out. It means pgrelay does not automatically exercise the privileges of any role it is a member of; it must SET ROLE explicitly. That is precisely the behaviour the run_as opt-in relies on — a job runs as a maintenance role only when the job says so — and it stops a granted role's privileges leaking into every ordinary action the Processor runs.
  • CONNECTION LIMIT bounds how many sessions the credential can open at once. A legitimate fleet uses --workers per instance, plus one debug-trace connection and one shared exec connection per instance, plus a detached-job connection per concurrent detached job. Size the limit to that and a small margin, and an attacker cannot use the credential to open hundreds of sessions.
  • Do not grant pg_read_all_data, pg_write_all_data, pg_monitor, or any of the predefined roles. None is needed.

Confirm the role's privileges outside the pgrelay schema are nothing:

-- Any table privilege at all, anywhere, held directly by pgrelay? Expect zero rows.
SELECT table_schema, table_name, privilege_type
  FROM information_schema.table_privileges
 WHERE grantee = 'pgrelay' AND table_schema <> 'pgrelay';

-- Roles pgrelay can SET ROLE to (the run_as opt-ins). Expect an empty or deliberately short list.
SELECT roleid::regrole AS can_become FROM pg_auth_members WHERE member = 'pgrelay'::regrole;

What PUBLIC holds — because pgrelay holds it too

Every role, pgrelay included, has whatever PUBLIC has. On a fresh PostgreSQL 15+ database that is CONNECT on the database, USAGE on the public schema (but no CREATE), and EXECUTE on any function created without revoking it. On an older database, or one where application schemas were set up loosely, PUBLIC may hold table privileges you never intended a service role to have.

The two things to check:

-- Tables PUBLIC can read: every one is a table a compromised Processor can read.
SELECT table_schema, table_name
  FROM information_schema.table_privileges
 WHERE grantee = 'PUBLIC' AND privilege_type = 'SELECT'
   AND table_schema NOT IN ('pg_catalog', 'information_schema');

and, on a database the Processor shares with applications, whether pgrelay.notify() should stay public. It is granted to PUBLIC by default — any role that can connect can fire an event on any active channel, mirroring pg_notify(). If not every role should be able to trigger actions, revoke it and grant per role, as the Security Model chapter shows. Note that this is about other roles: pgrelay needs notify() for nothing, and a compromised Processor could fire events through it only in the same way any connected role could.

The run_as opt-in, restated as policy

What a Compromised Processor Can Do explains why GRANT <role> TO pgrelay is the one setting that defines the blast radius. As database policy:

  1. Default: grant nothing. If no ad-hoc SQL job uses p_run_as, pgrelay should be a member of no role.
  2. When a job needs it, create a role for the job, owning exactly what the job maintains: CREATE ROLE maint_orders NOLOGIN; ALTER TABLE orders OWNER TO maint_orders; … then GRANT maint_orders TO pgrelay;. Never grant an application role, a schema owner, or anything with CREATEROLE/CREATEDB/BYPASSRLS.
  3. Review the membership list (the pg_auth_members query above) as part of the same change control that reviews pg_hba.conf.
  4. Remember the scheduling-side check already enforces that the person scheduling a run_as job must themselves hold the role — so a management user cannot schedule privileges they do not have. The GRANT … TO pgrelay is the DBA's separate, explicit consent that the Processor may exercise it.

Management roles are the other half

The Processor is deliberately unable to change channels, register actions, or schedule work. The roles that can — those given pgrelay.grant_user(role) — are therefore where "what SQL does pg_relay execute?" is actually decided. A management role can register a channel whose action is any SQL, and ad-hoc jobs whose payload is SQL. Treat grant_user() grants as you would treat DDL privileges: named roles, real people or tightly scoped deployment identities, never PUBLIC, and never the Processor's own role. Granting Permissions lists exactly what each grant function confers.

Logging on the database side

Two settings make the Processor's activity durably visible independent of anything the Processor itself records:

log_connections = on
log_disconnections = on

— filtered to usename = 'pgrelay' in your log pipeline, these give a record of every session the credential opened, from which address, for how long. A connection from an address that is not a Processor host, or outside a change window, is the earliest possible signal of the compromise the previous chapter describes. On managed platforms the same information is in the provider's database logs (RDS/Aurora log exports, Azure diagnostic settings, Cloud SQL logging) and pg_stat_activity at any moment.

Continue to the Checklist.