Row-level security (RLS) attaches a policy to a table: a Boolean expression PostgreSQL 1,289 adds to every query a matching role runs, as if it were part of the WHERE clause. Once RLS is enabled, a role that no policy admits sees no rows at all. BookNest's regional analysts each see their countries' sales, driven by a mapping table rather than one policy per analyst:
CREATE ROLE analysts NOLOGIN; -- a group role
CREATE ROLE ana_eu LOGIN IN ROLE analysts;
CREATE ROLE ana_apac LOGIN IN ROLE analysts;
GRANT USAGE ON SCHEMA mart TO analysts;
GRANT SELECT ON mart.sales TO analysts;
CREATE TABLE mart.region_access (role_name name, country char(2)); -- who sees which rows
INSERT INTO mart.region_access VALUES ('ana_eu', 'GB'), ('ana_eu', 'DE'),
('ana_apac', 'AU'), ('ana_apac', 'NZ'), ('ana_apac', 'MY'), ('ana_apac', 'SG');
GRANT SELECT ON mart.region_access TO analysts;
ALTER TABLE mart.sales ENABLE ROW LEVEL SECURITY;
CREATE POLICY region_rows ON mart.sales FOR SELECT TO analysts
USING (country IN (SELECT country FROM mart.region_access WHERE role_name = current_user));
SET ROLE ana_eu;
SELECT current_user, count(*) AS lines, string_agg(DISTINCT country, ',') FROM mart.sales;
SET ROLE ana_apac;
SELECT current_user, count(*) AS lines, string_agg(DISTINCT country, ',') FROM mart.sales;
RESET ROLE; -- the owner is not subject to RLS
SELECT current_user, count(*) AS lines, count(DISTINCT country) AS countries FROM mart.sales;Output
current_user | lines | string_agg --------------+-------+------------ ana_eu | 23808 | DE,GB ... ana_apac | 27523 | AU,MY,NZ,SG ... postgres | 137944 | 10
Same table, same query, three answers. Owners bypass RLS unless the table is set to FORCE ROW LEVEL SECURITY; superusers and BYPASSRLS roles always do, so connect BI tools as ordinary roles. Policies can also restrict writes (WITH CHECK) and combine with OR unless declared AS RESTRICTIVE.