# Apply row-level security (/docs/guides/secure/row-level-security)



Apply row-level security [#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 [#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](/docs/reference/api/databases#post-v1databases).
* 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](/docs/reference/api/tables#post-v1tablestable).

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

1\. Create a role [#1-create-a-role]

Create a role for the readers.

```sql test
CREATE ROLE acme_app_reader
```

2\. Grant table access [#2-grant-table-access]

Grant `SELECT` on the event table.

```sql test known-failure="GRANT returns code 497 after access repair (local stack)"
GRANT SELECT ON analytics.events TO acme_app_reader
```

3\. Create a permissive policy [#3-create-a-permissive-policy]

Create a permissive policy for the tenant.

```sql test
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 [#4-replace-the-permissive-policy]

Replace the policy when its condition changes.

```sql test
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 [#5-add-a-restrictive-policy]

Add a restrictive policy for the region.

```sql test
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 [#6-create-a-role-key]

Create a key that uses the role.

```bash
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.

```json
{
  "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 [#7-query-with-the-role-key]

Query the table with the returned key.

```bash
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 [#check-the-result]

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

Cleanup [#cleanup]

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

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

Drop the restrictive policy first.

```sql
DROP ROW POLICY us_app_only ON events
```

Drop the permissive policy.

```sql
DROP ROW POLICY acme_app_only ON events
```

Drop the role after you delete its keys.

```sql
DROP ROLE acme_app_reader
```

The parser rejects subqueries in row policies.

```sql test expect-error="Unsupported row policy statement."
CREATE ROW POLICY tenant_filter ON events USING (SELECT 1) TO acme_app_reader
```

Next steps [#next-steps]

* [Access-control concepts](/docs/concepts/access-control)
* [Restrict an API key](/docs/guides/secure/restrict-api-key-access)
* [Row-policy statements](/docs/reference/sql/statements/row-policy)