Thread: 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/reloadissues 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