Thread: 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@svana.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@svana.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