Hi all,
I noticed that we have no option to set default privileges for newly created schemas, other than calling GRANT explicitly. At work I use ALTER DEFAULT PRIVILEGE (ADP) command extensively, as the developers are permitted to manage DDL on the databases, and all work fine except for when a new schema is created. So,I'd like to propose this very simple patch (attached) that adds the capability of using SCHEMAS, adding the following syntax to ADP:
ALTER DEFAULT PRIVILEGES
[ FOR { ROLE | USER } target_role [, ...] ]
abbreviated_grant_or_revoke
where abbreviated_grant_or_revoke is one of:
GRANT { USAGE | CREATE | ALL [ PRIVILEGES ] }
ON SCHEMAS
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
REVOKE [ GRANT OPTION FOR ]
{ USAGE | CREATE | ALL [ PRIVILEGES ] }
ON SCHEMAS
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
The patch itself is really straight forward (I'm new to sending patches, so I've chosen a simple one), and there is only one thing that concerns me (as in, if I did it right/good). The difference in syntax for SCHEMAS and the other objects is that IN SCHEMA option makes no sense here (as we don't have nested schemas), and to solve that I simple added the error "cannot use IN SCHEMA clause when using GRANT/REVOKE ON SCHEMAS".
Does that look good to you?
Also, should I add translations for that error message in other languages (I can do that without help of tools for pt_BR) or is that a latter process in the releasing?
Other than that, I added a few regression tests (similar to others used for ADP), and patched the documentation (my English is not that good, so I'm open to suggestions). Anything else I forgot?
While at this, I'd like to ask if you are interested in have all the other types we have in GRANT/REVOKE for ADP (I myself see few use for that at work, but the symmetry on those commands seems like a good idea). If you agree, I can take some time to do the others (looks very simple to do). I just wonder if that should be done as one patch for each, or just a single patch for all of them (perhaps send the sequence of patches in order, as certainly one will conflict with the other if done apart).