Thread: access checking using sql in 7.1beta3

I have a perl script for dumping public tables and I wanted to only try to
dump tables that aren't owned by postgres and have select access for the
current user. I currently use the following select statement to get a list
of these tables:

select relname from pg_class, pg_user where (relkind = 'r' or relkind = 'v')
  and relowner = usesysid and usename != 'postgres' and
  coalesce(aclcontains(relacl,aclitemin((current_user || '=r')::name)),
  current_user = usename) order by relname;

This works for the way I have things set up now (using 7.1beta3), but it
doesn't really work for all cases.

For example if I grant public select access and revoke select access from
the object owner, the object owner will not have select access, though
checking for select access by the owner with aclcontains will show as true.

For example:
area=> create table test (col1 int);
CREATE
area=> grant select on test to public;
CHANGE
area=> revoke select on test from bruno;
CHANGE
area=> select * from test;
ERROR:  test: Permission denied.
area=> select aclcontains(relacl,'bruno=r') from pg_class where relname='test';
 aclcontains
-------------
 t
(1 row)

Conversely for another user aclcontains returns false when in fact the
user does have access. Continuing on the previous example:
area=> select aclcontains(relacl,'nobody=r') from pg_class where relname='test'; aclcontains
-------------
 f
(1 row)

Is this how aclcontains is really supposed to work?

Is there a right way to check if the current user has access to a table
without trying something to see if it fails?

Bruno Wolff III <bruno@wolff.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