Re: Determining what a user can access

I was just trying to find a query what would determine, given a username,
what tables they can see and what permissions they have on those tables.
Obviously this would only work for superusers, but does anyone have any
ideas?

A while ago someone tried using the like operator, would that be the best
approach?
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> (... have gone from d-i being barely usable even by its developers
> anywhere, to being about 20% done. Sweet. And the last 80% usually takes
> 20% of the time, too, right?) -- Anthony Towns, debian-devel-announce

Martijn van Oosterhout wrote:
> I was just trying to find a query what would determine, given a
> username, what tables they can see and what permissions they have on
> those tables. Obviously this would only work for superusers, but does
> anyone have any ideas?

http://www.postgresql.org/docs/current/static/functions-misc.html
Table 9-37

Thanks, I've never seen that page before. I guess it must be new.

Thanks.

On Wed, Dec 10, 2003 at 07:37:16AM +0100, Peter Eisentraut wrote:
> Martijn van Oosterhout wrote:
> > I was just trying to find a query what would determine, given a
> > username, what tables they can see and what permissions they have on
> > those tables. Obviously this would only work for superusers, but does
> > anyone have any ideas?
>
> http://www.postgresql.org/docs/current/static/functions-misc.html
> Table 9-37
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> (... have gone from d-i being barely usable even by its developers
> anywhere, to being about 20% done. Sweet. And the last 80% usually takes
> 20% of the time, too, right?) -- Anthony Towns, debian-devel-announce

Martijn van Oosterhout wrote:
> Thanks, I've never seen that page before. I guess it must be new.
>

There may be more elegant ways to do this, and I'm sure this is
incomplete/could be improved upon, but just for grins:
-------------------------------------------------------------------
create type user_privs_type as (objtype text, objname name, privs text);
create or replace function user_privs(text) returns setof
user_privs_type as '
declare
v_user alias for $1;
priv text;
sep text;
res user_privs_type%rowtype;
rec record;
begin
for rec in
select (select nspname from pg_namespace where oid = relnamespace) as
objnsp, relname as objname
from pg_class where relkind = ''r'' loop
priv := '''';
sep := '''';
if has_table_privilege(v_user, quote_ident(rec.objnsp) || ''.'' ||
quote_ident(rec.objname), ''SELECT'') then
priv := priv || sep || ''SELECT'';
sep := '','';
end if;
if has_table_privilege(v_user, quote_ident(rec.objnsp) || ''.'' ||
quote_ident(rec.objname), ''INSERT'') then
priv := priv || sep || ''INSERT'';
sep := '','';
end if;
if has_table_privilege(v_user, quote_ident(rec.objnsp) || ''.'' ||
quote_ident(rec.objname), ''UPDATE'') then
priv := priv || sep || ''UPDATE'';
sep := '','';
end if;
if has_table_privilege(v_user, quote_ident(rec.objnsp) || ''.'' ||
quote_ident(rec.objname), ''DELETE'') then
priv := priv || sep || ''DELETE'';
sep := '','';
end if;
if has_table_privilege(v_user, quote_ident(rec.objnsp) || ''.'' ||
quote_ident(rec.objname), ''RULE'') then
priv := priv || sep || ''RULE'';
sep := '','';
end if;
if has_table_privilege(v_user, quote_ident(rec.objnsp) || ''.'' ||
quote_ident(rec.objname), ''REFERENCES'') then
priv := priv || sep || ''REFERENCES'';
sep := '','';
end if;
if has_table_privilege(v_user, quote_ident(rec.objnsp) || ''.'' ||
quote_ident(rec.objname), ''TRIGGER'') then
priv := priv || sep || ''TRIGGER'';
sep := '','';
end if;
if priv != '''' then
res.objtype := ''relation'';
res.objname := quote_ident(rec.objnsp) || ''.'' ||
quote_ident(rec.objname);
res.privs := priv;
RETURN NEXT res;
end if;
end loop;

for rec in
select (select nspname from pg_namespace where oid = pronamespace) as
objnsp, proname as objname,
proargtypes from pg_proc loop
priv := '''';
sep := '''';
if has_function_privilege(v_user, quote_ident(rec.objnsp) || ''.''
|| quote_ident(rec.objname) || ''('' || oidvectortypes(rec.proargtypes)
|| '')'', ''EXECUTE'') then
priv := priv || sep || ''EXECUTE'';
sep := '','';
end if;
if priv != '''' then
res.objtype := ''function'';
res.objname := quote_ident(rec.objname);
res.privs := priv;
RETURN NEXT res;
end if;
end loop;

for rec in
select datname as objname from pg_database loop
priv := '''';
sep := '''';
if has_database_privilege(v_user, quote_ident(rec.objname),
''CREATE'') then
priv := priv || sep || ''CREATE'';
sep := '','';
end if;
if has_database_privilege(v_user, quote_ident(rec.objname),
''TEMPORARY'') then
priv := priv || sep || ''TEMPORARY'';
sep := '','';
end if;
if priv != '''' then
res.objtype := ''database'';
res.objname := quote_ident(rec.objname);
res.privs := priv;
RETURN NEXT res;
end if;
end loop;

for rec in
select lanname as objname from pg_language loop
priv := '''';
sep := '''';
if has_language_privilege(v_user, quote_ident(rec.objname),
''USAGE'') then
priv := priv || sep || ''USAGE'';
sep := '','';
end if;
if priv != '''' then
res.objtype := ''language'';
res.objname := quote_ident(rec.objname);
res.privs := priv;
RETURN NEXT res;
end if;
end loop;

for rec in
select nspname as objname from pg_namespace loop
priv := '''';
sep := '''';
if has_schema_privilege(v_user, quote_ident(rec.objname),
''CREATE'') then
priv := priv || sep || ''CREATE'';
sep := '','';
end if;
if has_schema_privilege(v_user, quote_ident(rec.objname),
''USAGE'') then
priv := priv || sep || ''USAGE'';
sep := '','';
end if;
if priv != '''' then
res.objtype := ''schema'';
res.objname := quote_ident(rec.objname);
res.privs := priv;
RETURN NEXT res;
end if;
end loop;

return;
end;
' language plpgsql;

HTH,

Joe