Remove User from a Group not remove it from Database?

[pc-dok]

hi all, i have done some tests in the last days with ldap2pg. i test a owner role, what is working fine. i have 2 members in this role. when i remove now one member from this ldap group and i rerun ldap2pg it not delete me the user from the database. so is this not the exact feature from ldap2pg. so when a member leaves company, remove it from ldap, and ldap2pg remove it from postgres also?

regards
franco

[bersace]

ldap2pg drops only roles returned by managed_roles_query. Does this query returns the to-drop role ?

[pc-dok]

  # The roles which get managed by ldap2pg
  managed_roles_query: |
    VALUES
      ('pgdb_dba')
    UNION
    SELECT DISTINCT role.rolname
    FROM pg_roles AS role
    JOIN pg_auth_members AS ms ON ms.member = role.oid
    JOIN pg_roles AS parent
      ON parent.rolname in ('pgdb_dba') AND parent.oid = ms.roleid
    ORDER BY 1;

[pc-dok]

11:25:12 DEBUG Privilege synchronized. privilege="SCHEMA DEFAULT" database=test1
11:25:12 INFO All default privileges configured. database=test1
11:25:12 INFO Nothing to do. elapsed=159.672039ms mempeak=0.7MiB postgres=0s queries=0 ldap=1.528708ms searches=1
11:25:12 DEBUG Closing Postgres global connection. database=test1

[bersace]

You need a Managing role. role=testdrop line. Else ldap2pg wont drop testdrop role.

[bersace]

What's the output of \drg in psql ?

[pc-dok]

[local] postgres@postgres=# \drg
List of role grants
Role name | Member of | Options | Grantor
-----------+-----------+---------+---------
(0 rows)

[local] postgres@postgres=#

[bersace]

Which version of Postgres are you running ? It looks like pgdb_dba does not have members.

[pc-dok]

rules:
- description: "Setup static roles and grants."
#  roles:
#  - names:
#    - pgdb_dba
#    options: NOLOGIN
  roles:
  - name: pgdb_dba
    options:
      LOGIN: no
      CREATEDB: no
  grant:
  - privilege: owner
    role: pgdb_dba
    owner: pgdb_dba

[pc-dok]

hmm you mean i need some additional role, like role ldap_role

roles:
  - name: pgdb_dba
    parents:
    - ldap_roles
    options:
      LOGIN: no
      CREATEDB: no

  - name: ldap_roles
    options:
      LOGIN: no
      CREATEDB: no

[bersace]

Yes, you need to set parent explicitly.

[pc-dok]

this is my ldap2pg.yml

# File format version. Allows ldap2pg to check whether the file is supported.
version: 6

postgres:
  # These roles will not be touched by ldap2pg
  roles_blacklist_query: [postgres, pg_*]
  # The database which gets managed by this configuration
  databases_query: |
    SELECT datname
      FROM pg_catalog.pg_database
     WHERE datallowconn IS TRUE
       AND datistemplate IS FALSE
       AND datname != 'template1';
  # The list of schemas which get managed by ldap2pg per database
  schemas_query: |
    select nspname from pg_catalog.pg_namespace where nspname not in ( 'public', 'information_schema' ) and nspname not like 'pg_%';
  # The roles which get managed by ldap2pg
  managed_roles_query: |
    VALUES
      ('ldap_roles')
    UNION
    SELECT DISTINCT role.rolname
    FROM pg_roles AS role
    JOIN pg_auth_members AS ms ON ms.member = role.oid
    JOIN pg_roles AS parent
      ON parent.rolname in ( 'ldap_roles' ) AND parent.oid = ms.roleid
    ORDER BY 1;

# This defines the setup of privileges for a given name.
# The "name" is later referenced in the "rules" section.
# Pivileges starting with "__" are build in into ldap2pg, the list is here:
#  https://ldap2pg.readthedocs.io/en/latest/builtins/#using-predefined-privilege-profiles
privileges:
  # The owner has full privileges
  owner:
  - writer
  - __create_on_schemas__
  - __truncate_on_tables__

  # The "reader" only gets reading privileges
  reader:
  - __connect__
  - __usage_on_schemas__
  - __select_on_tables__
  - __select_on_sequences__
  - __usage_on_sequences__

  # The "writer" may change/write in the database
  writer:
  - reader
  - __temporary__
  - __insert_on_tables__
  - __update_on_tables__
  - __delete_on_tables__
  - __update_on_sequences__
  - __execute_on_functions__
  - __trigger_on_tables__

# Rules define the list of roles to create
# and assign the permissions to them in the "grant" section
rules:
- description: "Setup static roles and grants."
  # This is the list of roles which get created initially.
  # The list of AD users comes later in the ldap part.
  roles:
  - name: pgdb_dba
    parents:
    - ldap_roles
    options:
      LOGIN: no
      CREATEDB: no
  - name: pgdb_ro
    parent: ldap_roles
    options:
      LOGIN: no
      CREATEDB: no
  - name: pgdb_rw
    parents:
    - ldap_roles
    options:
      LOGIN: no
      CREATEDB: no
  - name: ldap_roles
    options:
      LOGIN: no
      CREATEDB: no
  # This section grants the "permissions" created above to the pre-defined roles
  grant:
  - privileges: owner
    role: pgdb_dba
    owner: pgdb_dba
  - privilege: reader
    role: pgdb_ro
    owner: pgdb_ro
  - privilege: writer
    role: pgdb_rw
    owner: pgdb_rw

# This is the list of users defined in the ldap directory
- description: "Search LDAP to create members of Postgres LDAP from RO/RW/DBA"
  ldapsearch:
    base: cn=groups,l=world,o=example,dc=local,dc=domain
    filter: "(| (cn=pgdb_ro) (cn=pgdb_rw) (cn=pgdb_dba) )"

  role:
    name: '{uniquemember.uid}'
    options: LOGIN
    parent: "{uid}"

[bersace]

   role:
     name: '{uniquemember.uid}'
     options: LOGIN
-    parent: "{uid}"
+    parents: ["{uid}", ldap_roles]

Shoud do the trick.

[pc-dok]

[local] postgres@postgres=# \du+
                                        List of roles
 Role name  |                         Attributes                         |    Description
------------+------------------------------------------------------------+--------------------
 test1      |                                                            | Managed by ldap2pg
 test2      |                                                            | Managed by ldap2pg
 test3      |                                                            | Managed by ldap2pg
 test4      |                                                            | Managed by ldap2pg
 ldap_roles | Cannot login                                               | Managed by ldap2pg
 pgdb_dba   | Cannot login                                               | Managed by ldap2pg
 pgdb_ro    | Cannot login                                               | Managed by ldap2pg
 pgdb_rw    | Cannot login                                               | Managed by ldap2pg
 postgres   | Superuser, Create role, Create DB, Replication, Bypass RLS |

on \drg it looks as it should


[local] postgres@postgres=# \drg
               List of role grants
 Role name | Member of  |   Options    | Grantor
-----------+------------+--------------+----------
 test1     | ldap_roles | INHERIT, SET | postgres
 test3     | ldap_roles | INHERIT, SET | postgres
 test4     | ldap_roles | INHERIT, SET | postgres
 pgdb_dba  | ldap_roles | INHERIT, SET | postgres
 pgdb_ro   | ldap_roles | INHERIT, SET | postgres
 pgdb_rw   | ldap_roles | INHERIT, SET | postgres

[pc-dok]

so why test2 is on \du visible? on \drg is the user away

[bersace]

test2 is not member of ldap_roles.

If you run GRANT ldap_roles TO test2 and run ldap2pg, ldap2pg may drop test2.

[pc-dok]

yes that was the trick

[pc-dok]

thanks for helping me, and the hints and your time

[pc-dok]

with the ldapsearch command from ldap2pg i see in every ldap group one member