July 2012 – Database Technologies & Cyber Security Platform

PostgreSQL has some useful functions which can be use to know about the privilege of a user on a particular Database object. Those functions is available in following link:

http://www.postgresql.org/docs/9.1/static/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.

Since, DBAs/Users are interested in listing objects and privileges of a Database User and currently PostgreSQL doesn’t have a view, which 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 9.1. These are basic functions and can be expanded, as per need, to show more privileges like WITH GRANT OPTION.

Following are functions which can use to get 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,REFRENCES,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,UPDATE,DELETE}
(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 &lt;&gt; 'template0';
$$ language sql;

Example:

edb=# select * from database_privs('test_user');
 username  |  dbname   |         privileges          
-----------+-----------+-----------------------------
 test_user | template1 | {CONNECT}
 test_user | edb       | {CONNECT,TEMPORARY,CONNECT}
(2 rows)

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 Dataa 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 following functions can be use:

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 priveleges, following function can be use:

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 following can be use:

CREATE OR REPLACE FUNCTION schema_privs(text) RETURNS table(username text, schemaname name, privieleges 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 use in Advanced Server. For schema_privilege in PostgreSQL, user can try following function:

CREATE OR REPLACE FUNCTION schema_privs(text) RETURNS table(username text, schemaname name, privieleges 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 use:

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,REFRENCES,TRIGGER') AND has_schema_privilege($1,c.relnamespace,'USAGE')
$$ language sql;

9. For Sequence Privilege following function can be use:

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)]),
(CASE WHEN has_table_privilege($1,c.oid,'USAGE') 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,USAGE')  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,privieleges 
    FROM schema_privs('test_user') 
 UNION ALL
SELECT username,'TABLE' as object_type ,relname::name as object_name ,privs 
    FROM table_privs('test_user')
 ) order by 2;

Above will give table and schema privileges of a user test_user. Below is output:

edb=# select * from  
edb-# (select username,'SCHEMA' as object_type,schemaname as object_name,privieleges FROM schema_privs('test_user') 
edb(#  UNION ALL
edb(# SELECT username,'TABLE' as object_type ,relname::name as object_name ,privs  FROM table_privs('test_user')
edb(# ) order by 2;

 username  | object_type |    object_name     |      privieleges       
-----------+-------------+--------------------+------------------------
 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)

PostgreSQL has pg_stat_activity view which can be use to get the session details. This view gives following information:

1. datid: database OID
2. datname: database name
3. procpid: process ID
4. usesysid: user OID 
5. usename: user name
6. application_name: application name
7. client_addr: client's address
8. client_hostname: host name (if available)
9. client_port: Clients port number
10. backend_start: time at which the server process started
11. xact_start: time at which current transaction started
12: query_start: time at which current query began execution started
13: waiting:  process's waiting status
14. current_query: text of the current query.

Above is good for monitoring sessions in postgresql. However, suppose user wants to know about percentage of CPU & Memory used by a particular session/user, then he can get this information by using plperlu function.

To Create the pleperlu function, user needs to have plperlu language installed in DB. For creating the plperlu, connect to Database as super user and execute following command:

edb=# CREATE EXTENSION plperlu;
CREATE EXTENSION

Now create following functions:

CREATE OR REPLACE FUNCTION get_pid_cpu_mem(int) returns table(PID INT,CPU_perc float,MEM_perc float) 
as
$$
  my $ps = "ps aux";
  my $awk = "awk '{if (\$2==".$_[0]."){print \$2\":\"\$3\":\"\$4}}'";
  my $cmd = $ps."|".$awk;
  $output = `$cmd 2>&1`;
  @output = split(/[\n\r]+/,$output);
  foreach $out (@output)
  { 
    my @line = split(/:/,$out);
    return_next{'pid' => $line[0],'cpu_perc' => $line[1], 'mem_perc' => $line[2]};
    return undef;
  }
   return;
 $$ language plperlu;

Note:: Above function is made for PostgreSQL running on Linux/Unix System.

Now user can use above function with pg_stat_activity to monitor the percentage of cpu and memory used by particular user/process.

Following is one snapshot:

edb=# select procpid,usename,application_name, get_pid_cpu_mem(procpid).cpu_perc,get_pid_cpu_mem(procpid).mem_perc,current_query from pg_stat_activity;
-[ RECORD 1 ]----+--------------------------------------------------------------------------------------------------------------------------------------------------
procpid          | 12991
usename          | enterprisedb
application_name | psql
cpu_perc         | 0
mem_perc         | 0.6
current_query    | select procpid,usename,application_name, get_pid_cpu_mem(procpid).cpu_perc,get_pid_cpu_mem(procpid).mem_perc,current_query from pg_stat_activity;

Database Technologies & Cyber Security Platform

We explore and grow together

Month: July 2012

List user privileges in PostgreSQL/PPAS 9.1

Monitor CPU and MEMORY percentage used by each process in PostgreSQL/PPAS 9.1