Thread: Safety/validity of resetting permissions by updating system tables
I have long maintained permissions in my databases by having a script which assigns all the permissions. I have tended to start with invocations something like this:
REVOKE ALL ON ALL TABLES IN SCHEMA ... FROM ...;
... where the appropriate schemas and roles are listed. This is intended to ensure that the permissions actually in effect exactly match those in the permissions script file. However, the commands I'm using don't guarantee a true reset to default permissions. What I really want is to guarantee that after running the permissions script I will have exactly the same permissions whether I am starting from a freshly initialized database (from script files containing table definitions etc.) or from an existing database (potentially with obsolete grants or other history).
Is it safe and valid to reset to default permissions by doing UPDATE pg_namespace/pg_class/pg_type/pg_proc SET nspacl/relacl/typacl/proacl = NULL WHERE ... to accomplish this? Do I need to take locks or inform some component that I have updated permissions?
And what do people think, conceptually, of the notion of adding a command to do this without resorting to updating system tables directly?
Note: I don't use ALTER DEFAULT PRIVILEGES; my pg_default_acl is empty. So for my immediate question default privileges can be ignored; but in the context of adding a command for privilege resetting we would have to think about how to handle default privileges.
Isaac Morland <isaac.morland@gmail.com> writes:
> Is it safe and valid to reset to default permissions by doing
> UPDATE pg_namespace/pg_class/pg_type/pg_proc
> SET nspacl/relacl/typacl/proacl = NULL WHERE ... to accomplish this?
Not terribly; the main objection is you'd fail to update pg_shdepend.
> And what do people think, conceptually, of the notion of adding a command
> to do this without resorting to updating system tables directly?
I'm a little skeptical as to the use-case, particularly once you take
ALTER DEFAULT PRIVILEGES into account and try to figure out what that
means. If it means "apply the current default privileges", you could
easily be "resetting" to a state that never actually prevailed in the
past.
regards, tom lane
On Fri, 1 Jan 2021 at 11:44, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Isaac Morland <isaac.morland@gmail.com> writes:
> Is it safe and valid to reset to default permissions by doing
> UPDATE pg_namespace/pg_class/pg_type/pg_proc
> SET nspacl/relacl/typacl/proacl = NULL WHERE ... to accomplish this?
Not terribly; the main objection is you'd fail to update pg_shdepend.
Right, the object would still be recorded as depending on the role, even though it really didn't any more. I should have considered that.
I think I can fix that by first looping through using aclexplode() and issuing a REVOKE against every role mentioned, then do a table update to replace the empty array acl with a NULL. Of course I could also update pg_shdepend myself but the goal is to minimize direct system table updates.
Thanks for the response.
> And what do people think, conceptually, of the notion of adding a command
> to do this without resorting to updating system tables directly?
I'm a little skeptical as to the use-case, particularly once you take
ALTER DEFAULT PRIVILEGES into account and try to figure out what that
means. If it means "apply the current default privileges", you could
easily be "resetting" to a state that never actually prevailed in the
past.
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.
On Fri, Jan 1, 2021 at 7:35 PM Isaac Morland <isaac.morland@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 topof 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 mightlook like. > > If the tables are newly created this would have no effect; if they were existing tables it would change the permissionsto 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, ...) toNULL; with default privileges, I think it probably means resetting acl to NULL and then applying the current default privilegesas if the object had just been created by its owner. As you point out, it's possible the object never had thisprivilege 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/
On Sun, 3 Jan 2021 at 05:57, Simon Riggs <simon.riggs@enterprisedb.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.
On 1/1/21 11:44 AM, Tom Lane wrote: > Isaac Morland <isaac.morland@gmail.com> writes: >> Is it safe and valid to reset to default permissions by doing >> UPDATE pg_namespace/pg_class/pg_type/pg_proc >> SET nspacl/relacl/typacl/proacl = NULL WHERE ... to accomplish this? > Not terribly; the main objection is you'd fail to update pg_shdepend. And apart from that I'm generally resistant to anything that requires direct manipulation of the catalog. One of many reasons is that there is no guarantee that it will have the same shape in the next release. I normally encourage people strongly to look for other solutions. cheers andrew -- Andrew Dunstan EDB: https://www.enterprisedb.com
On Mon, 4 Jan 2021 at 10:12, Andrew Dunstan <andrew@dunslane.net> wrote:
On 1/1/21 11:44 AM, Tom Lane wrote:
> Isaac Morland <isaac.morland@gmail.com> writes:
>> Is it safe and valid to reset to default permissions by doing
>> UPDATE pg_namespace/pg_class/pg_type/pg_proc
>> SET nspacl/relacl/typacl/proacl = NULL WHERE ... to accomplish this?
> Not terribly; the main objection is you'd fail to update pg_shdepend.
And apart from that I'm generally resistant to anything that requires
direct manipulation of the catalog. One of many reasons is that there is
no guarantee that it will have the same shape in the next release. I
normally encourage people strongly to look for other solutions.
So am I. That's why I asked before proceeding.
As far as I can tell, it is not possible to fully reset permissions using GRANT/REVOKE even querying the system tables to figure out which permissions exist; the closest one can get is to set explicit (non-NULL) acls that have the same effect as the default (NULL) acls; and doing so requires duplicating the logic used within the system to determine the permissions that apply to an object with a blank (NULL) acl.
On 1/4/21 11:15 AM, Isaac Morland wrote: > On Mon, 4 Jan 2021 at 10:12, Andrew Dunstan <andrew@dunslane.net > <mailto:andrew@dunslane.net>> wrote: > > > On 1/1/21 11:44 AM, Tom Lane wrote: > > Isaac Morland <isaac.morland@gmail.com > <mailto:isaac.morland@gmail.com>> writes: > >> Is it safe and valid to reset to default permissions by doing > >> UPDATE pg_namespace/pg_class/pg_type/pg_proc > >> SET nspacl/relacl/typacl/proacl = NULL WHERE ... to accomplish > this? > > Not terribly; the main objection is you'd fail to update > pg_shdepend. > > And apart from that I'm generally resistant to anything that requires > direct manipulation of the catalog. One of many reasons is that > there is > no guarantee that it will have the same shape in the next release. I > normally encourage people strongly to look for other solutions. > > > So am I. That's why I asked before proceeding. > > As far as I can tell, it is not possible to fully reset permissions > using GRANT/REVOKE even querying the system tables to figure out which > permissions exist; the closest one can get is to set explicit > (non-NULL) acls that have the same effect as the default (NULL) acls; > and doing so requires duplicating the logic used within the system to > determine the permissions that apply to an object with a blank (NULL) acl. I think there is probably a good case for some sort of "from scratch" option on GRANT. cheers andrew -- Andrew Dunstan EDB: https://www.enterprisedb.com
Andrew Dunstan <andrew@dunslane.net> writes:
> I think there is probably a good case for some sort of "from scratch"
> option on GRANT.
Maybe my head's not screwed on straight this morning, but it seems
to me that any such action would typically be revoking permissions
not adding them, so that it'd be more naturally framed as a REVOKE
option.
There's still the question of exactly what "from scratch" means.
Do we really want it to just reset the acl column to null, forcing
the object to the wired-in defaults? Might be better to reset to
whatever pg_init_privs has, if anything. Also, what about the
effects of any applicable ALTER DEFAULT PRIVILEGES settings?
Maybe we could go with two commands (spelling subject to bikeshedding):
REVOKE ALL NONSTANDARD PRIVILEGES ON object
resets to pg_init_privs state, or null if no entry there
GRANT DEFAULT PRIVILEGES ON object
add any privileges implied by applicable ALTER DEFAULT PRIVILEGES
settings
A different way to look at it, which I think is what the OP had
in mind, is that the existing behaviors are sufficient if you can
say "REVOKE ... FROM ALL". Or, maybe we need that too.
regards, tom lane
Greetings, * Tom Lane (tgl@sss.pgh.pa.us) wrote: > Andrew Dunstan <andrew@dunslane.net> writes: > > I think there is probably a good case for some sort of "from scratch" > > option on GRANT. > > Maybe my head's not screwed on straight this morning, but it seems > to me that any such action would typically be revoking permissions > not adding them, so that it'd be more naturally framed as a REVOKE > option. Except that actually resetting permissions on some object to NULL ends up actually grant'ing out rights in some cases- specifically particularly dangerous cases like functions... > There's still the question of exactly what "from scratch" means. > Do we really want it to just reset the acl column to null, forcing > the object to the wired-in defaults? Might be better to reset to > whatever pg_init_privs has, if anything. Also, what about the > effects of any applicable ALTER DEFAULT PRIVILEGES settings? > > Maybe we could go with two commands (spelling subject to bikeshedding): > > REVOKE ALL NONSTANDARD PRIVILEGES ON object If we're going to invent something that actually REVOKE's all rights for an object, it should actually do so- including in cases where the object's default set includes privileges. In other words, I'd say we would want to have: REVOKE ALL RIGHTS ON object and that would then set NULL for objects which don't have any GRANT'd out rights by default but then for functions and other objects which *do* have a default set of GRANT'd out rights, it'd actually set up an ACL which explicitly removes those rights. If I'm understanding correctly, the point of this feature is to allow for something like: REVOKE ALL RIGHTS ON object; GRANT whatever ON object TO whomever; and have the result mean that only 'whomever' has any rights on the object (other than the owner). Simply going to NULL doesn't do that- you'd have to *also* explicitly do: REVOKE EXECUTE ON FUNCTION myfunc FROM public; Which doesn't seem ideal. > resets to pg_init_privs state, or null if no entry there An option which resets to pg_init_privs set shouldn't be saying 'REVOKE ALL' or such but should instead be 'RESET PRIVILEGES' or something along those lines. > GRANT DEFAULT PRIVILEGES ON object > > add any privileges implied by applicable ALTER DEFAULT PRIVILEGES > settings This does seem like a useful, albeit mostly independent, feature. > A different way to look at it, which I think is what the OP had > in mind, is that the existing behaviors are sufficient if you can > say "REVOKE ... FROM ALL". Or, maybe we need that too. REVOKE ... FROM ALL also seems like a useful capability. Thanks, Stephen