Skip to content

Instantly share code, notes, and snippets.

@zxdvd
Last active August 20, 2018 11:02
Show Gist options
  • Save zxdvd/26f482906e2ad96c58bc2e228c65f42a to your computer and use it in GitHub Desktop.
Save zxdvd/26f482906e2ad96c58bc2e228c65f42a to your computer and use it in GitHub Desktop.
postgresql
-- function to get user privileges of all tables in schemas
create or replace function get_user_table_privs(schemas text[], username text) returns table (username text, schema text, relname regclass, privs text[]) AS $$
select $2, n1.nspname::text, c1.oid::regclass,
array_remove(ARRAY[
(case when has_table_privilege($2, c1.oid, 'SELECT') then 'SELECT' else NULL END),
(case when has_table_privilege($2, c1.oid, 'INSERT') then 'INSERT' else NULL END),
(case when has_table_privilege($2, c1.oid, 'UPDATE') then 'UPDATE' else NULL END),
(case when has_table_privilege($2, c1.oid, 'DELETE') then 'DELETE' else NULL END),
(case when has_table_privilege($2, c1.oid, 'TRUNCATE') then 'TRUNCATE' else NULL END),
(case when has_table_privilege($2, c1.oid, 'REFERENCES') then 'REFERENCES' else NULL END),
(case when has_table_privilege($2, c1.oid, 'TRIGGER') then 'TRIGGER' else NULL END)
], NULL)
from pg_class c1
left join pg_namespace n1 on c1.relnamespace = n1.oid
and c1.relkind = 'r'
where n1.nspname = any ($1)
and has_schema_privilege($2, n1.nspname, 'USAGE')
and has_table_privilege($2, c1.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
$$ language sql;
-- get all children (include children of children)
create or replace function role_get_children(role text) returns SETOF pg_roles AS $$
with recursive roles as (
select oid from pg_roles
where rolname::text = role
union all
select m.member from roles
inner join pg_auth_members m on m.roleid = roles.oid
)
select r1.* from pg_roles r1 inner join roles r2 on r1.oid = r2.oid;
$$ language sql;
-- get all parent roles (include parent's parents)
create or replace function role_get_parents(role text) returns SETOF pg_roles AS $$
with recursive roles as (
select oid from pg_roles
where rolname::text = role
union all
select m.roleid from roles
inner join pg_auth_members m on m.member = roles.oid
)
select r1.* from pg_roles r1 inner join roles r2 on r1.oid = r2.oid;
$$ language sql;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment