Issue with Applying Privileges for All Schemas

[erin-nielsen]

Hello!

I am having an issue with the YML specifically the "grant" tag, using version 6.

The documentation states for ldap2pg v4.0 (https://ldap2pg.readthedocs.io/en/latest/changelog/?h=__all__#ldap2pg-40)
Deprecation: schema __all__ should be used instead of __all__

The following YML snippet will not pick up ANY schemas to set the privileges. And does not work for either the --config or --real option.

grant:
  - privilege: ro
    role: readers
    schemas: __all__    
  - privilege: rw
    role: writers
  - privilege: ddl
    role: owners

The following YML snippet will yield the correct results with the --config option, however when I run with the --real option we encounter the following error that seems to indicate that this token is not being replaced and it is using it as a literal value.

ERROR Fatal error. err="stage 2: apply: sync: ERROR: schema \"schema __all__\" does not exist (SQLSTATE 3F000)"

grant:
  - privilege: ro
    role: readers
    schemas: schema __all__    
  - privilege: rw
    role: writers
  - privilege: ddl
    role: owners

A work-around is to specify each schema name which DOES grant privileges correctly however, the requirement is for this to be dynamic if new schemas are being created. Therefore, this work-around is not acceptable.

grant:
  - privilege: ro
    role: readers
    schemas: [apple,orange]
    database: prime    
  - privilege: rw
    role: writers   
  - privilege: ddl
    role: owners

Is there another way to achieve the grant privileges to all schemas?

Thank you!
Erin

[erin-nielsen]

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.

[erin-nielsen]

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.

[bersace]

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,

[erin-nielsen]

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.

[erin-nielsen]

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..

[erin-nielsen]

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

[bersace]

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.

[bersace]

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