Inspecting Postgres cluster

ldap2pg follows the explicite create / implicit drop and explicit grant / implicit revoke model. Thus properly inspecting cluster for what you want to drop/revoke is very crucial to succeed in synchronisation.

ldap2pg inspects databases, schemas, roles, owners and grants with SQL queries. You can customize all these queries in the postgres YAML section with parameters ending with _query.

What databases to synchronize ?

databases_query return the flat list of databases to manage. When dropping roles, ldap2pg loop the databases list to reassign objects and clean GRANTs of to be dropped role. This databases list also narrow the scope of GRANTs inspection. ldap2pg will revoke GRANTs only on these databases. By default, ldap2pg lists all databases with connection allowed. This includes template1 and postgres.

  databases_query: |
    SELECT datname
    FROM pg_catalog.pg_database
    WHERE datallowconn IS TRUE;

Synchronize a subset of roles

By default, ldap2pg inspects all roles from Postgres. If you want ldap2pg to synchronsize only a subset of roles, you need to customize inspection query in postgres:managed_roles_query.

  # Inspect only non SUPERUSER roles.
  managed_roles_query: |
    SELECT rolname
    FROM pg_catalog.pg_roles
    WHERE rolsuper IS FALSE
    ORDER BY 1;

ldap2pg will only drop, revoke, grant on roles returned by this query. This whitelist also applies to members. Only members matching this list may be removed from a group. Members not matching this list will be left in the group.

A common case for this query is to return only members of a group like ldap_roles. This case is tested in ldap2pg.yml sample. This way, ldap2pg is scoped to a subset of roles in the cluster.

A safety net to completely ignore some roles is available : blacklist. blacklist is a list of glob patterns. Every roles matching one of blacklist patterns will be totally ignored from roles and privileges synchronisation.

  # This is the default.
  blacklist: [postgres, pg_*]


A pattern starting with a * must be quoted. Else you’ll end up with a YAML error like found undefined alias.

Inspecting Schema & Owners

Except with database and global default privileges, almost all privileges are schema aware. Thus ldap2pg needs to known what schemas are in each database. This is the purpose of schemas_query.

When managing ALTER DEFAULT PRIVILEGES with ldap2pg, you must tell who are owners. Owners are roles supposed to create or drop objects in database such as tables, views, functions, etc. ldap2pg checks that every owners have proper default privileges. This way you dont have to re-run ldap2pg after update on your SQL schema to grant privileges.

There is two ways of listing owners: globally or per schema. With owners_query you can specify a global list of owners common to all databases and all schemas. With schemas_query you can specify owners per schema.

The managed_roles whitelist applies to owners from either schemas_query or owners_query.

Global Owners Example

This is the default configuration:

  schemas_query: |
    SELECT nspname FROM pg_catalog.pg_namespace;
  owners_query: |
    SELECT role.rolname
    FROM pg_catalog.pg_roles AS role
    WHERE role.rolsuper IS TRUE;

Per-Schema Owners Example

In this example, each schema is associated with a owners_% group whose name includes schema name.

  schemas_query: |
      array_agg(owner.rolname) FILTER (WHERE rolname IS NOT NULL)
    FROM pg_catalog.pg_namespace
    LEFT OUTER JOIN pg_catalog.pg_roles AS owners_group
      ON owners_group.rolname = 'owners_' || nspname
    LEFT OUTER JOIN pg_catalog.pg_auth_members AS ms ON ms.roleid = owners_group.oid
    LEFT OUTER JOIN pg_catalog.pg_roles AS owner ON owner.oid = ms.member
    GROUP BY 1

Static Queries

You can replace all queries with a static list in YAML. This list will be used as if returned by Postgres. That’s very handy to freeze a value like databases or schemas.

  databases_query: [postgres]