Learning PostgreSQL 11 - Third Edition

Column-level security

PostgreSQL allows permissions to be defined on the column level. To explore this feature, let's create a table and role, as follows:

CREATE DATABASE test_column_acl;\c test_column_aclCREATE TABLE test_column_acl AS SELECT * FROM (values (1,2), (3,4)) as n(f1, f2);CREATE ROLE test_column_acl;GRANT SELECT (f1) ON test_column_acl TO test_column_acl;

To check column permissions, let's try to get all the data from the table, as follows:

test_column_acl=# SET ROLE test_column_acl ;SETtest_column_acl=> TABLE test_column_acl;ERROR: permission denied for relation test_column_acltest_column_acl=> SELECT f1 from test_column_acl ; f1 ----  1  3(2 rows)