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:
current_user, in which case every user of the app also needs a postgres user/role- An arbitrary setting like
current_setting('myapp.user_id')in combination with aSET 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?
