RFE: postgres_permissions should support ALTER DEFAULT PRIVILEGES

[jdelic]

As mentioned in #6558, which hasn't been updated in a long time, the PostgreSQL states (specifically salt.states.postgres_privileges) should also allow ALTER DEFAULT PRIVILEGES queries. This is necessary since PostgreSQL's GRANT implementation only changes privileges on already existing objects in a database/schema. ALTER DEFAULT PRIVILEGES makes it so that a user will gain the defined privileges on all future objects as well.

Using Salt to manage database owners and read-only database roles is pretty much impossible currently as you can only grant permissions on already existing objects, thereby creating a chicken/egg problem where Salt has to create a database owner which then creates the tables and then Salt has to run again for the read-only user to gain access to the newly created tables (this is assuming that Salt does not also create the tables).

A simple boolean flag alter_default_privileges which then issues both the GRANT and ALTER DEFAULT PRIVILEGES query, would probably solve this problem just fine.

Docs:
https://www.postgresql.org/docs/9.5/static/sql-alterdefaultprivileges.html

[lsh-0]

hi guys - has there been any movement since Sept 2016?

[mattiasr]

I would like to see this, since i needed it today

[koalaurent]

Hello, no news about this option since 2016 ? Thanks.

[emerichunter]

Forked the repo today. I need the feature too so I'm looking into it. Any advice on implementation would be welcome.

[emerichunter]

I started on it today. It is not so easy to add a flag to select whether you want default privileges or plain privileges as they don't cover the same objects nor use the same syntax.

My thought is implementing a new set of functions/module/states to complete/replace the standard implementation.

In the long run, the way it has been implemented is - at least to me - not usable. So why not put new stuffs and discuss about the old ones later.

[emerichunter]

Quick update. Managed to implement DEFAULT PRIVILEGES for TABLES, SEQUENCES and FUNCTIONS. In doing so, I also stumbled on an error from a SQL query checking the owner from the object. Will patch this one first if it is not already done. GROUP will be done also most likely.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

If this issue is closed prematurely, please leave a comment and we will gladly reopen the issue.

[lsh-0]

ah, way to go @dwoz! perhaps in v3008 I can put all my crazy postgres permission wrangling scripts in to salt.