---
title: How to use Row-Level Security with Serverless SQL Database
description: This page explains how to use Row-Level Security with 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'


Row-Level Security is a database security mechanism that allows access only to specific rows of a table based on a user's role or permissions.

Row-Level Security can be activated with Serverless SQL Databases for a maximum of two different roles, having both read and write permissions. This can be used to restrict access to a subset of users with frameworks or tools such as [PostgREST](https://docs.postgrest.org/en/v12/).

This requires setting up different [IAM permissions sets](/iam/reference-content/permission-sets/) for each role (**ServerlessSQLDatabaseFullAccess** or **ServerlessSQLDatabaseReadWrite** for one role, and **ServerlessSQLDatabaseDataReadWrite** for the other).

<Requirements />

- 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 contained in the database displays, as you are connected with `role_admin`.
    
    <Message type="tip">
    You can verify the current role you are connected with using the following command:
    ```sql
    SELECT current_user;
    ```
    </Message>

## How to create an IAM application with Row Level Security enabled

1. Create a new [IAM application](/iam/how-to/create-application/).

2. Create a new [IAM policy](/iam/how-to/create-policy/), and add the **ServerlessSQLDatabaseDataReadWrite** permission set to the application you just created.

    <Message type="note">
    You must provide **ServerlessSQLDatabaseDataReadWrite** permission set and not **ServerlessSQLDatabaseReadWrite** permission set. Indeed, all connections to your database performed with the former permissions set will use `role_readwrite` in PostgreSQL, whereas all connections performed with the latter, or **ServerlessSQLDatabaseFullAccess** will use `role_admin` in PostgreSQL.
    </Message>

3. Create an [API Key](/iam/how-to/create-api-keys/) for this application, and connect to your Serverless SQL Database with this application.
    ```bash
    psql "postgres://[new-application-id]:[new-api-secret-key]@[database-hostname]:5432/[database-name]?sslmode=require"
    ```

4. Run the following command to list the `pets` this application has access to:
    ```sql
    SELECT * FROM pets;
    ```
    Only the pets with a `keeper` column value of `role_readwrite` display. Your new application can now only access a specific subset of rows based on its permissions.

    <Message type="tip">
    Row-level security and policies can be created or deleted by a table owner. In this example, you can check the table owner with the following command:
    ```sql
    select * from pg_tables where tablename = 'pets';
    ```
    </Message>