How to use Row-Level Security with Serverless SQL Database

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.

This requires setting up different IAM permissions sets for each role (ServerlessSQLDatabaseFullAccess or ServerlessSQLDatabaseReadWrite for one role, and ServerlessSQLDatabaseDataReadWrite for the other).

Before you start

To complete the actions presented below, you must have:

• A Scaleway account logged into the console
• Owner status or IAM permissions allowing you to perform actions in the intended Organization
• Created a Serverless SQL Database

How to add sample data and enable PostgreSQL Row Level Security

1. Connect to your Serverless SQL Database with a PostgreSQL client such as psql:

   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:

   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:

   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:

   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:

   SELECT * FROM pets;
   

   All the data contained in the database displays, as you are connected with role_admin.

   Tip

   You can verify the current role you are connected with using the following command:

   SELECT current_user;
   

How to create an IAM application with Row Level Security enabled

1. Create a new IAM application.

2. Create a new IAM policy, and add the ServerlessSQLDatabaseDataReadWrite permission set to the application you just created.

   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.

3. Create an API Key for this application, and connect to your Serverless SQL Database with this application.

   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:

   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.

   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:

   select * from pg_tables where tablename = 'pets';