Inspecting Postgres cluster
ldap2pg follows the explicit 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 synchronization.
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. See ldap2pg.yaml reference for details.
What databases to synchronize ?¶
databases_query returns the flat list of databases to manage. When dropping
roles, ldap2pg loops the databases list to reassign objects and clean GRANTs of
to be dropped role. This databases list also narrows the scope of GRANTs
inspection. ldap2pg will revoke GRANTs only on these databases. See
ldap2pg.yaml reference for details.
Synchronize a subset of roles¶
By default, ldap2pg manages all roles from Postgres, minus the default
blacklist. If you want ldap2pg to synchronsize only a subset of roles, you need
to customize inspection query in
postgres:managed_roles_query. The following
query excludes superusers from synchronization.
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
This way, ldap2pg is scoped to a subset of roles in the cluster.
public role does not exists in the system catalog. Thus if you want
ldap2pg to manage
public privileges, you must include explicitly
the set of managed roles. This is the default. Of course, even if
managed, ldap2pg won’t drop or alter it if it’s not in the directory.
A safety net to completely ignore some roles is roles_blacklist_query.
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 privileges 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
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 when an owner
creates a new object in the cluster.
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.
Per-Schema Owners Example¶
In this example, each schema is associated with a
owners_% group whose name
includes schema name.
postgres: schemas_query: | SELECT nspname, 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
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.