---
title: How to use Row-Level Security with PostgREST for Serverless SQL Databases
description: This page provides the steps to use Row-Level Security with PostGREST for Serverless SQL Databases
tags: sql-databases serverless database row-level-security postgresql postgrest
dates:
validation: 2025-10-21
posted: 2024-09-24
---
import Requirements from '@macros/iam/requirements.mdx'
PostgREST's built-in Row Level Security based on users JWT relies either on [role impersonation](https://docs.postgrest.org/en/v12/references/auth.html#user-impersonation) or [transaction-scoped settings](https://docs.postgrest.org/en/v12/references/transactions.html#tx-settings).
Due to connection pooling, Serverless SQL Databases currently only support transaction-scoped settings and requires using a single PostgreSQL role for all queries (the internal `role_readwrite` in PostgreSQL).
- A Scaleway account logged into the [console](https://console.scaleway.com)
- [Owner](/iam/concepts/#owner) status or [IAM permissions](/iam/concepts/#permission) allowing you to perform actions in the intended Organization
- [Created a Serverless SQL Database](/serverless-sql-databases/how-to/create-a-database/)
## How to add sample data and enable PostgreSQL Row Level Security
1. [Connect to your Serverless SQL Database](/serverless-sql-databases/quickstart/#how-to-connect-to-a-database) with a PostgreSQL client such as `psql`:
```bash
psql "postgres://[user-or-application-id]:[api-secret-key]@[database-hostname]:5432/[database-name]?sslmode=require"
```
2. Add sample data to the database using the following command:
```sql
CREATE TABLE pets (name varchar, keeper varchar, id int);
INSERT INTO pets VALUES ('Stuart','role_admin',1),('Nemo','role_admin',2),('Alfie','role_readwrite',3),('Peanut','role_readwrite',4);
```
3. Run the command below to enable **Row Level Security**:
```sql
ALTER TABLE pets ENABLE row level security;
```
4. Run the command below to create a PostgreSQL policy so that users or applications connecting with `role_readwrite` can access a `pet` row only if its `keeper` column value is `role_readwrite`:
```sql
CREATE POLICY pets_keeper ON pets TO role_readwrite USING (keeper = current_user);
```
5. (Optional) Run the command below to check that you can see all the data with your current connection:
```sql
SELECT * FROM pets;
```
All the data in the database displays, as you are connected with `role_admin`.
You can verify the current role you are connected with using the following command:
```sql
SELECT current_user;
```
## How to use Row Level Security with PostgREST
1. Install PostgREST by following the [official documentation](https://docs.postgrest.org/en/v12/tutorials/tut0.html#step-1-install-postgresql).
2. Create a `tutorial.conf` file with the following content:
```json
db-uri = "postgres://[user-or-application-id]:[api-secret-key]@[database-hostname]:5432/[database-name]?sslmode=require"
db-schemas = "[your database schema]"
jwt-secret = "[your jwt secret]"
```
Where:
- `db-uri` must use credentials with an [application](/iam/how-to/create-application/) having **ServerlessSQLDatabaseDataReadWrite** permissions (not **ServerlessSQLDatabaseReadWrite** or **ServerlessSQLDatabaseFullAccess**)
- `db-schemas` is your database schema. Use `public` as a default value.
- `jwt-secret` is a token generated using the following command:
```bash
openssl rand -base64 32
```
3. In a terminal, access the folder containing the `tutorial.conf` file, and run the command below to start a local PostgREST instance:
```bash
postgrest tutorial.conf
```
You can check that you can query your database by [generating a JWT](https://docs.postgrest.org/en/v12/tutorials/tut1.html#step-3-sign-a-token) with `{"role": "role_readwrite"}` as the payload data, then running the command below, where `$TOKEN` is your generated JWT:
```bash
curl http://localhost:3000/pets \
-H "Authorization: Bearer $TOKEN"
```
A list of pets displays.
4. Connect to your Serverless SQL Database with **ServerlessSQLDatabaseFullAccess** permissions, and run the following command to delete the `pets_keeper` policy previously applied to the `pets` table:
```sql
DROP POLICY pets_keeper ON pets;
```
5. Run the command below to create a new policy on the `pets` table:
```sql
CREATE POLICY pets_keeper ON pets TO role_readwrite
USING (keeper = current_setting('request.jwt.claims', true)::json->>'user_type');
```
This policy uses `current_setting` instead of `current_user`, and thus checks for additional fields contained by the JWT, and not only the `role` field.
6. [Generate a JWT](https://docs.postgrest.org/en/v12/tutorials/tut1.html#step-3-sign-a-token) with the following payload data:
```json
{
"role": "role_readwrite",
"user_type": "role_readwrite"
}
```
Here, the `user_type` value from the JWT will be checked against the `keeper` column value in your database to authorize access. You can replace `"user_type": "role_readwrite"` with any alternative field name or value depending on your use case. However, you must keep `"role": "role_readwrite"` for any users you want to authenticate through PostgREST, because other roles (such as `role_admin`) have too many permissions and will be able to see any data.
7. Run the command below to query your database using the JWT you just created through PostgREST:
```bash
curl http://localhost:3000/pets \
-H "Authorization: Bearer $TOKEN"
```
A list of pets with a `role_readwrite` value for `keeper` displays.
Your new application can now only access a specific subset of rows based on its permissions using transaction-scoped settings.
You can change your JWT payload data with `"user_type": "role_admin"` and see that only another set of rows will be displayed.
To go further, try adding fields or values to filter, and edit your policy to filter and give your policy a more complex set of rules. Refer to the [official PostgREST](https://docs.postgrest.org/en/v12/explanations/db_authz.html) documentation for more information.