Re: Safety/validity of resetting permissions by updating system tables

On Sun, 3 Jan 2021 at 05:57, Simon Riggs <simon(dot)riggs(at)enterprisedb(dot)com>
wrote:

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

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:

WITH t AS (
SELECT DISTINCT (aclexplode (%3$I)).grantee
FROM %2$s
WHERE oid = object
)
SELECT string_agg (coalesce (nullif (grantee,
0)::regrole::text, 'PUBLIC'), ', ' ORDER BY rolname)
FROM t LEFT JOIN pg_authid ON (oid = grantee)
INTO STRICT roles;

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.

The meaning of GRANT and REVOKE is now defined by SQL Standard, so not
> something we can easily change.
>

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.