Filter SELECT results for a database role with permissive and restrictive policies.

Apply row-level security

Row policies filter reads for database roles. They do not replace table grants: create the role, grant SELECT, then attach a policy.

Before you begin

Set RAWTREE_API_KEY to an administrator API key.
Use analytics. If it does not exist, create it with the POST /v1/databases endpoint.
Pass ?database=analytics on each query. If you omit it, the Query API uses the API key's stored default database.
Use an events table with these rows. Insert them through the table API.

tenant	environment	region
`acme`	`prod`	`us`
`acme`	`dev`	`eu`
`other`	`prod`	`us`

1. Create a role

Create a role for the readers.

CREATE ROLE acme_app_reader

2. Grant table access

Grant SELECT on the event table.

GRANT SELECT ON analytics.events TO acme_app_reader

3. Create a permissive policy

Create a permissive policy for the tenant.

CREATE ROW POLICY acme_app_only ON events FOR SELECT USING __raw_data.tenant::String = 'acme' TO acme_app_reader

4. Replace the permissive policy

Replace the policy when its condition changes.

CREATE ROW POLICY OR REPLACE acme_app_only ON events FOR SELECT USING __raw_data.tenant::String = 'acme' AND __raw_data.environment::String = 'prod' TO acme_app_reader

5. Add a restrictive policy

Add a restrictive policy for the region.

CREATE ROW POLICY us_app_only ON events AS RESTRICTIVE FOR SELECT USING __raw_data.region::String = 'us' TO acme_app_reader

6. Create a role key

Create a key that uses the role.

curl -X POST 'https://api.rawtree.com/v1/keys?database=analytics' \
  --header "Authorization: Bearer $RAWTREE_API_KEY" \
  --header "Content-Type: application/json" \
  -d '{"name":"acme-app-reader","database_roles":["acme_app_reader"]}'

The response contains the new key's ID and token.

{
  "id": "<key-id>",
  "token": "rt_..."
}

Save the token value as READER_API_KEY and the id value as READER_KEY_ID.

7. Query with the role key

Query the table with the returned key.

curl -X POST 'https://api.rawtree.com/v1/query?database=analytics' \
  --header "Authorization: Bearer $READER_API_KEY" \
  --header "Content-Type: application/json" \
  -d '{"sql":"SELECT count() AS visible FROM events"}'

The permissive policy uses OR across matching policies. The restrictive policy uses AND with the permissive result.

Check the result

The role key sees one row after both policies apply. Compare its count with the total row count in events.

Cleanup

If the service no longer needs the role key, delete it.

curl -X DELETE "https://api.rawtree.com/v1/keys/$READER_KEY_ID" \
  --header "Authorization: Bearer $RAWTREE_API_KEY"

Drop the restrictive policy first.

DROP ROW POLICY us_app_only ON events

Drop the permissive policy.

DROP ROW POLICY acme_app_only ON events

Drop the role after you delete its keys.

DROP ROLE acme_app_reader

The parser rejects subqueries in row policies.

CREATE ROW POLICY tenant_filter ON events USING (SELECT 1) TO acme_app_reader

Apply row-level security

Apply row-level security

Before you begin

1. Create a role

2. Grant table access

3. Create a permissive policy

4. Replace the permissive policy

5. Add a restrictive policy

6. Create a role key

7. Query with the role key

Check the result

Cleanup

Next steps

On this page