Issue with Applying Privileges for All Schemas about ldap2pg HOT 8 CLOSED

Example of YML File using the schemas: schema __all__

This shows that the --config option works correctly, and when we run the --real option there is an error with the YML shown above.

from ldap2pg.

More specifically I need to sync with all schemas for a given database EXCEPT for the public schema.

I've tried the global option to handle all schemas for the privileges and also the managed_roles_query to exclude the public schema.

The issue is we do not want to have the newly created roles to have privileges on the public schema such as creating new tables. This I know is default PS functionality, so perhaps this can only be accomplished with a post psql script.

from ldap2pg.

Hi @erin-nielsen, thanks for reaching.

There is no schema __all__. The changelog typography may be misleading. https://ldap2pg.readthedocs.io/en/latest/config/#grant-schema states default schema is meta-value__all__.

If you want to customize per database schema, overwrite postgres:schemas_query at https://ldap2pg.readthedocs.io/en/latest/config/#postgres-schemas-query excluding public.

Is this clear for you ?

Regards,

from ldap2pg.

Thank you so much for the reply @bersace!!

BTW we are using Postgres v15.6 with Version 6 of ldap2pg.

When we try using the __all__ unfortunately it ignores all schemas and does not apply any privileges. It does not error it just ignores them all. When I run the config or real options it doesn't grant any privileges. If I specify each individual schema, then it DOES work, but we really need it to be dynamic and specify all if we can.

Alternatively, I tried using the global option on the privileges as it seems to indicate this in the documentation that it doesn't really support the __all__ for granting privileges and that we need to specify a global default in the privileges section, and I was unsuccessful.

I also tried using the schemas_query and while the query is correct, that too resulted in no privileges being granted. I can only seem to get it to work if I explicitly specify the schemas, but the issue is if new ones are created we do not want to have to update the YML.

from ldap2pg.

BTW - you may see I'm also playing with managed_roles_query. The customer is wanting the new roles being created to not have any privileges granted to them within the public schema, which PS does by default. I'm not sure we can accomplish this using ldap2pg or not, it may just have to be a custom script that's run post sync..

from ldap2pg.

Can we can possibly an update on my questions? Thank you!!!!!

from ldap2pg.

Hi Erin,

BTW - you may see I'm also playing with managed_roles_query. The customer is wanting the new roles being created to not have any privileges granted to them within the public schema, which PS does by default. I'm not sure we can accomplish this using ldap2pg or not, it may just have to be a custom script that's run post sync..

I you include public in managed_roles_query, ldap2pg will revoke privileges from public. I don't remember correctly, but Postgres 15 is more restrictive. Also, ensure ldap2pg is aware of schema public and manages it.

from ldap2pg.

When we try using the __all__ unfortunately it ignores all schemas and does not apply any privileges. It does not error it just ignores them all. When I run the config or real options it doesn't grant any privileges. If I specify each individual schema, then it DOES work, but we really need it to be dynamic and specify all if we can.

Alternatively, I tried using the global option on the privileges as it seems to indicate this in the documentation that it doesn't really support the __all__ for granting privileges and that we need to specify a global default in the privileges section, and I was unsuccessful.

I also tried using the schemas_query and while the query is correct, that too resulted in no privileges being granted. I can only seem to get it to work if I explicitly specify the schemas, but the issue is if new ones are created we do not want to have to update the YML.

Please paste code instead of screenshot.

NOT IN ('postgres') is useless because postgres is blacklisted.

Comparing rolname and nspname is weird. Especially when your nspname is always public.

Note that public role is not in pg_roles. It's a virtual role. You must add it explicitly with an UNION. See https://ldap2pg.readthedocs.io/en/latest/config/#postgres-managed-roles-query

from ldap2pg.