Grants & privileges

Grants define what privileges a role has on database objects. pgroles supports granting on specific objects, all objects of a type in a schema, schemas themselves, and databases.

Grant syntax

grants:
  - role: analytics
    privileges: [SELECT]
    object:
      type: table
      schema: public
      name: "*"

The preferred key is object. pgroles still accepts a quoted legacy "on" key when parsing older manifests, but new manifests should use object to avoid YAML 1.1 boolean coercion.

Privilege types

Grant targets

Schema-level

Grant privileges on the schema itself (e.g. USAGE to allow accessing objects within it):

grants:
  - role: analytics
    privileges: [USAGE]
    object: { type: schema, name: public }

Generates: GRANT USAGE ON SCHEMA "public" TO "analytics";

Database-level

grants:
  - role: analytics
    privileges: [CONNECT]
    object: { type: database, name: mydb }

Generates: GRANT CONNECT ON DATABASE "mydb" TO "analytics";

Wildcard (all objects of a type in schema)

Use name: "*" to grant on all existing objects of a type:

grants:
  - role: analytics
    privileges: [SELECT]
    object: { type: table, schema: public, name: "*" }

pgroles expands wildcard relation grants against the current objects of the requested type in that schema. That keeps table, view, and materialized_view grants scoped correctly instead of letting one subtype touch the others.

If a schema has no objects of the declared type (e.g. no sequences yet), the wildcard grant is treated as vacuously satisfied — pgroles will not re-issue the statement on subsequent reconciles. When objects are later added, the next reconcile detects the new objects and applies the appropriate grants.

Wildcard grants are strict desired state. name: "*" means every matching current object in the schema, not only the objects the current executor happens to own. During diff, plan, and apply, pgroles checks whether each missing wildcard privilege is grantable by the connected database user. If a matching object is missing the requested privilege and the executor lacks the matching WITH GRANT OPTION, pgroles stops with UnsatisfiableWildcardGrant instead of printing or applying a wildcard GRANT that would churn on every reconcile.

The diagnostic includes the role, object type, schema, requested privileges, executor, skipped object count, and example object names with owners. To resolve it, run pgroles as a role that can grant the requested privileges on every matching object, transfer ownership, grant the executor the needed grant option, or narrow the manifest to objects that are intentionally managed by that executor.

Specific object

grants:
  - role: analytics
    privileges: [SELECT]
    object: { type: table, schema: public, name: users }

Generates: GRANT SELECT ON TABLE "public"."users" TO "analytics";

Privilege merging

If multiple grant entries target the same role and object, their privileges are merged:

grants:
  - role: app
    privileges: [SELECT]
    object: { type: table, schema: public, name: "*" }
  - role: app
    privileges: [INSERT, UPDATE]
    object: { type: table, schema: public, name: "*" }

This is equivalent to granting SELECT, INSERT, UPDATE on all tables.

Convergent revocation

Privileges present in the database but absent from the manifest are revoked. If a role currently has DELETE on a table but your manifest only grants SELECT, pgroles will generate a REVOKE DELETE statement.