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]
    on:
      type: table
      schema: public
      name: "*"

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]
    on: { type: schema, name: public }

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

Database-level

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

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

Wildcard (all objects in schema)

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

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

Generates: GRANT SELECT ON ALL TABLES IN SCHEMA "public" TO "analytics";

Specific object

grants:
  - role: analytics
    privileges: [SELECT]
    on: { 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]
    on: { type: table, schema: public, name: "*" }
  - role: app
    privileges: [INSERT, UPDATE]
    on: { 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.