July 29, 2012
PostgreSQL has some useful functions which can be used to know about the privilege of a user on a particular Database object. Those functions are available in following link:
https://www.postgresql.org/docs/11/functions-info.html
Functions has_*_privilege in PostgreSQL/PPAS is good to know about privilege a user has on one database objects and these function returns boolean value true or false.
Enterprise-ready Postgres tools for high availability, monitoring, and disaster recovery. Download Now.
DBAs/Users are interested in listing objects and privileges of a Database User. Currently PostgreSQL doesn’t have a view that a DBA can use to list users' privileges on objects for a particular database. Therefore, I thought about making some functions, which can be used to list users' privileges, based on what is available in PostgreSQL/PPAS 11. These are basic functions and can be expanded, as needed, to show more privileges like WITH GRANT OPTION. Following are functions which can be used to designate the privileges of a particular user:
1. Function for table privileges:
CREATE OR REPLACE FUNCTION table_privs(text) RETURNS table(username text, relname regclass, privs text[])
AS
$$
SELECT $1,c.oid::regclass, array(select privs from unnest(ARRAY [
( CASE WHEN has_table_privilege($1,c.oid,'SELECT') THEN 'SELECT' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'INSERT') THEN 'INSERT' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'UPDATE') THEN 'UPDATE' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'DELETE') THEN 'DELETE' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'TRUNCATE') THEN 'TRUNCATE' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'REFERENCES') THEN 'REFERENCES' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'TRIGGER') THEN 'TRIGGER' ELSE NULL END)]) foo(privs) where privs is not null) FROM pg_class c JOIN pg_namespace n on c.relnamespace=n.oid where n.nspname not in ('information_schema','pg_catalog','sys') and nspparent=0 and c.relkind='r' and
has_table_privilege($1,c.oid,'SELECT, INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER') AND has_schema_privilege($1,c.relnamespace,'USAGE')
$$ language sql;
Note: Above function can be used in Advanced Server. For table_privs in PostgreSQL, users can try the following function:
CREATE OR REPLACE FUNCTION table_privs(text) RETURNS table(username text, relname regclass, privs text[])
AS
$$
SELECT $1,c.oid::regclass, array(select privs from unnest(ARRAY [
( CASE WHEN has_table_privilege($1,c.oid,'SELECT') THEN 'SELECT' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'INSERT') THEN 'INSERT' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'UPDATE') THEN 'UPDATE' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'DELETE') THEN 'DELETE' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'TRUNCATE') THEN 'TRUNCATE' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'REFERENCES') THEN 'REFERENCES' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'TRIGGER') THEN 'TRIGGER' ELSE NULL END)]) foo(privs) where privs is not null) FROM pg_class c JOIN pg_namespace n on c.relnamespace=n.oid where n.nspname not in ('information_schema','pg_catalog','sys') and c.relkind='r' and
has_table_privilege($1,c.oid,'SELECT, INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER') AND has_schema_privilege($1,c.relnamespace,'USAGE')
$$ language sql;
Example:
edb=# select * from table_privs('test_user');
username | relname | privs
-----------+---------+-----------------------------------------------------------
test_user | test_id | {SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER}
(1 row)
2. Database privileges:
CREATE OR REPLACE FUNCTION database_privs(text) RETURNS table(username text,dbname name,privileges text[])
AS
$$
SELECT $1, datname, array(select privs from unnest(ARRAY[
( CASE WHEN has_database_privilege($1,c.oid,'CONNECT') THEN 'CONNECT' ELSE NULL END),
(CASE WHEN has_database_privilege($1,c.oid,'CREATE') THEN 'CREATE' ELSE NULL END),
(CASE WHEN has_database_privilege($1,c.oid,'TEMPORARY') THEN 'TEMPORARY' ELSE NULL END),
(CASE WHEN has_database_privilege($1,c.oid,'TEMP') THEN 'CONNECT' ELSE NULL END)])foo(privs) WHERE privs IS NOT NULL) FROM pg_database c WHERE
has_database_privilege($1,c.oid,'CONNECT,CREATE,TEMPORARY,TEMP') AND datname not in ('template0');
$$ language sql;
Example:
edb=# select * from database_privs('test_user');
username | dbname | privileges
-----------+-----------+-----------------------------
test_user | postgres | {CONNECT,TEMPORARY,CONNECT}
test_user | edb | {CONNECT,TEMPORARY,CONNECT}
test_user | template1 | {CONNECT}
3. Tablespace privileges:
CREATE OR REPLACE FUNCTION tablespace_privs(text) RETURNS table(username text,spcname name,privileges text[])
AS
$$
SELECT $1, spcname, ARRAY[
(CASE WHEN has_tablespace_privilege($1,spcname,'CREATE') THEN 'CREATE' ELSE NULL END)] FROM pg_tablespace WHERE has_tablespace_privilege($1,spcname,'CREATE');
$$ language sql;
Example:
edb=# select * from tablespace_privs('test_user');
username | spcname | privileges
-----------+---------+------------
test_user | test | {CREATE}
(1 row)
4. Foreign Data Wrapper privileges
CREATE OR REPLACE FUNCTION fdw_wrapper_privs(text) RETURNS table(username text,fdwname name,privleges text[])
AS
$$
SELECT $1, fdwname, ARRAY[
(CASE WHEN has_foreign_data_wrapper_privilege($1,fdwname,'USAGE') THEN 'USAGE' ELSE NULL END)] FROM pg_catalog.pg_foreign_data_wrapper WHERE has_foreign_data_wrapper_privilege($1,fdwname,'USAGE');
$$ language sql;
Example:
edb=# select * from fdw_wrapper_privs('test_user');
username | fdwname | privleges
-----------+--------------+-----------
test_user | libpq_dblink | {USAGE}
(1 row)
5. To find foreign server privileges, the following functions can be used:
CREATE OR REPLACE FUNCTION foreign_server_privs(text) RETURNS table(username text, srvname name, privileges text[])
AS
$$
SELECT $1, s.srvname , ARRAY[
(CASE WHEN has_server_privilege($1,srvname,'USAGE') THEN 'USAGE' ELSE NULL END)] from pg_catalog.pg_foreign_server s WHERE has_server_privilege ($1,srvname,'USAGE');
$$
language sql;
6. To find language privileges, following function can be used:
CREATE OR REPLACE FUNCTION language_privs(text) RETURNS table(username text,srvname name, privileges text[])
AS
$$
SELECT $1, l.lanname, ARRAY[(CASE WHEN has_language_privilege($1,lanname,'USAGE') THEN 'USAGE' ELSE NULL END)] FROM pg_catalog.pg_language l where has_language_privilege($1,lanname,'USAGE');
$$ language sql;
7. To find schema privileges of a user, the following can be used:
CREATE OR REPLACE FUNCTION schema_privs(text) RETURNS table(username text, schemaname name, privileges text[])
AS
$$
SELECT $1, c.nspname, array(select privs from unnest(ARRAY[
( CASE WHEN has_schema_privilege($1,c.oid,'CREATE') THEN 'CREATE' ELSE NULL END),
(CASE WHEN has_schema_privilege($1,c.oid,'USAGE') THEN 'USAGE' ELSE NULL END)])foo(privs) WHERE privs IS NOT NULL)
FROM pg_namespace c where has_schema_privilege($1,c.oid,'CREATE,USAGE') AND c.nspparent=0;
$$ language sql;
Note: Above function can be used in Advanced Server. For schema_privilege in PostgreSQL, users can try the following function:
CREATE OR REPLACE FUNCTION schema_privs(text) RETURNS table(username text, schemaname name, privileges text[])
AS
$$
SELECT $1, c.nspname, array(select privs from unnest(ARRAY[
( CASE WHEN has_schema_privilege($1,c.oid,'CREATE') THEN 'CREATE' ELSE NULL END),
(CASE WHEN has_schema_privilege($1,c.oid,'USAGE') THEN 'USAGE' ELSE NULL END)])foo(privs) WHERE privs IS NOT NULL)
FROM pg_namespace c where has_schema_privilege($1,c.oid,'CREATE,USAGE');
$$ language sql;
8. To get privilege of a particular with view name, following function can be used:
CREATE OR REPLACE FUNCTION view_privs(text) returns table(username text, viewname regclass, privileges text[])
AS
$$
SELECT $1, c.oid::regclass, array(select privs from unnest(ARRAY [
( CASE WHEN has_table_privilege($1,c.oid,'SELECT') THEN 'SELECT' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'INSERT') THEN 'INSERT' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'UPDATE') THEN 'UPDATE' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'DELETE') THEN 'DELETE' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'TRUNCATE') THEN 'TRUNCATE' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'REFERENCES') THEN 'REFERENCES' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'TRIGGER') THEN 'TRIGGER' ELSE NULL END)]) foo(privs) where privs is not null) FROM pg_class c JOIN pg_namespace n on c.relnamespace=n.oid where n.nspname not in ('information_schema','pg_catalog','sys') and nspparent=0 and c.relkind='v' and has_table_privilege($1,c.oid,'SELECT, INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER') AND has_schema_privilege($1,c.relnamespace,'USAGE')
$$ language sql;
Note: Above function can be used in Advanced Server. For view_privs in PostgreSQL, users can try the following function:
CREATE OR REPLACE FUNCTION view_privs(text) returns table(username text, viewname regclass, privileges text[])
AS
$$
SELECT $1, c.oid::regclass, array(select privs from unnest(ARRAY [
( CASE WHEN has_table_privilege($1,c.oid,'SELECT') THEN 'SELECT' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'INSERT') THEN 'INSERT' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'UPDATE') THEN 'UPDATE' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'DELETE') THEN 'DELETE' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'TRUNCATE') THEN 'TRUNCATE' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'REFERENCES') THEN 'REFERENCES' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'TRIGGER') THEN 'TRIGGER' ELSE NULL END)]) foo(privs) where privs is not null) FROM pg_class c JOIN pg_namespace n on c.relnamespace=n.oid where n.nspname not in ('information_schema','pg_catalog','sys') and c.relkind='v' and has_table_privilege($1,c.oid,'SELECT, INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER') AND has_schema_privilege($1,c.relnamespace,'USAGE')
$$ language sql;
9. For Sequence Privilege, following function can be used:
CREATE OR REPLACE FUNCTION sequence_privs(text) RETURNS table(username text, sequence regclass, privileges text[])
AS
$$
SELECT $1, c.oid::regclass, array(select privs from unnest(ARRAY [
( CASE WHEN has_table_privilege($1,c.oid,'SELECT') THEN 'SELECT' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'UPDATE') THEN 'UPDATE' ELSE NULL END)]) foo(privs) where privs is not null) FROM pg_class c JOIN pg_namespace n on c.relnamespace=n.oid where n.nspname not in ('information_schema','pg_catalog','sys') and nspparent=0 and c.relkind='S' and
has_table_privilege($1,c.oid,'SELECT,UPDATE') AND has_schema_privilege($1,c.relnamespace,'USAGE')
$$ language sql;
Note: Above function can be used in Advanced Server. For sequence_privs in PostgreSQL, users can try the following function:
CREATE OR REPLACE FUNCTION sequence_privs(text) RETURNS table(username text, sequence regclass, privileges text[])
AS
$$
SELECT $1, c.oid::regclass, array(select privs from unnest(ARRAY [
( CASE WHEN has_table_privilege($1,c.oid,'SELECT') THEN 'SELECT' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'UPDATE') THEN 'UPDATE' ELSE NULL END)]) foo(privs) where privs is not null) FROM pg_class c JOIN pg_namespace n on c.relnamespace=n.oid where n.nspname not in ('information_schema','pg_catalog','sys') and c.relkind='S' and
has_table_privilege($1,c.oid,'SELECT,UPDATE') AND has_schema_privilege($1,c.relnamespace,'USAGE')
$$ language sql;
User can also make join on above functions to view the privileges on objects, something like given below:
select * from (
select username,'SCHEMA' as object_type,schemaname as object_name,privileges
FROM schema_privs('test_user')
UNION ALL
SELECT username,'TABLE' as object_type ,relname::name as object_name ,privs
FROM table_privs('test_user')
) as text1 order by 2 ;
Above will give table and schema privileges of a user test_user. Below is the output:
select * from
(select username,'SCHEMA' as object_type,schemaname as object_name,privileges FROM schema_privs('test_user')
UNION ALL
SELECT username,'TABLE' as object_type ,relname::name as object_name ,privs FROM table_privs('test_user')
) as text order by 2;
username | object_type | object_name | privileges
-----------+-------------+--------------------+------------------------
test_user | SCHEMA | pg_catalog | {USAGE}
test_user | SCHEMA | public | {CREATE,USAGE}
test_user | SCHEMA | information_schema | {USAGE}
test_user | SCHEMA | sys | {USAGE}
test_user | SCHEMA | dbo | {USAGE}
test_user | SCHEMA | test | {USAGE}
test_user | TABLE | test_id | {SELECT,UPDATE,DELETE}
(7 rows)
Filed under: Postgres Plus Advanced Server, PostgreSQL, PostgreSQL DBA SQLs, PPAS Tagged: Advanced Server, DBAs, pg catalog, Postgres Plus Advanced Server, PostgreSQL 11, PPAS 11, User privileges, Users 
