Hacker News Reader

Comment by sgarman | Hacker News Reader

Am I right in my understanding that EVERY request that comes in to their api creates a new connection to the database? What about reusing connections with connection pools or one level up using pgbouncer or thing. Can you actually use RLS while reusing connections?

View on HN

It's possible to implement this without creating new connections to the database for each request by using SET LOCAL and wrapping every query in a transaction. Instead of applying RLS based on the current user, you apply RLS based on the parameter value you set at the beginning of the transaction. You can set this parameter value based on the user session in your application.

Your RLS policy looks as follows: CREATE POLICY tenant_${tableName}_isolation_policy ON "${tableName}" USING ("tenant_id" = current_setting('app.current_tenant');

Your queries look something like this: BEGIN TRANSACTION SET LOCAL app.current_tenant = '${tenant}'; SELECT * from some_table END TRANSACTION;

You can even initialize your writes with a `tenant_id` column defaulted to your `current_setting('app.current_tenant')`

Nope. Quoting the article itself:

"In the traditional use case of direct db access, RLS works by defining policies on tables that filter rows based on the current db user. For a SaaS application, however, defining a new db user for each app user is clunky. For an application use case you can dynamically set and retrieve users using Postgres’ current_settings() function ( i.e:

  SET app.current_app_user = ‘usr_123’

and

  SELECT current_settings(‘app.current_app_user)

)."

The policies that they define reference these settings, so they can do a "set" at the start of processing every web request, on a pre-existing db connection.

You can reuse the connection with a connection pool and use SET ROLE when you check it out.

Reply on news.ycombinator.com