PostgreSQL user with SELECT only access to a VIEW without granting TABLE access

External systems (Tableau, Mode, etc) connecting to your data to run reports must definitely not have DDL access. In addition, reporting systems should not have direct access to tables. Tables may get altered over time and quite possible that some sensitive information might get exposed to external systems. As a result, it’s always safe to create explicit VIEWS on tables with well defined columns you would like to expose and setup a user which ONLY has access to the VIEW, and not the underlying table. Here’s how you can set it up. In the example below, I am using the postgres (superuser) to create the new user/role and then I connect as the database owner to GRANT SELECT permissions to the VIEW.

— Connect to the database payloads as postgres (superuser)

CREATE USER report_user WITH PASSWORD 'report_user';
ALTER ROLE report_user SET search_path TO payloads;

— Connect to the database payloads as the owner of the database. Create a VIEW on table system_event and grant the right access

CREATE OR REPLACE VIEW payloads.rv_system_event AS SELECT id, event_id, event_type_id, tenant, intg_type, event_type, trigger_type, created_at, updated_at, status, prc_api FROM payloads.system_event;
GRANT USAGE ON SCHEMA payloads TO report_user;
GRANT SELECT ON rv_system_event TO report_user;

In the end, connect to the database payloads as user report_user. You’ll be able to see definition for all the underlying tables.

payloads=> \d
                        List of relations
  Schema  |           Name            |   Type   |     Owner      
----------+---------------------------+----------+----------------
 payloads | system_event              | table    | payloads_owner
 payloads | system_event_id_seq       | sequence | payloads_owner
 payloads | rv_system_event           | view     | payloads_owner
 3 rows)

However, try executing a SELECT on the table system_event and you’ll notice that the user report_user does not have the permissions to do so.

payloads=> SELECT * FROM system_event;
ERROR:  permission denied for table system_event

And finally, try executing a SELECT on the view rv_system_event and you’ll see the results being displayed.