How to use Row-Level Security with PostgREST for Serverless SQL Databases
PostgREST's built-in Row Level Security based on users JWT relies either on role impersonation or transaction-scoped 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).
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 in the database displays, as you are connected with
role_admin
.
How to use Row Level Security with PostgREST
-
Install PostgREST by following the official documentation.
-
Create a
tutorial.conf
file with the following content: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 having ServerlessSQLDatabaseDataReadWrite permissions (not ServerlessSQLDatabaseReadWrite or ServerlessSQLDatabaseFullAccess)db-schemas
is your database schema. Usepublic
as a default value.jwt-secret
is a token generated using the following command:
openssl rand -base64 32
-
In a terminal, access the folder containing the
tutorial.conf
file, and run the command below to start a local PostgREST instance:postgrest tutorial.conf
-
Connect to your Serverless SQL Database with ServerlessSQLDatabaseFullAccess permissions, and run the following command to delete the
pets_keeper
policy previously applied to thepets
table:DROP POLICY pets_keeper ON pets;
-
Run the command below to create a new policy on the
pets
table: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 ofcurrent_user
, and thus checks for additional fields contained by the JWT, and not only therole
field. -
Generate a JWT with the following payload data:
{ "role": "role_readwrite", "user_type": "role_readwrite" }
-
Run the command below to query your database using the JWT you just created through PostgREST:
curl http://localhost:3000/pets \ -H "Authorization: Bearer $TOKEN"
A list of pets with a
role_readwrite
value forkeeper
displays.Your new application can now only access a specific subset of rows based on its permissions using transaction-scoped settings.