postgresql

postgresql_functions.sql

-- 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;