PostgreSQL Row-Level Security for multi-tenancy

-- ======================================================================
-- Q: How do you apply PostgreSQL Row-Level Security for multi-tenancy?
--
-- It is a second line of defense: even if a filter is forgotten in the code, the
-- database does not return another tenant's data. Using set_config with true
-- (local) is important when you use a connection pool, so the tenant does not
-- "stick" to the connection.
-- ======================================================================

ALTER TABLE assets ENABLE ROW LEVEL SECURITY;
ALTER TABLE assets FORCE ROW LEVEL SECURITY;      -- applies to the owner too
CREATE POLICY tenant_isolation ON assets
    USING (tenant_id = current_setting('app.tenant_id')::int);

# In every transaction, from the backend:
session.execute(text("SELECT set_config('app.tenant_id', :t, true)"),
                {'t': str(user.tenant_id)})        # true = only for this transaction