Database View to Organize Pivotal HDB or Pivotal Greenplum(GPDB) ACL Privileges
Article ID: 295180
Updated On:
Products
Issue/Introduction
This article will provide a basic understanding of ACL privileges to identify table / user privileges in HDB or 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 the 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. Example: 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 Greenplum Database (GPDB) all versions
- Pivotal HAWQ Database (HDB) all versions
Feedback
