Revoke Access From Just One System Table

Jim_Knicely · February 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 from just one user? To do that I first have to revoke the SELECT privilege from PUBLIC then re-grant the SELECT privileges to all users expect the user that I wish to remove the access.

Example:

I don’t want user JIM to be able to read from the V_CATALOG.DATABASES system table.

Currently, he 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)

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 FROM PUBLIC;
REVOKE PRIVILEGE

dbadmin=> \! vsql -Atc "SELECT 'GRANT SELECT ON v_catalog.databases TO ' || user_name || ';' FROM users WHERE NOT is_super_user AND user_name <> 'jim';" | vsql -e
GRANT SELECT ON v_catalog.databases TO user1;
GRANT PRIVILEGE
GRANT SELECT ON v_catalog.databases TO user2;
GRANT PRIVILEGE
GRANT SELECT ON v_catalog.databases TO monitor;
GRANT PRIVILEGE
GRANT SELECT ON v_catalog.databases TO role_user1;
GRANT PRIVILEGE
GRANT SELECT ON v_catalog.databases TO role_user2;
GRANT PRIVILEGE
GRANT SELECT ON v_catalog.databases TO dave;
GRANT PRIVILEGE

Now user JIM can no longer read from the V_CATALOG.DATABASES system table!

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

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)

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

Have fun!