Determining what a user can access

[Martijn van Oosterhout]

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 <kl*****@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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE/1pzDY5Twig3Ge+YRArOwAJwPjpjAjQb7aCk2o7AksHYx1YEhgw CcCaPv
/C9IBxDnDIdPdkK6/AHoC70=
=tXl4
-----END PGP SIGNATURE-----

[Peter Eisentraut]

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/curre...ions-misc.html
Table 9-37
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Martijn van Oosterhout]

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/curre...ions-misc.html
Table 9-37


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

--
Martijn van Oosterhout <kl*****@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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE/1sB5Y5Twig3Ge+YRApaSAJ9nldu1FbnlOxs3cvksbtdq7Y68Ug CfZriY
6wN8cxViDn3cmZHePGra8xg=
=48gg
-----END PGP SIGNATURE-----

[Joe Conway]

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html