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?
