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.

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.

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.