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:

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 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 use Row Level Security with PostgREST

1. Install PostgREST by following the official documentation.

2. 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. Use public as a default value.
   • jwt-secret is a token generated using the following command:

   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:

   postgrest tutorial.conf 
   

   Tip

   You can check that you can query your database by generating a JWT with {"role": "role_readwrite"} as the payload data, then running the command below, where $TOKEN is your generated JWT:

    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:

   DROP POLICY pets_keeper ON pets;
   

5. 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 of current_user, and thus checks for additional fields contained by the JWT, and not only the role field.

6. Generate a JWT with the following payload data:

   {
       "role": "role_readwrite",
       "user_type": "role_readwrite"
   }
   

   Tip

   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:

    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.

   Tip

   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 documentation for more information.