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
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;