CONNECT and TEMPORARY privileges revoked with 6.3

[dani]

I'm using ldap2pg to manage privileges on a postgres 16 cluster. It's a simple "static" (no LDAP) setup. I've mainly created two special roles for postgres monitoring and another highly privileged for administration. Based on the documentation, I've created a few privileges levels (user can only connect, reader has user privileges + SELECT, writer has reader + INSET and UPDATE etc.)

ldap2pg.yml

(Sorry if the order of directives seems wierd, it's passing through a templating engine which parses YAML, and reformat it). For clarity, I removed some entries which are not related to this issue

ldap2pg.yml

postgres:
  managed_roles_query: |
    VALUES
      ('public'),
      ('managed_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 = 'managed_roles' AND parent.oid = ms.roleid
    ORDER BY 1;
privileges:
  owner:
    - writer
    - __create_on_schemas__
    - __truncate_on_tables__
  reader:
    - user
    - __select_on_tables__
    - __select_on_sequences__
    - __usage_on_sequences__
  rewinder:
    - __connect__
    - __execute_on_functions__
  user:
    - __connect__
    - __usage_on_schemas__
  writer:
    - reader
    - __temporary__
    - __insert_on_tables__
    - __update_on_tables__
    - __delete_on_tables__
    - __update_on_sequences__
    - __execute_on_functions__
    - __trigger_on_tables__
version: 6

rules:
  - roles:
      - comment: Parent role for all ldap2pg managed roles
        name: managed_roles
      - comment: Databases admins
        name: dba
        options: SUPERUSER NOLOGIN
        parents:
          - managed_roles
      - comment: Databases monitor
        name: monitor
        options: LOGIN
        parents:
          - managed_roles
          - pg_monitor
      - comment: Hashicorp Vault
        name: vault
        options: SUPERUSER LOGIN
        parents:
          - managed_roles
  - grant:
      databases: postgres
      privileges: reader
      role: vault
  - grant:
      privileges: user
      role: monitor
  - grant:
      databases: postgres
      privileges: rewinder
      role: rewind
  - grant:
      privileges: owner
      role: dba

This simple config works fine with ldap2pg 6.2

Expectations

• What you expected from ldap2pg ?

Updating to ldap2pg 6.3 should not change privileges, nor break common usage

• What ldap2pg did wrong ?

The same config with ldap2pg 6.3 revokes CONNECT privilege for both monitor and dba roles (and also TEMPORARY for dba)

Verbose output of ldap2pg execution

Running in dry mode gives this (truncated, I have a few other DB)

Verbose output

2025-02-17 20:48:22.000 time=2025-02-17T21:48:22.165+01:00 level=CHANGE msg="Would Revoke privileges." grant="CONNECT ON DATABASE bounca TO dba" database=bounca
2025-02-17 20:48:22.000 time=2025-02-17T21:48:22.182+01:00 level=CHANGE msg="Would Revoke privileges." grant="TEMPORARY ON DATABASE bounca TO dba" database=bounca
2025-02-17 20:48:22.000 time=2025-02-17T21:48:22.182+01:00 level=CHANGE msg="Would Revoke privileges." grant="CONNECT ON DATABASE bounca TO monitor" database=bounca
2025-02-17 20:48:22.000 time=2025-02-17T21:48:22.182+01:00 level=CHANGE msg="Would Revoke privileges." grant="CONNECT ON DATABASE cloudbeaver TO dba" database=cloudbeaver
2025-02-17 20:48:22.000 time=2025-02-17T21:48:22.194+01:00 level=CHANGE msg="Would Revoke privileges." grant="TEMPORARY ON DATABASE cloudbeaver TO dba" database=cloudbeaver
2025-02-17 20:48:22.000 time=2025-02-17T21:48:22.194+01:00 level=CHANGE msg="Would Revoke privileges." grant="CONNECT ON DATABASE cloudbeaver TO monitor" database=cloudbeaver
2025-02-17 20:48:22.000 time=2025-02-17T21:48:22.194+01:00 level=CHANGE msg="Would Revoke privileges." grant="CONNECT ON DATABASE etherpad TO dba" database=etherpad
2025-02-17 20:48:22.000 time=2025-02-17T21:48:22.207+01:00 level=CHANGE msg="Would Revoke privileges." grant="TEMPORARY ON DATABASE etherpad TO dba" database=etherpad
2025-02-17 20:48:22.000 time=2025-02-17T21:48:22.207+01:00 level=CHANGE msg="Would Revoke privileges." grant="CONNECT ON DATABASE etherpad TO monitor" database=etherpad
2025-02-17 20:48:22.000 time=2025-02-17T21:48:22.207+01:00 level=CHANGE msg="Would Revoke privileges." grant="CONNECT ON DATABASE grafana TO dba" database=grafana
2025-02-17 20:48:22.000 time=2025-02-17T21:48:22.219+01:00 level=CHANGE msg="Would Revoke privileges." grant="TEMPORARY ON DATABASE grafana TO dba" database=grafana
2025-02-17 20:48:22.000 time=2025-02-17T21:48:22.219+01:00 level=CHANGE msg="Would Revoke privileges." grant="CONNECT ON DATABASE grafana TO monitor" database=grafana
2025-02-17 20:48:22.000 time=2025-02-17T21:48:22.219+01:00 level=CHANGE msg="Would Revoke privileges." grant="CONNECT ON DATABASE hoppscotch TO dba" database=hoppscotch
2025-02-17 20:48:22.000 time=2025-02-17T21:48:22.232+01:00 level=CHANGE msg="Would Revoke privileges." grant="TEMPORARY ON DATABASE hoppscotch TO dba" database=hoppscotch
2025-02-17 20:48:22.000 time=2025-02-17T21:48:22.232+01:00 level=CHANGE msg="Would Revoke privileges." grant="CONNECT ON DATABASE hoppscotch TO monitor" database=hoppscotch
2025-02-17 20:48:22.000 time=2025-02-17T21:48:22.232+01:00 level=CHANGE msg="Would Revoke privileges." grant="CONNECT ON DATABASE immich TO dba" database=immich
2025-02-17 20:48:22.000 time=2025-02-17T21:48:22.244+01:00 level=CHANGE msg="Would Revoke privileges." grant="TEMPORARY ON DATABASE immich TO dba" database=immich
2025-02-17 20:48:22.000 time=2025-02-17T21:48:22.244+01:00 level=CHANGE msg="Would Revoke privileges." grant="CONNECT ON DATABASE immich TO monitor" database=immich
2025-02-17 20:48:22.000 time=2025-02-17T21:48:22.244+01:00 level=CHANGE msg="Would Revoke privileges." grant="CONNECT ON DATABASE lemonldap-ng TO dba" database=lemonldap-ng
2025-02-17 20:48:22.000 time=2025-02-17T21:48:22.261+01:00 level=CHANGE msg="Would Revoke privileges." grant="TEMPORARY ON DATABASE lemonldap-ng TO dba" database=lemonldap-ng
2025-02-17 20:48:22.000 time=2025-02-17T21:48:22.261+01:00 level=CHANGE msg="Would Revoke privileges." grant="CONNECT ON DATABASE lemonldap-ng TO monitor" database=lemonldap-ng

Then there's a few GRANTS I don't quite understand (as my ldap2pg config didn't changed at all), like

2025-02-17 20:48:22.000 time=2025-02-17T21:48:22.451+01:00 level=CHANGE msg="Would Grant privileges." grant="DEFAULT FOR vault IN SCHEMA public EXECUTE ON FUNCTIONS TO rewind" database=postgres
2025-02-17 20:48:22.000 time=2025-02-17T21:48:22.451+01:00 level=CHANGE msg="Would Grant privileges." grant="DEFAULT FOR vault IN SCHEMA public TRUNCATE ON TABLES TO dba" database=postgres
2025-02-17 20:48:22.000 time=2025-02-17T21:48:22.451+01:00 level=CHANGE msg="Would Grant privileges." grant="DEFAULT FOR vault IN SCHEMA public DELETE ON TABLES TO dba" database=postgres
2025-02-17 20:48:22.000 time=2025-02-17T21:48:22.451+01:00 level=CHANGE msg="Would Grant privileges." grant="DEFAULT FOR vault IN SCHEMA public EXECUTE ON FUNCTIONS TO dba" database=postgres
2025-02-17 20:48:22.000 time=2025-02-17T21:48:22.451+01:00 level=CHANGE msg="Would Grant privileges." grant="DEFAULT FOR vault IN SCHEMA public INSERT ON TABLES TO dba" database=postgres
2025-02-17 20:48:22.000 time=2025-02-17T21:48:22.451+01:00 level=CHANGE msg="Would Grant privileges." grant="DEFAULT FOR vault IN SCHEMA public TRIGGER ON TABLES TO dba" database=postgres
2025-02-17 20:48:22.000 time=2025-02-17T21:48:22.451+01:00 level=CHANGE msg="Would Grant privileges." grant="DEFAULT FOR vault IN SCHEMA public UPDATE ON SEQUENCES TO dba" database=postgres
2025-02-17 20:48:22.000 time=2025-02-17T21:48:22.452+01:00 level=CHANGE msg="Would Grant privileges." grant="DEFAULT FOR vault IN SCHEMA public UPDATE ON TABLES TO dba" database=postgres
2025-02-17 20:48:22.000 time=2025-02-17T21:48:22.452+01:00 level=CHANGE msg="Would Grant privileges." grant="DEFAULT FOR vault IN SCHEMA public SELECT ON SEQUENCES TO dba" database=postgres
2025-02-17 20:48:22.000 time=2025-02-17T21:48:22.452+01:00 level=CHANGE msg="Would Grant privileges." grant="DEFAULT FOR vault IN SCHEMA public SELECT ON TABLES TO dba" database=postgres
2025-02-17 20:48:22.000 time=2025-02-17T21:48:22.452+01:00 level=CHANGE msg="Would Grant privileges." grant="DEFAULT FOR vault IN SCHEMA public USAGE ON SEQUENCES TO dba" database=postgres

More complete logs, in debug level (but still dry mode) available here: ldap2pg.log

Running in real mode does revoke CONNECT (and TEMPORARY), which breaks monitoring (prometheus exporter can't connect to postgres anymore, same for dba). Reverting to 6.2 and the expected privileges are correctly set.

[bersace]

Hi @dani , thanks for the feedback.

The REVOKE CONNECT looks like  bug. I'll investigate.

since vault is SUPERUSER, that may explain why its default privileges must be configured.