On Fri, Jan 1, 2021 at 7:35 PM Isaac Morland <isaac.morl...@gmail.com> wrote:
> The use case is to ensure that after doing my GRANTs the permissions are in a > known state, no matter what they were before. Typically, one would follow a > reset command with some GRANTs. So maybe my permissions script contains: > > GRANT UPDATE ON TABLE t1, t2 TO u1, u2; > > Later, I revise this to: > > GRANT UPDATE ON TABLE t1, t2 TO u1; > > But the obsolete permissions will still be available to u2. I would like to > be able to put something like this at the top of the permissions script: > > RESET PERMISSIONS ON ALL TABLES IN SCHEMA test; > > Or in a different context: > > RESET PERMISSIONS ON TABLE t1, t2; > > Note: I'm not particularly fond of "RESET PERMISSIONS" as the syntax; I just > wrote that as an example of what it might look like. > > If the tables are newly created this would have no effect; if they were > existing tables it would change the permissions to what newly created tables > would have. > > In the absence of default privileges, I think it's clear that this means > setting the acl column (relacl, proacl, ...) to NULL; with default > privileges, I think it probably means resetting acl to NULL and then applying > the current default privileges as if the object had just been created by its > owner. As you point out, it's possible the object never had this privilege > set, which is an argument against using the word "reset" in describing the > feature. Maybe "GRANT DEFAULT"? But it's weird for GRANT to actually revoke > privileges, as it would for most object types. Exactly what's wrong with "REVOKE ALL ON ALL TABLES IN SCHEMA test" at the top of your script? You say there is a problem, but don't describe the precise problem. Can you give a fully worked example so we can understand how to resolve? The meaning of GRANT and REVOKE is now defined by SQL Standard, so not something we can easily change. -- Simon Riggs http://www.EnterpriseDB.com/
