deeejay

psql - Table and view permissions

Oct 15th, 2014
432
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. select usename, nspname || '.' || relname as relation,
  2.        case relkind when 'r' then 'TABLE' when 'v' then 'VIEW' end as relation_type,
  3.        priv
  4. from pg_class join pg_namespace on pg_namespace.oid = pg_class.relnamespace,
  5.      pg_user,
  6.      (values('SELECT', 1),('INSERT', 2),('UPDATE', 3),('DELETE', 4)) privs(priv, privorder)
  7. where relkind in ('r', 'v')
  8.       and has_table_privilege(pg_user.usesysid, pg_class.oid, priv)
  9.       and not (nspname ~ '^pg_' or nspname = 'information_schema')
  10. order by 2, 1, 3, privorder;
Advertisement
Add Comment
Please, Sign In to add comment