Row level security with a single DB user and connection pooling

9 votes

2 answers

4366 views

                          I'm using [node-postgres](https://github.com/brianc/node-postgres)  to connect to a PostgreSQL 9.6 database with connection pooling enabled. All connections user the same database user. One reason I can't use multiple DB users is that as far as I read, row level security and views don't work well together in that case as the owner of the view is used for RLS.

I'm now looking at using row level security in Postgres, and I'd like to make sure I'm doing this correctly. 

I'm using SET LOCAL to set the current application user id which is then used by the row level security USING clause. The only way I can think of achieving this with connection pooling is to wrap every query in a transaction with node-postgres and execute the SET LOCAL command like the following in every transaction.

    SET LOCAL postgres.my_user = 20;


The following code is a simplified example of how I want to define the row level security, the real version has a few more conditions:

    CREATE POLICY table_a_read_policy ON table_a FOR SELECT
    USING (
    	EXISTS (
    	SELECT * 
    	FROM permissions 
    	WHERE 
    		user_id = current_setting('postgres.my_user')::int AND 
    		permission_type = 'read'
    	)
    );


My understanding is that any SQL injection in this case would also give the attacker the ability to just set any user id and circumvent row level security this way. But I don't see any way to avoid this as the db user I connect with must have the ability to see all data for the entire application.

There are a few points I'm not entirely sure about:

- Is using SET LOCAL as a way to store the current application user identity for row level security safe? Especially in the context of connection pooling?
- Is there a better way than wrapping every query in a transaction in node-postgres and executing the SET LOCAL command every single time?
- Is there a way to structure this (with the restriction that the database connection is made using a single database user) in a way where an SQL injection would not automatically give the ability to circumvent row level security?
                        

Asked by Mad Scientist (1260 rep)

Jan 31, 2017, 11:39 AM
Last activity: Dec 5, 2024, 02:58 PM

Row level security with a single DB user and connection pooling

Related Questions