Re: Safety/validity of resetting permissions by updating system tables - Mailing list pgsql-hackers

 

There are two separate issues.

First, I can say "ON ALL TABLES" but I can't say "FROM EVERYBODY". I have to list out all roles (including PUBLIC, if applicable) which have been granted permissions. It might be sufficient to allow, instead of FROM role_specification [, ...], FROM EVERYBODY or some such (although the specific word "everybody" doesn't feel appropriate as a keyword for some reason).

Right now I'm calculating the role specification as follows:

The %#$ stuff is because this is happening inside a loop which goes through VALUES

        ('regclass'::regtype, 'pg_class'::regclass, 'relacl', 'TABLE'),

        ('regnamespace',      'pg_namespace',       'nspacl', 'SCHEMA'),

        ('regprocedure',      'pg_proc',            'proacl', 'FUNCTION'),

        ('regtype',           'pg_type',            'typacl', 'TYPE')

and declares 4 versions of my "reset permissions" function.

The second issue is that by default objects actually have some permissions; for most this is just all permissions granted to owner, but functions and a couple of others have some default permissions granted to public. So if I just revoke everything, I end with with acl = {} which is not the same as acl NULL. I handle this by doing an UPDATE on the relevant system table to change the acl from {} to NULL; by doing a REVOKE first I ensure pg_shdepend is updated as Tom pointed out I needed to do. For objects with no default PUBLIC permissions it would (almost) work to simply exclude the owner from the list of roles, but for other objects I would need to duplicate the default permissions logic.

Finally, and this is pretty minor, but to be 100% compliant with what I want, there should be no difference at all between (1) creating the object and (2) creating the object, applying some permissions, and then resetting the permissions. As far as I know there is no way to get back to a NULL acl using GRANT/REVOKE; I can get back to the same effect, but not actually the same value in the database. This is visible in pg_dump output, where a NULL acl results in no GRANT/REVOKE statements in the output, but equivalent non-NULL acl results in the usual output with the same overall effect. Again, this is pretty minor, but part of my workflow involves diffing the result of pg_dumping the schema after running the creation script on an empty database and after running the latest schema upgrade script on an existing database. This used to be problematic but in recent versions pg_dump has become very good at dumping in a defined order.

I agree that is another reason not to like my "GRANT DEFAULT" syntax idea. I'm not a big fan of either of my own syntax suggestions.