search

dalibo / ldap2pg

:elephant: :busts_in_silhouette: Manage PostgreSQL roles and privileges from YAML or LDAP
https://labs.dalibo.com/ldap2pg
PostgreSQL License
200 stars 33 forks source link

fallback_owner #659

Open seanzechman opened 1 month ago

seanzechman commented 1 month ago

I think this is related to issue 421. We were hoping that fallback_owner could be used to have all objects owned by role being dropped be reassigned to fallback_owner instead of postgres.

However, it seems that even though we have fallback_owner defined in our yml syntax under the postgres directive - all objects and databases owned by role to be dropped are reassigned to postgres before dropping.

What is the point of fallback_owner?

Is there a way to specify who should own objects of role being dropped?

bersace commented 1 month ago

Hi @seanzechman , can you share a verbose log showing the case ?

bersace commented 1 month ago

fallback_owner is used to reassign database.

seanzechman commented 1 month ago

Ok - so fallback_owner only works if the database is owned by the role being dropped - I just confirmed that it also reassigns objects properly in that database to fallback_owner.

In our case the database is owned by postgres - but objects inside it are being owned by role being dropped. We wanted to reassign those to an account like "orphan_owner" so they could easily be reviewed later and most likely dropped. It would be easier to identify those objects that were reassigned if they are owned by a different user then postgres.

bersace commented 1 month ago

In our case the database is owned by postgres - but objects inside it are being owned by role being dropped. We wanted to reassign those to an account like "orphan_owner" so they could easily be reviewed later and most likely dropped. It would be easier to identify those objects that were reassigned if they are owned by a different user then postgres.

There may be a hack: postgres:schemas_query allows you to return schema name and owner. This owner is the role who'll inherite orphaned objects. You may use something like:

postgres:
  schemas_query: |
    SELECt nspname, 'orphan_owner' FROM pg_namespace;

Please share what you find.

seanzechman commented 1 month ago

This didn't seem to make a difference. Does it work with v5.9?