Re: Determining what a user can access - Mailing list pgsql-general

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