Enforcing row-level security in a shared postgres pool

Question

I'm working on a typical client-server webapp. It is using a system somewhat like GraphQL where the client has some flexibility in specifying what data it needs, without custom API endpoints for every type of data. The server is running node, and is using node-postgres with a typical pg.Pool. A client could send something like this:

{select: '*', from: 'expenses', where: {'op': 'gt', 'lhs': 'expenses.amount', 'rhs': 20}}

which would be translated to SELECT * FROM expenses WHERE expenses.amount > $1 (given $1 = 20). With enough care, this system can be made safe from injection attacks.

I'd also like to incorporate row-level security policies. For example:

create policy only_see_own_expenses on expenses using (expenses.user_id = <USER ID>);

As an extra security barrier, I want to make sure that even if an injection attack is succesful, a client can not "unset" its user ID.

I've seen <USER ID> been defined in a few ways:

1. current_user, in which case every user of the app also needs a postgres user/role
2. An arbitrary setting like current_setting('myapp.user_id') in combination with a SET LOCAL myapp.user_id = ... at the start of a transaction

Approach (2) seems most flexible to me. I'd just wrap every generated SQL query in a BEGIN; SET LOCAL myapp.user_id = 123; {generated query}; END;. The problem is that an attacker could inject another SET LOCAL statement, and impersonate another user.

In approach (1) you can similarly wrap every generated query with a SET ROLE ... statement at the start, yielding the same problem. An alternative is to create a new connection for each query with that specific role. I believe postgres would never allow that connection to switch to another role. But setting up a new connection per query would result in a lot of overhead.

How do I enforce row-level security without the performance hit of a new connection per query?

Answer 1

As you observe, setting a placeholder parameter and using SET LOCAL ROLE to temporarily assume a different role can be subverted by attackers who can execute arbitrary SQL, like in an SQL injection attack.

I don't think that there is a way to accomplish what you want that is safe from SQL injection. The problem is a fundamental one: you handle authentication in the application, not in the database (where you are using a single application user to benefit from connection pooling), but you want to have the database handle authorization via row-level security. That requires a way for the application to tell the database what the application user is. Now the only way that the application can tell the database anything is by using SQL, and an attacker who can run arbitrary SQL statements can always subvert that.

I think that the only option for you is to harden your application against SQL injection attacks.