Setting ACL
I have a few questions about setting acl on SQL level.
Is it safe to do something like
UPDATE pg_class SET relacl = $1 WHERE oid = $2;
?
I don't think it is because ExecGrant_* call updateAclDependencies after
they do the update and my own update would not do that. But is it safe
to do my update if I'm not touching anything in pg_global?
If it is not safe, is there any point in keeping around makeaclitem()?
I see no use for it except for manually setting an acl column like
above, and it gives people a false sense of security (or at least it did
for me).
And finally, would there be any interest in a function like
aclset("char", oid, aclitem[]) and does this properly?
My use case is I have a simple view and a simple function that both
provide a wrapper over a table, and I want to have an event trigger that
updates their acls when the user does a GRANT/REVOKE on the base table.
--
Vik Fearing
Vik Fearing <vik@postgresfriends.org> writes:
I have a few questions about setting acl on SQL level.
Is it safe to do something like
UPDATE pg_class SET relacl = $1 WHERE oid = $2;
?
I don't think it is because ExecGrant_* call updateAclDependencies after
they do the update and my own update would not do that. But is it safe
to do my update if I'm not touching anything in pg_global?
Well, it'll work, but the system won't know about the role references
in this ACL item, so for instance dropping the role wouldn't make the
ACL go away. Which might cause you dump/reload issues later.
And finally, would there be any interest in a function like
aclset("char", oid, aclitem[]) and does this properly?
Not really, when GRANT is already there ...
regards, tom lane
On 03/03/2020 19:02, Tom Lane wrote:
Vik Fearing <vik@postgresfriends.org> writes:
I have a few questions about setting acl on SQL level.
Is it safe to do something like
UPDATE pg_class SET relacl = $1 WHERE oid = $2;
?I don't think it is because ExecGrant_* call updateAclDependencies after
they do the update and my own update would not do that. But is it safe
to do my update if I'm not touching anything in pg_global?Well, it'll work, but the system won't know about the role references
in this ACL item, so for instance dropping the role wouldn't make the> ACL go away. Which might cause you dump/reload issues later.
Ok, so not safe. Should we remove makeaclitem() then?
And finally, would there be any interest in a function like
aclset("char", oid, aclitem[]) and does this properly?Not really, when GRANT is already there ...
So I have to manually do a diff of the two acls and generate
GRANT/REVOKE statements? That's not encouraging. :(
--
Vik Fearing
Vik Fearing <vik@postgresfriends.org> writes:
Ok, so not safe. Should we remove makeaclitem() then?
Well, I wouldn't recommend poking values into an ACL with it,
but it seems like it has potential use in queries too, say
select * from pg_class
where makeaclitem('joe'::regrole, 'bob'::regrole, 'select', false) = any(relacl);
However, that certainly leaves a lot to be desired because
in practical cases you wouldn't only be interested in
exact matches. I suppose the has_foo_privilege series of
functions would cover some of that territory though.
So I have to manually do a diff of the two acls and generate
GRANT/REVOKE statements? That's not encouraging. :(
The case of just blindly copying one object's ACL to another
object seems kind of limited. I could see providing some more
general facility for that sort of operation, but I'm not quite
sure what it should look like.
regards, tom lane
Greetings,
* Vik Fearing (vik@postgresfriends.org) wrote:
So I have to manually do a diff of the two acls and generate
GRANT/REVOKE statements? That's not encouraging. :(
Not sure if it's helpful to you, but pg_dump has code that generates SQL
to do more-or-less exactly this.
Thanks,
Stephen