Can schema be created dynamically using ldap2pg ?

[jeanpommier]

Hi,
Thanks for this tool (and for sharing it). I'm still in the phase of checking if it can fulfill my needs

What do you want?

In my LDAP records, I will have roles with the following syntax PSQL_[SCHEMA]_[ROLE]
For instance PGSQL_STATSOFFICE_WRITER means that the member of this role will have write privileges to the statsoffice schema. All will happen in a single database.

I'm still trying to figure out how to configure this. But my question for now is: can I configure ldap2pg to create the schemas hinted by this syntax if they don't exist ?

Thanks !

ldap2pg.yml

ldap2pg.yml

``` yml ... ```

[bersace]

Est-ce un doublon de #171 ?

[jeanpommier]

Mmmh, yes, I think so, thanks for pointing up this ticket, I hadn't seen it.
I'd like the schema to be named statsoffice (just a substring of the pgsql_statsoffice_writer role) but I suppose I'll be able to manage this with some string manipulations in the SQL query.

The next step will be to be able to refer to this schema in the grant rule. Is there a way to do it, in case it's only a substring (e.g. statsoffice in this case) ?

[jeanpommier]

Je crois que je commence à perdre pied dans la configuration. Serait-il possible, par hasard, de t'embaucher pour m'écrire la config ? Tu peux me contacter à jean.pommier à pi-geosolutions.fr

[jeanpommier]

Hi,
I'm almost there !
I've got a pb though:

• if my config file only deals with creating the schema, it works well and creates the schema
• if my config file also manages some user roles and grants that refer to the said schema, it fails. Probably because the schema is not created yet.

[ldap2pg.script CRITI] Unknown schema georchestra.ksk.

Is there a way to define some priorities ? Or do I have to run twice ldap2pg, the first run aiming at creating the schemas, on which the next run wil create the roles and grants ?

working ldap2pg.yml (only create schemas)

ldap:
  uri: ldap://192.168.1.70:3389
  binddn: cn=admin,dc=georchestra,dc=org
  password: secret

postgres:
  #dsn: "host=192.168.1.70 port=5432 user=georchestra dbname=georchestra"
  dsn: postgres://georchestra:[email protected]:5434/georchestra
  databases_query: |
    SELECT datname
    FROM pg_catalog.pg_database
    WHERE datallowconn IS TRUE
    AND datname NOT IN ('postgres','template1', 'template_postgis');

  # List of role names which can be dropped from cluster. Privileges on these
  # roles can be revoked.
  managed_roles_query: |
    SELECT 'public'
    UNION
    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;

  # List of object owners that requires default privileges configuration. Since
  # readers/writer/owners groups are globals to cluster, 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;

  # Exclude information_schema, pg_catalog, pg_toast, and other system schemas
  # from privilege management.
  schemas_query: |
    SELECT
      nspname,
      array_agg(owner.rolname) FILTER (WHERE owner.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
    WHERE nspname NOT LIKE 'pg_%' AND nspname <> 'information_schema'
    GROUP BY 1

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

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

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

  # Hack to create the schema if necessary
  create-user-schema:
    type: datacl
    inspect: |
      SELECT NULL as "schema", rolname
      FROM pg_namespace n
      JOIN pg_authid ON rolname = nspname
    grant: |
      CREATE SCHEMA IF NOT EXISTS {role};
    revoke: |
      DROP SCHEMA IF EXISTS {role} RESTRICT;

sync_map:
- description: "Query LDAP to create schemas."
  ldap:
    base: ou=roles,dc=georchestra,dc=org
    filter: "(cn=PGSQL_*)"
  grant:
  - privilege: create-user-schema
    schema: "{cn}"
    role: "{cn}"

failing ldap2pg.yml (fails with `Unknown schema georchestra.ksk`)

ldap:
  uri: ldap://192.168.1.70:3389
  binddn: cn=admin,dc=georchestra,dc=org
  password: secret

postgres:
  #dsn: "host=192.168.1.70 port=5432 user=georchestra dbname=georchestra"
  dsn: postgres://georchestra:[email protected]:5434/georchestra
  databases_query: |
    SELECT datname
    FROM pg_catalog.pg_database
    WHERE datallowconn IS TRUE
    AND datname NOT IN ('postgres','template1', 'template_postgis');

  # List of role names which can be dropped from cluster. Privileges on these
  # roles can be revoked.
  managed_roles_query: |
    SELECT 'public'
    UNION
    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;

  # List of object owners that requires default privileges configuration. Since
  # readers/writer/owners groups are globals to cluster, 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;

  # Exclude information_schema, pg_catalog, pg_toast, and other system schemas
  # from privilege management.
  schemas_query: |
    SELECT
      nspname,
      array_agg(owner.rolname) FILTER (WHERE owner.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
    WHERE nspname NOT LIKE 'pg_%' AND nspname <> 'information_schema'
    GROUP BY 1

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

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

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

  # Hack to create the schema if necessary
  create-user-schema:
    type: datacl
    inspect: |
      SELECT NULL as "schema", rolname
      FROM pg_namespace n
      JOIN pg_authid ON rolname = nspname
    grant: |
      CREATE SCHEMA IF NOT EXISTS {role};
    revoke: |
      DROP SCHEMA IF EXISTS {role} RESTRICT;

sync_map:
- description: "Query LDAP to create schemas."
  ldap:
    base: ou=roles,dc=georchestra,dc=org
    filter: "(cn=PGSQL_*)"
  grant:
  - privilege: create-user-schema
    schema: "{cn}"
    role: "{cn}"

- description: "Query LDAP to create schemas _readers, _writers and _owners."
  ldap:
    base: ou=roles,dc=georchestra,dc=org
    filter: "(cn=PGSQL_*)"
  roles:
  - name: '{cn}_readers'
    parent: ldap_roles
    options: NOLOGIN
  - name: '{cn}_writers'
    parents:
    - ldap_roles
    - '{cn}_readers'
    options: NOLOGIN
  - name: '{cn}_owners'
    parents:
    - ldap_roles
    - '{cn}_writers'
    options: NOLOGIN
  grant:
  - privilege: ro
    role: '{cn}_readers'
    schema: "{cn}"
  - privilege: rw
    role: '{cn}_writers'
    schema: "{cn}"
  - privilege: ddl
    role: '{cn}_owners'
    schema: "{cn}"

[bersace]

@jeanpommier privileges are applyied in a determined order: The sort key is (dbname, grantee, privilege name, schema, owner). You may prefix privilege to ensure schema is created first.

[jeanpommier]

Nice, thanks !
Closing the ticket, then