Revoke Access to Multiple System Tables from Multiple Users with a New Role

Jim_Knicely · March 2019

Vertica provides an API (application programming interface) for monitoring various features and functions within a database in the form of system tables. These tables provide a robust, stable set of views that let you monitor information about your system's resources, background processes, workload, and performance, allowing you to more efficiently profile, diagnose, and view historical data equivalent to load streams, query profiles, tuple mover operations, and more.

System tables are grouped into the following schemas:

V_CATALOG — information about persistent objects in the catalog
V_MONITOR — information about transient system state

The SELECT privilege on all system tables is granted to PUBLIC. Therefore, by default all user can query them.

But what If I want to revoke the SELECT privilege on several system tables from just one or more users? To do that I first have to revoke the SELECT privilege from PUBLIC, create a new Role which has the SELECT privilege, then grant the new Role to all users expect the users that I wish to remove the access.

Example:

I don’t want users JIM and DAVE to be able to read from the V_CATALOG.DATABASES nor the V_CATALOG.LICENSE_AUDITS system table.

Currently, they can.

dbadmin=> \c - jim
You are now connected as user "jim".

dbadmin=> SELECT database_name FROM v_catalog.databases;
database_name
---------------
test_db
(1 row)

dbadmin=> \c - dave
You are now connected as user "dave".

dbadmin=> SELECT database_name FROM v_catalog.databases;
database_name
---------------
test_db
(1 row)

Let’s change that!

dbadmin=> \c - dbadmin
You are now connected as user "dbadmin".

dbadmin=> SELECT object_schema, object_name, privileges_description, grantee
dbadmin->   FROM v_catalog.grants
dbadmin->  WHERE object_name = 'databases';
object_schema | object_name | privileges_description | grantee
---------------+-------------+------------------------+---------
v_catalog     | databases   | SELECT                 | public
(1 row)

dbadmin=> REVOKE SELECT ON v_catalog.databases, v_catalog.license_audits FROM public;
REVOKE PRIVILEGE

dbadmin=> CREATE ROLE system_tables_role;
CREATE ROLE

dbadmin=> GRANT SELECT ON v_catalog.databases, v_catalog.license_audits TO system_tables_role;
GRANT PRIVILEGE

dbadmin=> SELECT object_schema, object_name, privileges_description, grantee
dbadmin->   FROM v_catalog.grants
dbadmin->  WHERE object_name = 'databases';
object_schema | object_name | privileges_description |      grantee
---------------+-------------+------------------------+--------------------
v_catalog     | databases   | SELECT                 | system_tables_role
(1 row)

dbadmin=>  \! vsql -Atc "SELECT 'GRANT system_tables_role TO ' || user_name || '; ALTER USER ' || user_name || ' DEFAULT ROLE ' || CASE WHEN default_roles = '' THEN 'system_tables_role' ELSE REPLACE(default_roles, '*', '') || ', system_tables_role' END || ';' FROM users WHERE NOT is_super_user AND user_name NOT IN ('jim', 'dave');" | vsql -e
GRANT system_tables_role TO user1;
GRANT ROLE
ALTER USER user1 DEFAULT ROLE user_role1, user_role2, user_role3, system_tables_role;
ALTER USER
GRANT system_tables_role TO user2;
GRANT ROLE
ALTER USER user2 DEFAULT ROLE system_tables_role;
ALTER USER
GRANT system_tables_role TO monitor;
GRANT ROLE
ALTER USER monitor DEFAULT ROLE sysmonitor, system_tables_role;
ALTER USER
GRANT system_tables_role TO role_user1;
GRANT ROLE
ALTER USER role_user1 DEFAULT ROLE r1, system_tables_role;
ALTER USER
GRANT system_tables_role TO role_user2;
GRANT ROLE
ALTER USER role_user2 DEFAULT ROLE r1, r2, system_tables_role;
ALTER USER
GRANT system_tables_role TO dave;
GRANT ROLE
ALTER USER dave DEFAULT ROLE system_tables_role;
ALTER USER

Now users JIM and DAVE can no longer read from the V_CATALOG.DATABASES and V_CATALOG.LICENSE_AUDITS system tables!

dbadmin=> \c - jim
You are now connected as user "jim".

dbadmin=> SELECT database_name FROM v_catalog.databases;
ERROR 4367:  Permission denied for relation databases

dbadmin=> SELECT database_size_bytes FROM v_catalog.license_audits LIMIT 1;
ERROR 4367:  Permission denied for relation license_audits

dbadmin=> SHOW ENABLED ROLES;
     name      | setting
---------------+---------
enabled roles |
(1 row)

dbadmin=> \c - dave
You are now connected as user "dave".

dbadmin=> SELECT database_name FROM v_catalog.databases;
ERROR 4367:  Permission denied for relation databases

dbadmin=> SELECT database_size_bytes FROM v_catalog.license_audits LIMIT 1;
ERROR 4367:  Permission denied for relation license_audits

dbadmin=> SHOW ENABLED ROLES;
     name      | setting
---------------+---------
enabled roles |
(1 row)

But other users can!

dbadmin=> \c - user1
You are now connected as user "user1".

dbadmin=> SELECT database_name FROM v_catalog.databases;
database_name
---------------
test_db
(1 row)

dbadmin=> SELECT database_size_bytes FROM v_catalog.license_audits LIMIT 1;
database_size_bytes
---------------------
            37462123
(1 row)

dbadmin=> SHOW ENABLED ROLES;
     name      |                         setting
---------------+----------------------------------------------------------
enabled roles | user_role1*, user_role2, user_role3*, system_tables_role
(1 row)

Helpful Links:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/Monitoring/Vertica/UsingSystemTables.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/SystemTables/CATALOG/GRANTS.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Statements/GRANT/GRANTStatements.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Statements/REVOKE/REVOKEStatements.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Statements/CREATEROLE.htm

Have fun!