Use ldap2pg to manage roles without 'owning' the database

[burleight]

This is half bug report / half feature request.

I'd like to configure ldap2pg to connect with a role that doesn't own the database, instead opting to grant it admin over the managed roles (this is a PG >= 16 feature).

For example

CREATE ROLE role_admin WITH LOGIN CREATEROLE;
GRANT CONNECT ON DATABASE mydatabase TO role_admin WITH GRANT OPTION;
GRANT reader, editor TO role_admin WITH ADMIN TRUE, SET FALSE, INHERIT FALSE;

I'd expect in this case, ldap2pg would be able to manage the reader and editor roles, but it fails with default database not listed:

https://github.com/dalibo/ldap2pg/blob/43c2f11df63e92aa342276cf1281172ae0b946ba/internal/inspect/stage1.go#L76

https://github.com/dalibo/ldap2pg/blob/master/internal/inspect/sql/databases.sql

ldap2pg did succeed after I granted role_admin the datdba role, but I'd prefer to operate at the lowest level of privilege possible.

Is this something the project would consider supporting?

[bersace]

Hello @burleight

Did you tried to overwrite databases.sql in yaml ?

postgres:
  databases_query: |
    SELECT ...

[burleight]

Hi @bersace ,

I've just tried that. No luck:

msg="Inspecting managed databases." config=databases_query
msg="Executing SQL query:\nSELECT datname FROM pg_catalog.pg_database WHERE datname = 'mydatabase' ORDER BY 1;"
msg="Inspecting database owners."
msg="Executing SQL query:\nSELECT datname, rolname\nFROM pg_catalog.pg_database\nJOIN pg_catalog.pg_roles\n  ON pg_catalog.pg_roles.oid = datdba\n  -- Ensure ldap2pg can reassign to owner.\nWHERE pg_has_role(CURRENT_USER, datdba, 'USAGE')\nORDER BY 1;\n"
msg="Closing Postgres global connection." database=mydatabase
msg="Fatal error." err="databases: default database not listed"

The first query is in the yaml as postgres.databases_query, but the second query appears to be hard-coded. It would help if I could override this in the yaml

[bersace]

Hi @burleight. Not sure about that.

When dropping role, ldap2pg reassigns objects. This condition prevent privilege issue upfront. What do you expect ?

[bersace]

msg="Fatal error." err="databases: default database not listed"

means you either need to set PGDATABASE to one of listed by the query, or fix database_query to include connection database.

Closing stale issue.