Security baseline

Separate roles

CREATE ROLE app_owner NOLOGIN;
CREATE ROLE app_runtime LOGIN;
CREATE ROLE app_migrator LOGIN NOINHERIT;

CREATE SCHEMA app AUTHORIZATION app_owner;
GRANT app_owner TO app_migrator;
GRANT CONNECT ON DATABASE commerce TO app_runtime, app_migrator;
GRANT USAGE ON SCHEMA app TO app_runtime;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_runtime;

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_runtime;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT USAGE, SELECT ON SEQUENCES TO app_runtime;

Runtime does not own objects, the migrator uses SET ROLE app_owner only during migrations, and the owner cannot log in. ALTER DEFAULT PRIVILEGES affects future objects created by the specified creator; it does not repair existing privileges.

A read-only tier for BI tools, support access, and AI agents completes the layering:

CREATE ROLE app_readonly LOGIN;
ALTER ROLE app_readonly SET default_transaction_read_only = on;

GRANT CONNECT ON DATABASE commerce TO app_readonly;
GRANT USAGE ON SCHEMA app TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_readonly;

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT SELECT ON TABLES TO app_readonly;

default_transaction_read_only makes accidental writes fail even if a write grant is added later by mistake.

Authentication and network

• Listen only on required interfaces and restrict sources with firewall/security groups.
• Require TLS remotely and verify the server certificate; consider client certificates for sensitive systems.
• Use SCRAM for new password authentication and retire MD5 configuration.
• Order pg_hba.conf from narrow rules to broad ones by network, database, and role; reload and test both allow and deny paths.
• Separate administrative and application entry points; do not expose the database port publicly.

A pg_hba.conf example matching that ordering — the first matching rule wins, so narrow rules come before broad ones:

# TYPE   DATABASE  USER          ADDRESS        METHOD
local    all       all                          peer
hostssl  commerce  app_runtime   10.0.1.0/24    scram-sha-256
hostssl  commerce  app_readonly  10.0.2.0/24    scram-sha-256
host     all       all           0.0.0.0/0      reject

Reload with SELECT pg_reload_conf(); and test both an allowed and a rejected connection.

Protect search_path

Do not resolve names through untrusted writable schemas. Revoke default create privilege on public and pin paths for sensitive functions:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

CREATE FUNCTION app.current_tenant() RETURNS bigint
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = pg_catalog, app
AS $$ SELECT current_setting('app.tenant_id')::bigint $$;

REVOKE ALL ON FUNCTION app.current_tenant() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION app.current_tenant() TO app_runtime;

SECURITY DEFINER runs with owner privilege. Audit every input, qualified object name, search path, and execute grant.

Row-level security

ALTER TABLE app.orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE app.orders FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_orders ON app.orders
USING (tenant_id = current_setting('app.tenant_id')::bigint)
WITH CHECK (tenant_id = current_setting('app.tenant_id')::bigint);

With RLS enabled, no applicable policy means default deny. Superusers, BYPASSRLS, and normally table owners can bypass; FORCE ROW LEVEL SECURITY subjects owners during ordinary access. Normal object privileges still apply.

Secrets and logs

Rotate and shorten credentials and distribute them via a secret manager. Avoid bound values and sensitive DDL in database logs; restrict audit-log access and retention. pg_stat_activity can expose query text, so monitoring-view privilege also matters.

Audit

The built-in starting point is statement logging:

ALTER SYSTEM SET log_statement = 'ddl';
SELECT pg_reload_conf();

For accountable audit trails use pgaudit, which must be loaded via shared_preload_libraries:

shared_preload_libraries = 'pgaudit'
pgaudit.log = 'write, ddl, role'

Plan for its boundaries:

• pgaudit is an extension, not core; availability and version depend on your distribution or cloud provider.
• Broad classes such as pgaudit.log = 'all' produce large log volumes on busy systems. Start with ddl, role and add classes per compliance requirement; use object auditing (pgaudit.log_relation) for a few sensitive tables instead of logging everything.
• Session auditing can still capture sensitive values, so the log access, retention, and redaction rules above apply to audit output as well.