Pivotal Knowledge Base

Follow

Database view to organize Pivotal HDB or Pivotal Greenplum(GPDB) ACL privileges

Symptom

This article will provide a basic understanding of ACL privileges to identify table / user privileges in HDB or Pivotal Greenplum (GPDB) and an example of database view to organize relacl code in a user friendly view

Resolution

HDB/GPDB database can have many tables and multiple users for those table. With that arises a need to maintain information related to a table and user access privileges for reference of administrator. This information is stored under relacl column of the pg_class system catalog table and the priviliges are maintained in the form of code. Ex: arwdxt

Below is the meaning of the relacl code:

a - Insert
r - SELECT
w - UPDATE
d - DELETE
R - RULES
x - REFERENCES
t - TRIGGER
arwdRxt - ALL

Below listed is an example for an entry listing ACL privileges. Value of relacl indicates that user gpadmin has the all the privileges, and gpadmin is also the grantor. 

gpadmin=# select relname,relacl from pg_class where relname ~ 'hawq_log_database';
relname | relacl
----------------------------------------------------------------+----------------- hawq_log_database | {gpadmin=arwdxt/gpadmin}

relacl format is:

<grantee>=<privileges>/<grantor>

The above stored information is raw and can be transformed based on your requirement. In the example below we have created a view which utilizes information stored in relacl column and provides a descriptive output. You can query the view either based on table name or the user name.

Note: The view created below is applicable for HDB, you can extend the case statement to include UPDATE / DELETE and other privileges since GPDB allows those capabilities.

1) Query based on relation name

gpadmin=# select * from dba_role_view where table_name like 'emp';
relname | nspname | grantee | user_priviliges | grantor | notes
---------+---------+-----------+-----------------+---------+----------------------------------------------------------------------
emp | public | gpadmin | SELECT,INSERT | gpadmin | UPDATE, DELETE, REFERENCES operation are not supported with HAWQ 1.x
emp | public | foo | SELECT,INSERT | gpadmin | UPDATE, DELETE, REFERENCES operation are not supported with HAWQ 1.x
emp | public | foo_child | SELECT,INSERT | foo | UPDATE, DELETE, REFERENCES operation are not supported with HAWQ 1.x

2) Query based on grantee

gpadmin=# select * from dba_role_view where grantee like 'foo';
relname | nspname | grantee | user_priviliges | grantor | notes
---------+---------+---------+-----------------+---------+----------------------------------------------------------------------
emp | public | foo | SELECT,INSERT | gpadmin | UPDATE, DELETE, REFERENCES operation are not supported with HAWQ 1.x

3) Query based on grantor

gpadmin=# select * from dba_role_view where grantor like 'foo';
relname | nspname | grantee | user_priviliges | grantor | notes

---------+---------+-----------+-----------------+---------+----------------------------------------------------------------------
emp | public | foo_child | SELECT,INSERT | foo | UPDATE, DELETE, REFERENCES operation are not supported with HAWQ 1.x

Here is the view definition:

 SELECT b.table_name, b.schema_name, b.grantee, b.user_priviliges, b.grantor, b.notes
FROM ( SELECT a.relname AS table_name, a.nspname AS schema_name,
CASE
WHEN length(a.user_scope) > 0 THEN a.user_scope
ELSE 'PUBLIC'::text
END AS grantee,
CASE
WHEN "position"(a.acl_detail, 'r'::text) > 0 THEN 'SELECT'::text
ELSE ''::text
END ||
CASE
WHEN "position"(a.acl_detail, 'a'::text) > 0 THEN ',INSERT'::text
ELSE ''::text
END AS user_priviliges,
a.grantor, 'UPDATE, DELETE, REFERENCES operation are not supported with HAWQ 1.x' AS notes
FROM
( SELECT c.relname, n.nspname, split_part(regexp_split_to_table(array_to_string(c.relacl, ','::text), ','::text), '='::text, 1) AS user_scope, split_part(split_part(regexp_split_to_table(array_to_string(c.relacl, ','::text), ','::text), '='::text, 2), '/'::text, 1) AS acl_detail, split_part(split_part(regexp_split_to_table(array_to_string(c.relacl, ','::text), ','::text), '='::text, 2), '/'::text, 2) AS grantor
FROM pg_class c, pg_namespace n
WHERE c.relnamespace = n.oid) a
UNION ALL
SELECT c.relname AS table_name, n.nspname AS schema_name, pgt.tableowner AS grantee, 'SELECT,INSERT' AS user_priviliges, pgt.tableowner AS grantor, 'UPDATE, DELETE, REFERENCES operation are not supported with HAWQ 1.x' AS notes
FROM pg_class c, pg_namespace n, pg_tables pgt
WHERE c.relnamespace = n.oid AND c.relname = pgt.tablename AND n.nspname = pgt.schemaname AND array_to_string(c.relacl, ','::text) IS NULL) b;

Additional Information
Pivotal internal employee reference: GPSQL-1860

Comments

Powered by Zendesk