Postgresql – Row level security with a single DB user and connection pooling

postgresqlrow-level-security

I'm using 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?

Best Answer

I have a similar setup on a new project.

The issue here is that you have a connection pool that uses a single login. The application that holds that connection pool will need to set the user correctly to get the required row level permissions. If your application is incorrectly configured / coded, then the permissions can be subverted.

It seems to me that row level permissions are only really useful when you have different users/roles with different logins reading from tables directly. In that case there is no where user = xxxx style clause that can be subverted. In this situation they make different connections.

Related Question