Direct database connection and row-level security


As mentioned in Backend business logic - #3 by whoops-aniseed-0z I’m implementing a backend service which will respond to commands that are enqueued by users, to effectively allow them to perform side effects beyond the basic CRUD operations supported by the Thin API.

I’ve got a table which the users will need to be able to read (read if user_id = ihp_user_id()), but which only the backend service is permitted to write to (I’ve currently set it to write if false).

Unfortunately, I’m getting an error in the backend service which trying to write to that table:
[42704] ERROR: unrecognized configuration parameter "rls.ihp_user_id"

Is there some way I can bypass the row-level security rules for the backend service? Or perhaps hardcode some kind of admin user ID in my RLS rules, and get the backend service to execute database operations as that user somehow?


yes this should actually be the default. When you go to your Project settingsDatabase, you should see the database credentials you need to e.g. connect from nodejs.

These credentials by default bypass Row level security. So as long as you use any standard postgres client, you should not receive any error related to rls.ihp_user_id.

With that database connect you then can temporarly enable RLS policies by switching the user, like this:

SET LOCAL ROLE "gCrGFqoSxLXGppQlWusNIXlMuRKetCaT_ihp_authenticated"; SET LOCAL rls.ihp_user_id = '<some uuid>';  SELECT * FROM tasks;

In the above sql code the gCrGFqoSxLXGppQlWusNIXlMuRKetCaT is my database username. You need to replace that with your project specific database username, the RLS user is always in the format <db_username>_ihp_authenticated (db_username is visible on Project settings -> Database).

Thanks for the response.

As it turns out, this was my mistake- I wasn’t specifying a value for the user_id column when inserting the row, and that seems to have been the cause of the error.

Thanks for explaining how to enable the RLS policies too, as that may come in handy in future.

1 Like