Re: Determining what a user can access

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