Postgres: auto-populating an `INSERT` field based on session variable

user_163832 0
Viewed 143

I have a web app backed by Postgres.

  • Each web app request should only read/write data for the current logged-in user.
  • Every table with user data has a user_id column.

I occasionally have bugs where I forget to add user_id = ? to the WHERE clause of an SQL request. To protect against this problem in a general way, I'm looking into Postgres row-level security (article):

  • Set a policy on every user data table: CREATE POLICY table_policy ON table USING (user_id::TEXT = current_setting('app.user_id'))
  • In the web app, when a request begins, set the current logged-in user ID on the request's connection: SET app.user_id = ?.

This allows me to completely ignore user_id when writing SELECT and UPDATE requests.

My remaining problem is INSERTs. Is there a way to avoid having to provide user_id on INSERTs?

row-level-security postgresql
1 Answers

Just having a look at the manual :

Existing table rows are checked against the expression specified in USING, while new rows that would be created via INSERT or UPDATE are checked against the expression specified in WITH CHECK

it seems that you just have to add a WITH CHECK clause to your policy in addition of the USING clause, and which will apply to the INSERT and UPDATE statements.

user_80600170
Related