Support on ldap2pg.yml [includes rules loop]

[peteerwhite]

Hey all,

is it possible to assign all users from a LDAP group to a group in postgres? So like the command "CREATE USER in GROUP localaccount". So that I assign the group localaccount to all members of the ldapsearch?

Kind regards and thanks for help :)

[bersace]

Hi @peteerwhite . It's a bit tricky in ldap2pg 5.9 but ldap2pg 6.0 handles this very smoothly.

See https://ldap2pg.readthedocs.io/en/v5/cookbook/#inherit-unmanaged-role for an howto.

[bersace]

ldap2p 6.0 is near to come. It's a matter of days.

[peteerwhite]

Okay, thank you very much. Then I'll wait a few more days for the new version :)

I still have another question. Is it possible to distribute a YAML file on several servers without having to specify the database names directly? And then have them determined later somehow ?

[bersace]

I still have another question. Is it possible to distribute a YAML file on several servers without having to specify the database names directly? And then have them determined later somehow ?

Yes, of course. Actually, I always suggest to configure libpq and libldap outside ldap2pg.yml. One solution is to configure postgres:databases_query like this:

postgres:
  # no dsn (ldap2pg 6 dropped this)
  databases_query: SELECT current_database();

The run PGDATABASE=mydb ldap2pg and you're done.

Regards, Étienne

[peteerwhite]

Thank you very much for your help. That helps me a lot.

Is it also possible to get the result of databases_query: SELECT currentdatabase(); in a variable or something similar ? I have LDAP groups with the name "LDAP{DATABASENAME}_(READ/WRITE)" and would like to search for them in ldapsearch.

Note: Sometimes there are 2 databases on one server and I would like to separate them.

[bersace]

No. You may use envsubst for this.

PGDATABASE=mydb
envsubst < ldap2pg.yml | ldap2pg -c -

En use $PGDATABASE in YAML.

[bersace]

I'm thinking of a feature in ldap2pg: loops. Something like:

rules:
- loop:
    database: [mydb]
  ldapsearch:
    base: cn={database},ou=group,dc=acme,dc=tld
  role:
  - name: "{database}_readers"

I'm not sure about the design yet.

[bersace]

The idea would be to feed loop variables from env, csv, http, etc.

[peteerwhite]

Thanks, that would be a great feature :)

[peteerwhite]

I'm not sure if I've over-read it, but is it possible to blacklist all members of a role? For example, I have all local users in a group called "local". How do I get all these users blacklisted?

[peteerwhite]

ldap2p 6.0 is near to come. It's a matter of days.

Do you already know more about this?

[bersace]

ldap2p 6.0 is near to come. It's a matter of days.

Do you already know more about this?

Just released. https://blog.dalibo.com/2023/09/01/ldap2pg-6-en.html

[bersace]

I'm not sure if I've over-read it, but is it possible to blacklist all members of a role? For example, I have all local users in a group called "local". How do I get all these users blacklisted?

There is two level of blacklist : a hard blacklist where ldap2pg totaly ignore the existence of the role. This is configured by roles_blacklist_query which can be an SQL query.

There's also a soft blacklist that allows ldap2pg to reuse an existing role, but will not drop it. This is configured by managed_roles_list (which is a whitelist actually). Any role in neither roles_blacklist_query and managed_roles_query is soft blacklisted.

If you want ldap2pg to synchronize a subset of an instance roles, you'd better use managed_roles_query, it's for this purpose. Just return any roles not in local group. Usually, it's done the other way around : ldap2pg synchronizes only roles in ldap_roles group. This is tested in https://github.com/dalibo/ldap2pg/blob/master/test/ldap2pg.extra.yml#L20-L30 .

I'm thinking of a managed_roles_group shortcut to handle this case with more simplicity.

[peteerwhite]

Is it possible in version 5.9 to edit the SQL to create the role ?

[peteerwhite]

And is there a built-in privilege for create on database in version 5.9?

Thanks for help :)

[peteerwhite]

Do you know if the new version 6 will be available in the commons repo of postgresql at some point? (https://download.postgresql.org/pub/repos/yum/common/redhat/rhel-8-x86_64/)

[bersace]

Is it possible in version 5.9 to edit the SQL to create the role ?

No. What is your need ?

[bersace]

Do you know if the new version 6 will be available in the commons repo of postgresql at some point? (https://download.postgresql.org/pub/repos/yum/common/redhat/rhel-8-x86_64/)

No idea. You have an issue tracker for this repository at https://redmine.postgresql.org/projects/pgrpms/ .

[bersace]

And is there a built-in privilege for create on database in version 5.9?

Thanks for help :)

Yes : __connect__

Sorry, no, only CONNECT and TEMPORARY are builtin for 5.9. You have to setup your own __create_on_database__. ldap2pg 6.0 allows you to manage CREATE ON DATABASE.

[peteerwhite]

Is it possible in version 5.9 to edit the SQL to create the role ?

No. What is your need ?

I want to execute ALTER ROLE {role} SET log_statement='all'; after a role creation.

[bersace]

Is it possible in version 5.9 to edit the SQL to create the role ?

No. What is your need ?

I want to execute ALTER ROLE {role} SET log_statement='all'; after a role creation.

ldap2pg 6.0 handles this with config stanza:

https://ldap2pg.readthedocs.io/en/latest/config/#role-config

- roles:
  - name: my-db-writer
    config:
      log_statement: mod
      log_min_duration_sample: 100

[peteerwhite]

Thank you very much for your help. That helps me a lot.

[bersace]

Something like:

    WITH grants AS (
      SELECT
        (aclexplode(datacl)).grantee AS grantee,
        (aclexplode(datacl)).privilege_type AS priv
      FROM pg_catalog.pg_database
      WHERE datname = current_database()
    )
    SELECT
      grants.priv AS key,
      NULL as namespace,
      COALESCE(rolname, 'public')
    FROM grants
    LEFT OUTER JOIN pg_catalog.pg_roles AS rol ON grants.grantee = rol.oid
    WHERE grantee = 0 OR rolname IS NOT NULL AND grants.priv = 'CREATE'