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
-
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"
-
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);
-
Run the command below to enable Row-Level Security:
ALTER TABLE pets ENABLE row level security;
-
Run the command below to create a PostgreSQL policy so that users or applications connecting with
role_readwrite
can access apet
row only if itskeeper
column value isrole_readwrite
:CREATE POLICY pets_keeper ON pets TO role_readwrite USING (keeper = current_user);
-
(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
.
How to create an IAM application with Row Level Security enabled
-
Create a new IAM application.
-
Create a new IAM policy, and add the ServerlessSQLDatabaseDataReadWrite permission set to the application you just created.
-
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"
-
Run the following command to list the
pets
this application has access to:SELECT * FROM pets;
Only the pets with a
keeper
column value ofrole_readwrite
display. Your new application can now only access a specific subset of rows based on its permissions.