Implementing Row-Level Security | Dataford Interview Questions

What is your process for implementing RLS (row-level security) to ensure data privacy for KPMG client data?

Write a PostgreSQL query that returns the client records visible to the current analyst. Treat analyst ID 101 as the default session identity when app.analyst_id is not set, and include only active access grants.

Output

One row per authorized client, with client_id, client_name, and region
Exclude clients without an active grant or with only revoked grants
Order by client_id ascending

Schema

client_records

Column	Type	Description
client_idPK	INTEGER	Unique identifier for the client
client_name	VARCHAR(120)	Client organization name
region	VARCHAR(60)	Client operating region

analyst_client_access

Column	Type	Description
access_idPK	INTEGER	Unique access grant identifier
analyst_id	INTEGER	Analyst receiving the grant
client_id	INTEGER	Client covered by the grant
revoked_at	TIMESTAMP	Time the grant was revoked, or NULL while active

Your question is Implementing Row-Level Security. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem