Row level select policy on Postgres (greenplum)

Question

Im using a Greenploum database but im assuming its more or less the same as Postgres. I want to implement a row level security policy based on the value of a column which the table is partitioned by.

I have a table. TABLE rank (id int, rank int, year int, gender char(1), count int, source_system text)

example data: (1,2, 2012, 1,1, source_system_a), (2,1, 2012, 1,1, source_system_b), (3,4, 2012, 1,1, source_system_a), (4,3, 2012, 1,1, source_system_c),

The table is partitioned based on the source_system column. I want to have a set of users that can see all the data and a set of users that cannot see everything based on the source_system column. source_system_a should be a secure value therefore only those with secure permission should be able to see the row with source_system_a.

for instance,

user a (can see all) does 'select * from rank;'

result: 1,2, 2012, 1,1, source_system_a, 2,1, 2012, 1,1, source_system_b, 3,4, 2012, 1,1, source_system_a, 4,3, 2012, 1,1, source_system_c,

user b (not secure) does 'select * from rank;'

result: 2,1, 2012, 1,1, source_system_b, 4,3, 2012, 1,1, source_system_c,

Many Thanks

Answer 1

Greenplum doesn't have Row Level Security (RLS) aside from creating views for different user groups. If you use a view to dynamically hide rows, there is a way to see the hidden rows so don't do that.

PostgreSQL had the same problem with views until it introduced the security_barrier feature but Greenplum doesn't have this yet.

So for your example, I would create two views:

CREATE TABLE rank (id int, rank int, year int, gender char(1), count int, source_system text) DISTRIBUTED BY (id);
CREATE USER user_a;
CREATE USER user_b;

CREATE VIEW vw_rank_a AS SELECT * FROM rank;
CREATE VIEW vw_rank_b AS SELECT * FROM rank WHERE source_system <> 'source_system_a';

GRANT SELECT ON vw_rank_a TO user_a;
GRANT SELECT ON vw_rank_b TO user_b;