Busk: Created page with "# Simple Example ``` CREATE USER alice PASSWORD 'password'; CREATE USER bob PASSWORD 'password'; CREATE USER carol PASSWORD 'password'; GRANT group_admin TO alice; GRANT group..."

2024-05-27T13:47:51Z

Created page with "# Simple Example ``` CREATE USER alice PASSWORD 'password'; CREATE USER bob PASSWORD 'password'; CREATE USER carol PASSWORD 'password'; GRANT group_admin TO alice; GRANT group..."

New page

# Simple Example
```
CREATE USER alice PASSWORD 'password'; CREATE USER bob PASSWORD 'password'; CREATE USER carol PASSWORD 'password'; GRANT group_admin TO alice; GRANT group_user TO bob; GRANT group_manager TO carol;
```

```
CREATE TABLE confidential_data ( id SERIAL PRIMARY KEY, data TEXT, user_groups TEXT[] ); INSERT INTO confidential_data (data, user_groups) VALUES ('Admin and Manager Data', ARRAY['group_admin', 'group_manager']), ('User Data', ARRAY['group_user']), ('Admin and User Data', ARRAY['group_admin', 'group_user']);
```

```
ALTER TABLE confidential_data ENABLE ROW LEVEL SECURITY;
```

```
CREATE POLICY group_policy ON confidential_data FOR SELECT USING (EXISTS ( SELECT 1 FROM unnest(user_groups) AS g WHERE g = ANY (SELECT rolname FROM pg_roles WHERE pg_has_role(current_user, oid, 'member')) ));
```

```
ALTER TABLE confidential_data FORCE ROW LEVEL SECURITY;
```

# Detailed Example

```
CREATE ROLE group_admin; CREATE ROLE group_user; CREATE ROLE group_manager; CREATE USER alice PASSWORD 'password'; CREATE USER bob PASSWORD 'password'; CREATE USER carol PASSWORD 'password'; GRANT group_admin TO alice; GRANT group_user TO bob; GRANT group_manager TO carol;
```

```
CREATE TABLE confidential_data ( id SERIAL PRIMARY KEY, data TEXT, user_groups TEXT[] ); INSERT INTO confidential_data (data, user_groups) VALUES ('Admin and Manager Data', ARRAY['group_admin', 'group_manager']), ('User Data', ARRAY['group_user']), ('Admin and User Data', ARRAY['group_admin', 'group_user']);
```

```
ALTER TABLE confidential_data ENABLE ROW LEVEL SECURITY; CREATE POLICY group_policy ON confidential_data FOR SELECT USING (EXISTS ( SELECT 1 FROM unnest(user_groups) AS g WHERE g = ANY (SELECT rolname FROM pg_roles WHERE pg_has_role(current_user, oid, 'member')) )); ALTER TABLE confidential_data FORCE ROW LEVEL SECURITY;
```

```
SET ROLE alice; SELECT * FROM confidential_data;
Alice should see rows where user_groups contains group_admin.
As Bob (group_user):

SET ROLE bob; SELECT * FROM confidential_data;
Bob should see rows where user_groups contains group_user.
As Carol (group_manager):

SET ROLE carol; SELECT * FROM confidential_data;
Carol should see rows where user_groups contains group_manager.
```

RLS example - Revision history

Busk: Created page with "# Simple Example ``` CREATE USER alice PASSWORD 'password'; CREATE USER bob PASSWORD 'password'; CREATE USER carol PASSWORD 'password'; GRANT group_admin TO alice; GRANT group..."