Postgresql Pool Compatibility

[selopia909]

Is it compatible to work with Postgresql Pool with row level security?

Ref: https://aws.amazon.com/blogs/database/multi-tenant-data-isolation-with-postgresql-row-level-security/

Thanks for your great work.

[goxiaoy]

Yes
You can use this feature with Alternative approach

If you don’t want to create and maintain PostgreSQL users for each of your tenants, you can still use a shared PostgreSQL login for your application. However, you need to define a runtime parameter to hold the current tenant context of your application. Make sure the login is not the table owner or defined with BYPASSRLS. This alternative, which is very scalable, looks similar to the following code:

CREATE POLICY tenant_isolation_policy ON tenant
USING (tenant_id = current_setting('app.current_tenant')::UUID);

checkout jackc/pgx#288 (comment)
you can resolve connection from pool

        config, err := pgxpool.ParseConfig(dsn)
	if err != nil {
		return pool, err
	}
	config.BeforeAcquire = func(ctx context.Context, conn *pgx.Conn) bool {
		// set the tenant id into this connection's setting
		tenantInfo, _ := saas.FromCurrentTenant(rCtx)
		_, err := conn.Exec(ctx, "SET app.current_tenant = '$1'", tenantInfo..GetId())
		if err != nil {
			panic(err) // or better to log the error, and then `return false` to destroy this connection instead of leaving it open.
		}
		return true
	}

	config.AfterRelease = func(conn *pgx.Conn) bool {
		// set the setting to be empty before this connection is released to pool
		_, err := conn.Exec(context.Background(), "SET app.current_tenant = '$1'", "")
		if err != nil {
			panic(err) // or better to log the error, and then`return false` to destroy this connection instead of leaving it open.
		}
		return true
	}