Re: access checking using sql in 7.1beta3

Bruno Wolff III <bruno(at)wolff(dot)to> writes:
> Is this how aclcontains is really supposed to work?

Hmm. It turns out that the way aclcontains is (and always has been)
coded, what it is really looking for is to see whether there is any
entry in the ACL list that matches the named user or group. It is *not*
looking at the permissions part of the ACL entry at all. Thus, your
example

> area=> create table test (col1 int);
> area=> grant select on test to public;
> area=> revoke select on test from bruno;
> area=> select aclcontains(relacl,'bruno=r') from pg_class where relname='test';
> aclcontains
> -------------
> t
> (1 row)

returns t because there is an ACL entry mentioning bruno, even though
that ACL entry does not grant him 'r' privilege (in fact it exists
specifically to deny him 'r' privilege). You'd get a true result no
matter what permission you put after '='.

This definition strikes me as utterly useless; can anyone see a use
for this behavior?

I think what was probably intended was to match the permissions field
as well as the type/id fields, which would mean that aclcontains()
would return true only if some element of the ACL array exactly equals
the given ACL.

While somewhat sane, this still isn't really what you want to know,
which is whether ACL x grants permission type y to user z (possibly
indirectly through group membership). This computation exists
internally but isn't exported as an SQL function. Probably we ought
to add such a function. There would need to be some thought about
details of the definition, however. For example, if z is a superuser,
should the function always return true? Or do we want it just to take
the ACL and user at face value, rather than doing a side lookup in
pg_shadow?

regards, tom lane