Postgres Security And Data Access Hardening

Postgres Security and Data Access Hardening

When to use

Invoke when a PostgreSQL deployment must enforce a security architecture and data classification at the engine, when reviewing an existing database for access-control and data-protection gaps before go-live or a security review, or when remediating over-privileged roles, missing tenant isolation, unprotected PII, or weak connection security.

Do not use for: schema modeling or migrations (use postgres-schema-and-migration), query/index performance (use postgres-indexing-and-query-optimization), replication/HA topology (use postgres-replication-and-ha-readiness), or backup/restore/PITR (use postgres-backup-and-operational-readiness).

Inputs

Required:

• The security architecture in scope: trust boundaries, threat model, authorization model, and tenant-isolation requirements, sourced from security-architecture.md where it exists.
• The data classification: which tables/columns are PII, confidential, or regulated, sourced from security-architecture.md / data-architecture.md.
• The deployment substrate: self-managed, managed (RDS/Aurora/Cloud SQL), or Kubernetes operator — this constrains TLS, audit, and TDE options.

Optional:

• Current role/grant inventory, pg_hba.conf, and postgresql.conf security settings.
• Existing RLS policies, column grants, and encryption usage.
• Application connection model (single app role, per-tenant role, connection pooler identity).
• Secret store and rotation mechanism in use.
• Compliance regime (GDPR/HIPAA/PCI/SOC2) and audit obligations.
• Network topology and current exposure (public IP, VPC, allowlists).

Operating rules

• Enforce the architecture; do not invent the policy. The authorization model, tenant-isolation requirement, and data classification come from security-architecture.md. If a needed classification or isolation boundary is unstated, pause and raise an ADR candidate against security — do not guess.
• Least privilege is the default and is concrete. No role has SUPERUSER, broad ALL PRIVILEGES, or ownership it does not need. Application roles are not table owners. PUBLIC grants are revoked unless explicitly justified.
• Tenant isolation for tier-0/regulated multi-tenant data is enforced at the engine with Row-Level Security, not solely in application code. "The application filters by tenant_id" is not acceptable isolation for regulated data (per the data-layer decided constraints).
• PII protection uses the engine's capabilities: column-level GRANT/REVOKE, RLS, and encryption (pgcrypto for column-level, or substrate TDE for at-rest) per the classification. Application-only enforcement is rejected for tier-0 columns.
• Connection security is mandatory: TLS required (sslmode=verify-full expectation on clients, ssl=on, modern cipher/version floor), scram-sha-256 authentication, and pg_hba.conf that denies by default and never uses trust for non-local production access.
• Audit is scoped, not boil-the-ocean: pgaudit (or substrate-native audit) configured for the security-relevant event classes named by the threat model (DDL, role changes, privileged reads on regulated data), with log routing and tamper-evidence per observability-standards. Auditing everything is a performance and noise failure.
• Secrets and credentials have a rotation posture: no static long-lived superuser in app config, rotation owner and cadence stated, and the restore/bootstrap path does not embed an unrotated secret. Prefer IAM/short-lived auth where the substrate offers it.
• Network exposure is reviewed explicitly: no public database endpoint without a justified, allowlisted, TLS-enforced exception; the default is private connectivity within the platform trust zone (consumed from infrastructure-platform, not redesigned here).
• Every control names the threat or boundary it enforces from the threat model. Controls without a mapped threat are removed (no security theater); threats without a control are reported as gaps.
• Encryption choices state their threat model: pgcrypto protects against DB-file/backup exposure but puts keys in SQL/clients; TDE protects at-rest media loss but not a compromised superuser. Name what each does and does not defend.

Output contract

The hardening design MUST conform to:

• security-standards — authentication schemes, authorization scopes, secret handling, TLS, and PII tagging are enforced at the engine consistent with the standard.
• architecture-schema § data ownership — grants respect single-component ownership; no cross-component write grants introduced.
• observability-standards — audit log routing, privileged-access signals, and auth-failure signals are named observable outputs with retention.
• naming-conventions — roles, policies, and audit objects follow project naming rules.

Upstream contract: security-architecture.md is the source of truth for the threat model, authorization model, trust boundaries, and tenant-isolation requirement. data-architecture.md / security-architecture.md is the source of truth for data classification. infrastructure-platform owns the network trust zone (consumed, not redesigned). If classification or an isolation boundary the design needs is unstated, pause and raise an ADR candidate against security.

Process

1. Establish the policy and classification. Extract the threat model, authorization model, trust boundaries, and tenant-isolation requirement from security-architecture.md; extract the data classification (PII/confidential/regulated tables and columns) from security-architecture.md / data-architecture.md. If anything required is unstated, pause and escalate.
2. Inventory the current security posture: roles and memberships, grants (including PUBLIC and ownership), pg_hba.conf, TLS/postgresql.conf security settings, existing RLS, column grants, encryption usage, audit configuration, and network exposure. Record each gap against a threat.
3. Design the role and grant model: a role hierarchy (login roles vs group roles), least-privilege grants per component, app roles that are not owners, revoked PUBLIC, and a privileged/break-glass role with audit. Map each grant to the authorization model.
4. Design tenant isolation: where the classification requires engine-enforced isolation, author RLS policies (FORCE ROW LEVEL SECURITY, per-tenant USING/WITH CHECK predicates, the session/role mechanism that carries tenant identity safely) and state the bypass risks (table owner, BYPASSRLS).
5. Design PII/column protection: column-level REVOKE/GRANT for restricted columns, view or masking strategy for analytic access, and column encryption posture — pgcrypto for application-tier-keyed columns vs substrate TDE for at-rest — each with its stated threat model and key-custody implication.
6. Design connection security: enforce TLS (server ssl=on, modern version/cipher floor, client verify-full expectation), scram-sha-256, and a default-deny pg_hba.conf with explicit per-source/per-db/per-role rules; eliminate trust and password (md5) auth for production.
7. Design the audit posture: configure pgaudit (or substrate-native) for the security-relevant event classes from the threat model (DDL, GRANT/role changes, privileged reads on regulated tables, failed auth), define log routing, retention, and tamper-evidence per observability-standards, and explicitly exclude high-volume low-value events.
8. Design the secret and credential rotation posture: rotation owner and cadence per credential class, elimination of static long-lived superuser in app config, preference for short-lived/IAM auth where the substrate supports it, and confirmation that the backup/restore bootstrap path (from the backup skill) does not depend on an unrotated embedded secret.
9. Review network exposure: confirm private connectivity within the platform trust zone, justify and allowlist any public exception with TLS enforced, and flag any endpoint reachable outside the intended boundary. Consume the trust-zone definition from infrastructure-platform; do not redesign it.
10. Produce the hardening assessment: a threat → control coverage matrix (threat/boundary → control → enforced where → residual risk), the role/grant model, RLS policies, column-protection plan, connection-security settings, audit configuration, rotation posture, and the network-exposure finding. Report threats with no control as gaps and controls with no threat as removable.
11. Verify. Apply the model against a representative database and test enforcement: a non-privileged role cannot read restricted columns, a cross-tenant query returns zero rows under RLS, non-TLS connections are refused, and audited events appear in the configured sink. Capture the test evidence. If a representative environment is unavailable, document each unverified control as an explicit open risk — do not declare hardened on untested policy.

Outputs

Required:

• security-hardening.md: threat → control coverage matrix, role/grant model, tenant-isolation (RLS) design, PII/column-protection design, connection-security settings, audit configuration, secret-rotation posture, and network-exposure findings.
• Role and grant DDL (or a precise change list) implementing least privilege, marked as a migration handoff to postgres-schema-and-migration where it alters schema-owned objects.
• RLS policy DDL and column GRANT/REVOKE statements, with the carrying mechanism for tenant identity.
• Connection-security configuration: pg_hba.conf rules, TLS and auth settings.

Conditional, when applicable:

• Column-encryption design (pgcrypto/TDE) with key-custody and threat-model statement.
• pgaudit configuration with event-class scoping and retention.
• ADR candidate(s) for unstated classification/isolation, encryption key-custody trade-offs, or justified public-exposure exceptions.

Output rules:

• Every control maps to a threat or boundary from security-architecture.md; unmapped controls are removed; unmitigated threats are reported as gaps.
• Tier-0/regulated multi-tenant isolation is engine-enforced (RLS), never application-only.
• DDL that alters schema-owned objects is handed off to postgres-schema-and-migration as a migration, not silently applied.
• Network trust zone is consumed from infrastructure-platform, not redesigned.

Quality checks

• Threat model, authorization model, tenant-isolation requirement, and data classification are sourced from security-architecture.md/data-architecture.md (or an ADR candidate is raised).
• No role has unjustified SUPERUSER/ALL/ownership; PUBLIC is revoked unless explicitly justified; app roles are not table owners.
• Tier-0/regulated multi-tenant data is isolated with FORCE ROW LEVEL SECURITY, with the tenant-identity carrying mechanism stated and bypass risks named.
• Restricted/PII columns are protected with column grants and/or encryption; application-only enforcement is not relied on for tier-0.
• TLS is enforced (server + client verify-full expectation), scram-sha-256 is used, and pg_hba.conf is default-deny with no production trust/md5.
• pgaudit (or native) covers the threat-model event classes with routing, retention, and tamper-evidence; high-volume low-value events are excluded.
• Secret/credential rotation has an owner and cadence; no static long-lived superuser in app config; restore bootstrap does not embed an unrotated secret.
• Network exposure is reviewed; any public endpoint is justified, allowlisted, and TLS-enforced; trust zone is consumed from infrastructure-platform.
• A threat → control coverage matrix exists; unmapped controls removed, unmitigated threats reported as gaps.
• Enforcement is tested (restricted-column denial, cross-tenant zero-rows, non-TLS refusal, audit-event presence) or each unverified control is logged as an explicit open risk.
• Schema-owned DDL changes are handed off to postgres-schema-and-migration, not silently applied.

References

• Upstream: architecture/security, architecture/data-architecture.
• Related implementation skills: postgres-schema-and-migration (owns DDL/migration mechanics this skill hands off), postgres-backup-and-operational-readiness (backup-secret custody overlap), postgres-replication-and-ha-readiness (replication-user privilege scope).
• Compatible patterns: multi-tenant-saas, microservices, modular-monolith.