Proposal: aggregated postgresql_grant_policy resource

[ochaichenko]

Proposal: aggregated postgresql_grant_policy resource

Summary

Following up on #654 (aggregated postgresql_default_privileges_policy), I
would like to contribute a matching aggregated resource for plain grants:

postgresql_grant_policy

The existing postgresql_grant resource models one
(database, schema, role, object_type) tuple per Terraform resource. Like
postgresql_default_privileges, this scales poorly once a database has many
roles receiving grants across many schemas and object types.

The proposed resource changes the Terraform identity to one resource per
(database, role) and manages the individual grants as structured entries
inside that resource — the same shape as postgresql_default_privileges_policy,
applied to plain grants instead of default privileges.

Problem

A typical configuration currently expands approximately as:

database x schema x role x object_type

1. Combinatorial explosion in Terraform state

Every (schema, role, object_type) combination for a database becomes its
own independent postgresql_grant resource, even though PostgreSQL itself
stores and updates them as ACL entries on the same underlying objects. In
production configurations we measured, a single database/role combination
routinely expands into 3-5 separate postgresql_grant resources (database
connect, schema usage, table privileges, sometimes split further), and a
mid-sized installation accumulates on the order of 1,000+ such resources.

2. Concurrent reconciliation and deadlocks

Terraform refreshes and applies these resources concurrently under the
default parallelism. Multiple postgresql_grant resources for roles that
share a schema owner independently acquire pgLockRole and issue
GRANT/REVOKE. On sufficiently large configurations this produces
intermittent

pq: deadlock detected (40P01)

during apply, and the failure is non-deterministic — it depends on plan
ordering and parallelism, not on any single resource. Serializing with
-parallelism=1 avoids the deadlock but makes plans/applies for the whole
configuration impractically slow, and depends_on chains only address apply
ordering, not the underlying refresh cardinality.

3. Heavy Terraform graph and state

Independent of the deadlock class above, thousands of same-shaped
postgresql_grant resources make the Terraform graph and state file
themselves expensive to work with: every plan/refresh walks and reads
every one of them individually, state files grow into multiple megabytes for
a single workspace, and reviewing a plan diff across that many resources is
impractical for a human reviewer. Aggregating by (database, role) — the
boundary at which PostgreSQL privilege administration is actually reasoned
about — collapses this without changing what is actually granted.

Proposed resource

resource "postgresql_grant_policy" "product_service_ro" {
  database      = "product_service"
  role          = "product_service_role_ro"

  # Safe default: preserve grants not managed by this resource.
  authoritative = false

  grants = [
    {
      object_type = "database"
      privileges  = ["CONNECT"]
    },
    {
      schema      = "public"
      object_type = "schema"
      privileges  = ["USAGE"]
    },
    {
      schema      = "public"
      object_type = "table"
      privileges  = ["SELECT"]
      # objects           = ["orders", "order_items"] # optional; omitted = every table in schema
      # with_grant_option = false                      # optional, defaults to false
    }
  ]
}

Resource identity:

(database, role)

Grant identity inside the policy:

(schema, object_type)

objects, privileges and with_grant_option are values on the entry, not
part of its identity — in every configuration we generate today, a given
(database, role, schema, object_type) tuple never needs to be split across
two different object lists.

Supported object types:

database, schema, table, sequence, function, procedure, foreign_data_wrapper, foreign_server

This is a subset of postgresql_grant's allowedObjectTypes, deliberately
excluding:

• column — keyed by (table, column, single privilege), which does not fit
  the (schema, object_type) entry shape every other type here shares (a
  column grant has no meaningful "whole schema" form). postgresql_grant
  remains the right resource for column-level grants.
• routine — an alias of function/procedure at the catalog level
  (aclexplode has no dedicated "routine" tag); the same exclusion already
  made in postgresql_default_privileges_policy.
• type — postgresql_grant validates it as an allowed value but has no
  actual GRANT/REVOKE query implemented for it, so there is nothing
  meaningful to aggregate.

schema is required and denotes the containing schema for
table/sequence/function/procedure entries, denotes the target itself for a
schema-type entry, and must be empty for database/foreign_data_wrapper/
foreign_server entries. objects is required (non-empty) for
foreign_data_wrapper/foreign_server (there is no "ALL FOREIGN DATA WRAPPERS"
grant form in PostgreSQL), disallowed for database/schema, and optional for
table/sequence/function/procedure, where an empty/omitted list means every
object of that type in the schema (ALL <TYPE>S IN SCHEMA).

Reconciliation semantics

All reads and mutations for one policy are performed through one database
connection and one transaction. pgLockRole is reused exactly as-is —
because the resource boundary matches its lock scope 1:1 (role OID plus every
member OID), two grant_policy resources for different roles lock disjoint
OID sets, so no new deadlock class is introduced beyond what
postgresql_default_privileges_policy already carries. A second advisory
lock, pgLockDatabase, is additionally taken whenever a declared/managed
entry has object_type = "database", or the policy is authoritative (since
authoritative reconciliation may discover, and need to revoke, a
database-level grant even when none is declared) — this mirrors exactly when
plain postgresql_grant itself takes that same lock today.

For every declared entry, in deterministic sorted order, reconciliation
performs, inside the single transaction:

1. REVOKE ALL on that entry's exact scope.
2. GRANT the declared privilege set (skipped if the declared privilege list
   is empty).
3. Commit only after every entry succeeds.

Revoking and granting inside the same transaction, rather than via
destroy/recreate diffing, closes the revoke-then-regrant window described in
#321.

Additive mode (default, authoritative = false)

• Declared grants are created and updated.
• Grants this resource previously created and later removed from
  configuration are revoked.
• Grants only ever observed in PostgreSQL, but never created by this
  resource, are preserved — including ones for a (schema, object_type) this
  resource never declares at all.
• Destroy revokes only grants tracked as managed by this resource.

This is implemented via a computed managed_grants ledger, written only by
Create/Update, never by Read or Import — so a grant merely observed (for
example, one made by an external SQL script for a combination never declared
in grants) can never be classified as managed and can never be revoked by
additive-mode reconciliation.

Authoritative mode (authoritative = true)

• The resource owns every grant found for role in database, across the
  object types it manages.
• Apply revokes any such grant that is not declared in grants, including
  ones created outside Terraform.
• Destroy revokes all such grants for that (database, role) pair.

This mode must not be mixed with independent postgresql_grant resources or
manual management for the same (database, role) pair.

Read and drift behavior

Read runs one bulk discovery query per call — a single UNION ALL of
aclexplode(...) over pg_database.datacl, pg_namespace.nspacl,
pg_class.relacl (split by relkind into table vs. sequence),
pg_proc.proacl (function vs. procedure, distinguished by prokind where
supported), pg_foreign_data_wrapper.fdwacl and pg_foreign_server.srvacl,
filtered to the target role and grouped into one row per real object with
array_agg(privilege) and bool_and(grantable). This one query serves every
caller — Read, Create/Update's authoritative-mode discovery, Delete's
authoritative-mode cleanup, and Import — with per-entry filtering/aggregation
done afterward, rather than one query per declared entry.

For every entry already declared in state, Read intersects privileges (and
ANDs with_grant_option) across every live row matching that entry's
(schema, object_type) and, when objects is non-empty, matching object
name. A declared entry naming a since-dropped schema, table or role reads
back as an empty privilege set rather than erroring, so drift from a deleted
object surfaces as an ordinary plan diff, not a failure. with_grant_option
is read back on every Read via bool_and(grantable) — closing a real gap in
today's postgresql_grant, where it is write-only and drift on that
attribute is invisible.

Import and migration

Import ID: <database>/<role>.

Import runs the same bulk discovery query unconditionally and seeds grants
with everything found — there is no prior declared list to diff against.
Rows sharing a (schema, object_type) are grouped by their exact
(privileges, with_grant_option) signature: when every object sharing a key
has the identical signature, they collapse into one whole-schema/whole-database
entry (objects empty); when signatures differ, each distinct signature
becomes its own entry with objects listing exactly the objects that share
it — so import always reproduces live state exactly, never silently widening
or narrowing what a subsequent apply would do.
foreign_data_wrapper/foreign_server always get explicit objects
regardless of signature grouping, since there is no "ALL FOREIGN DATA
WRAPPERS" grant form to fall back on. The imported resource always starts
with authoritative = false, since ownership intent cannot be inferred from
PostgreSQL state, and managed_grants is left empty (Import never writes the
ledger).

We migrated existing installations without executing any GRANT/REVOKE:

1. Generate the policy configuration from existing granular
   postgresql_grant resources.
2. Import each (database, role) policy from live PostgreSQL.
3. Verify that every expected grant entry is present in the imported policy
   state.
4. Remove the old granular resource addresses from Terraform state only.
5. Run a clean plan.

This allows a state-only migration with no revoke/grant window — the only
mutation is to Terraform state, never to PostgreSQL.

Production validation

The implementation has been rolled out to full completion across every
managed environment in an internal multi-environment PostgreSQL fleet — 10
environments total (three deployment groups, each with development, staging
and production tiers; one group has no separate "shared" tier, so it
contributes 3 environments rather than 4). Numbers below are anonymized
aggregates; no environment or organization names are included.

Deployment group	postgresql_grant before → after	Policies after	Total state instances	State size
Group 1 (4 environments)	3,628 → 0	684	5,806 → 2,862 (-50.7%)	10.3 → 8.8 MiB (-14.6%)
Group 2 (4 environments)	440 → 0	85	748 → 393 (-47.5%)	1,288 → 1,104 KiB (-14.3%)
Group 3 (3 environments)	891 → 0	176	1,453 → 730 (-49.8%)	2,464 → 2,088 KiB (-15.3%)
Total (10 environments)	4,959 → 0	945 (-80.9% grant-resource reduction)	8,007 → 3,985 (-50.2%)	≈13.96 → ≈11.91 MiB (-14.7%)

One representative single-environment example (a development-tier
environment in Group 1):

postgresql_grant:        1,147 -> 0
postgresql_grant_policy:     0 -> 211
total state instances:   1,759 -> 823   (-53.2%)
state size:               3.1 -> 2.6 MiB (-16.1%)

All 4,959 legacy grant resources remain fully represented inside the 945
resulting policies. Every environment's final terraform plan after
migration was clean (0 add, 0 destroy), with only pre-existing, unrelated
drift left as "to change" — none of it introduced by the migration itself.
No GRANT/REVOKE SQL was executed by the state-only migration step in any
environment; the observed grant sets were bit-for-bit identical before and
after.

Real incidents hit and resolved during the rollout, none specific to the
resource's own logic:

• A genuine PostgreSQL deadlock detected (40P01) during one apply, caused
  by role-elevation for a shared schema owner racing under default
  parallelism — resolved by retrying the same apply (idempotent); a
  follow-up to cover this case explicitly in pgLockRole/role-elevation
  locking is tracked separately.
• An operational mistake (a migration-tooling background process not fully
  terminated before a later batch operation), unrelated to the resource
  implementation, causing a transient state-count mismatch — resolved by
  fully stopping all processes and re-converging idempotently.

Acceptance test coverage

The implementation has acceptance-test coverage for:

• basic create and read;
• adding, widening and removing grants and grant entries;
• additive preservation of unmanaged grants;
• authoritative cleanup of undeclared/external grants;
• import, including signature-based grouping into whole-scope vs.
  explicit-object entries;
• destroy behavior (additive and authoritative);
• WITH GRANT OPTION;
• database-level, schema-level, and explicit-object-list entries;
• validation of duplicate (schema, object_type) identities and
  unsupported/invalid combinations (e.g. objects on a database/schema
  entry, missing schema on a table entry).

Known, explicitly-tracked gaps, not silently omitted:

• no dedicated fault-injection test forcing a mid-transaction SQL error to
  exercise the rollback path (relies on code inspection: every path defers
  rollback before commit);
• no dedicated end-to-end test for a declared entry whose backing
  object/schema/role is dropped between applies (the code path exists —
  Read reports it as an empty privilege set rather than erroring — but isn't
  exercised against a real DROP);
• no load/stress test for a policy with 100-500 entries;
• no dedicated concurrency test for multiple independent policies applying
  in parallel without deadlocking.

Compatibility

This would be a new resource and would not change the behavior or state
schema of postgresql_grant. Users could continue using the granular
resource. The two resource types must not manage the same grant entries
concurrently, especially when the policy is authoritative.

Relationship to #654

This proposal deliberately mirrors #654's resource shape and lifecycle
semantics (additive/authoritative toggle, computed managed-entries ledger,
single-transaction reconciliation, same import philosophy) so that the two
aggregated resources stay structurally consistent for anyone adopting both.

Status

A working implementation — resource, provider registration, documentation
and acceptance tests — already exists in an internal fork and has been
validated against the production rollout described above. I can prepare a
focused upstream PR (implementation, tests, docs, changelog only — no
internal tooling or migration automation) once the API and lifecycle
semantics below are agreed with maintainers.

Questions for maintainers

1. Is one resource per (database, role) an acceptable ownership boundary,
   consistent with Proposal: aggregated postgresql_default_privileges_policy resource #654's (database, owner) boundary for default
   privileges?
2. Is a separate resource preferable to extending postgresql_grant itself?
3. Should additive and authoritative behavior be exposed through a boolean,
   as proposed here and in Proposal: aggregated postgresql_default_privileges_policy resource #654, or would an explicit mode string be
   clearer?
4. Is a computed managed-grants ledger acceptable for safely revoking
   entries removed from additive configuration?
5. Should import populate all observed grants, or should it require users to
   declare the grant list before import?
6. Are there object types (in particular column) that should be handled
   differently than "excluded from this resource, use postgresql_grant
   directly"?

[ochaichenko]

Hi maintainers, following up on this proposal. I have the implementation, documentation, and acceptance tests ready and can open a focused draft PR if reviewing the concrete code would be more useful than discussing the API in the abstract. Please let me know if that would be welcome.