ldap2pg.yml

#
# **LDAP2PG SAMPLE CONFIGURATION**
#
# This is a sample starting point configuration file for ldap2pg.yml. Including
# static roles, groups, privilege and LDAP query.
#
# This configuration assumes the following principles:
#
# - All LDAP users are grouped in `ldap_roles` group.
# - Read privileges are granted to `readers` group.
# - Write privileges are granted to `writers` group.
# - DDL privileges are granted to `owners` group.
# - We have one or more databases with public and maybe a schema.
# - Grants are not specific to a schema. Once you're writer in a database, you
#   are writer to all schemas in it.
#
# Adapt to your needs! See also full documentation on how to configure ldap2pg
# at https://ldap2pg.readthedocs.io/en/latest/config/.
#

verbosity: 5

postgres:
  # Scope the database where to purge objects when dropping roles. This is the
  # scope of grant on `__all__` databases.
  databases_query: [postgres, appdb, olddb]
  # List of managed schema. This skip pg_toast, pg_temp1, etc. but not pg_catalog.
  schemas_query: |
    SELECT nspname FROM pg_catalog.pg_namespace
    WHERE nspname = 'pg_catalog' OR nspname NOT LIKE 'pg_%'
  # Return managed roles which can be dropped or revoked.
  managed_roles_query: |
    SELECT DISTINCT role.rolname
    FROM pg_roles AS role
    LEFT OUTER JOIN pg_auth_members AS ms ON ms.member = role.oid
    LEFT OUTER JOIN pg_roles AS ldap_roles
      ON ldap_roles.rolname = 'ldap_roles' AND ldap_roles.oid = ms.roleid
    WHERE role.rolname IN ('ldap_roles', 'readers', 'writers', 'owners')
        OR ldap_roles.oid IS NOT NULL
    ORDER BY 1;

  # Since readers/writer/owners groups are globals, we have a global
  # owners_query.
  owners_query: |
    SELECT DISTINCT role.rolname
    FROM pg_catalog.pg_roles AS role
    JOIN pg_catalog.pg_auth_members AS ms ON ms.member = role.oid
    JOIN pg_catalog.pg_roles AS owners
      ON owners.rolname = 'owners' AND owners.oid = ms.roleid
    ORDER BY 1;


privileges:
  # Define an privilege group `ro` with read-only grants
  ro:
  - __connect__
  - __execute__
  - __select_on_tables__
  - __select_on_sequences__
  - __usage_on_schemas__
  - __usage_on_types__

  # `rw` privilege group lists write-only grants
  rw:
  - __all_on_tables__
  - __all_on_sequences__

  # `ddl` privilege group lists DDL only grants.
  ddl:
  - __create_on_schemas__


sync_map:
# First, setup static roles and grants
- roles:
  - names:
    - ldap_roles
    - readers
    options: NOLOGIN
  - name: writers
    # Grant reading to writers
    parent: readers
    options: NOLOGIN
  - name: owners
    # Grant read/write to owners
    parent: writers
    options: NOLOGIN
  # Now grant privileges to each groups
  grant:
  - privilege: ro
    role: readers
    # Let's everyone see pg_catalog
    schema: __all__
  - privilege: rw
    role: writers
    # But avoid writers to write in pg_catalog
    schema: public
  # Allow ddl to create tables in public only
  - privilege: ddl
    role: owners
    schema: public
  # owners must have write access to pg_catalog
  - privilege: rw
    role: owners
    schema: pg_catalog
  # Grants on specific schema appdb.appns:
  - privilege: rw
    role: writers
    database: appdb
    schema: appns
  - privilege: ddl
    role: owners
    database: appdb
    schema: appns

# Now query LDAP to create roles and grant them privileges by parenting.
- ldap:
    base: ou=groups,dc=ldap,dc=ldap2pg,dc=docker
    filter: "(cn=dba)"
  role:
    name: '{member.cn}'
    options: LOGIN SUPERUSER
    parent:
    - ldap_roles
    - owners
- ldap:
    base: ou=groups,dc=ldap,dc=ldap2pg,dc=docker
    filter: "(cn=app*)"
  role:
    name: '{member.cn}'
    options: LOGIN
    parent:
    - ldap_roles
    - writers
- ldap:
    base: ou=groups,dc=ldap,dc=ldap2pg,dc=docker
    filter: |
      (&
        (cn=bi)
        (objectClass=*)
      )
  role:
    name: '{member.cn}'
    options: LOGIN
    parent:
    - ldap_roles
    - readers