PostgreSQL row-level security for multi-tenant data isolation

<p>I am building a multi-tenant SaaS where each user should only see their own data. I want PostgreSQL row-level security (RLS) policies to enforce data isolation at the database level.</p>

<p>PostgreSQL RLS for multi-tenant isolation:</p> <div class="highlight"><pre><span></span><code><span class="c1">-- Enable RLS on table:</span> <span class="k">ALTER</span><span class="w"> </span><span class="k">TABLE</span><span class="w"> </span><span class="n">traces</span><span class="w"> </span><span class="n">ENABLE</span><span class="w"> </span><span class="k">ROW</span><span class="w"> </span><span class="k">LEVEL</span><span class="w"> </span><span class="k">SECURITY</span><span class="p">;</span> <span class="c1">-- Policy: users can only see their own traces</span> <span class="k">CREATE</span><span class="w"> </span><span class="n">POLICY</span><span class="w"> </span><span class="n">traces_user_isolation</span><span class="w"> </span><span class="k">ON</span><span class="w"> </span><span class="n">traces</span> <span class="w"> </span><span class="k">USING</span><span class="w"> </span><span class="p">(</span><span class="n">contributor_id</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">current_setting</span><span class="p">(</span><span class="s1">'app.current_user_id'</span><span class="p">)::</span><span class="n">uuid</span><span class="p">);</span> <span class="c1">-- Policy: allow all reads (seed traces visible to all):</span> <span class="k">CREATE</span><span class="w"> </span><span class="n">POLICY</span><span class="w"> </span><span class="n">traces_read</span><span class="w"> </span><span class="k">ON</span><span class="w"> </span><span class="n">traces</span> <span class="w"> </span><span class="k">FOR</span><span class="w"> </span><span class="k">SELECT</span> <span class="w"> </span><span class="k">USING</span><span class="w"> </span><span class="p">(</span> <span class="w"> </span><span class="n">contributor_id</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">current_setting</span><span class="p">(</span><span class="s1">'app.current_user_id'</span><span class="p">)::</span><span class="n">uuid</span> <span class="w"> </span><span class="k">OR</span><span class="w"> </span><span class="n">is_seed</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="k">TRUE</span> <span class="w"> </span><span class="k">OR</span><span class="w"> </span><span class="n">status</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="s1">'validated'</span><span class="w"> </span><span class="c1">-- Validated traces are public</span> <span class="w"> </span><span class="p">);</span> <span class="c1">-- Policy: only owner can update/delete:</span> <span class="k">CREATE</span><span class="w"> </span><span class="n">POLICY</span><span class="w"> </span><span class="n">traces_write</span><span class="w"> </span><span class="k">ON</span><span class="w"> </span><span class="n">traces</span> <span class="w"> </span><span class="k">FOR</span><span class="w"> </span><span class="k">UPDATE</span><span class="w"> </span><span class="k">USING</span><span class="w"> </span><span class="p">(</span><span class="n">contributor_id</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">current_setting</span><span class="p">(</span><span class="s1">'app.current_user_id'</span><span class="p">)::</span><span class="n">uuid</span><span class="p">);</span> <span class="k">CREATE</span><span class="w"> </span><span class="n">POLICY</span><span class="w"> </span><span class="n">traces_delete</span><span class="w"> </span><span class="k">ON</span><span class="w"> </span><span class="n">traces</span> <span class="w"> </span><span class="k">FOR</span><span class="w"> </span><span class="k">DELETE</span><span class="w"> </span><span class="k">USING</span><span class="w"> </span><span class="p">(</span><span class="n">contributor_id</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">current_setting</span><span class="p">(</span><span class="s1">'app.current_user_id'</span><span class="p">)::</span><span class="n">uuid</span><span class="p">);</span> <span class="c1">-- Admin bypass (superuser or designated admin role):</span> <span class="k">CREATE</span><span class="w"> </span><span class="k">ROLE</span><span class="w"> </span><span class="n">app_admin</span><span class="p">;</span> <span class="k">ALTER</span><span class="w"> </span><span class="k">TABLE</span><span class="w"> </span><span class="n">traces</span><span class="w"> </span><span class="k">FORCE</span><span class="w"> </span><span class="k">ROW</span><span class="w"> </span><span class="k">LEVEL</span><span class="w"> </span><span class="k">SECURITY</span><span class="p">;</span> <span class="k">GRANT</span><span class="w"> </span><span class="k">ALL</span><span class="w"> </span><span class="k">ON</span><span class="w"> </span><span class="n">traces</span><span class="w"> </span><span class="k">TO</span><span class="w"> </span><span class="n">app_admin</span><span class="p">;</span> <span class="c1">-- Note: BYPASSRLS privilege for admin users</span> </code></pre></div> <p>In SQLAlchemy -- set the user context per session:</p> <div class="highlight"><pre><span></span><code><span class="k">async</span> <span class="k">def</span><span class="w"> </span><span class="nf">get_db_with_rls</span><span class="p">(</span><span class="n">current_user</span><span class="p">:</span> <span class="n">CurrentUser</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="n">AsyncSession</span><span class="p">:</span> <span class="k">async</span> <span class="k">with</span> <span class="n">async_session_factory</span><span class="p">()</span> <span class="k">as</span> <span class="n">session</span><span class="p">:</span> <span class="c1"># Set per-session context variable:</span> <span class="k">await</span> <span class="n">session</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span> <span class="n">text</span><span class="p">(</span><span class="s2">"SELECT set_config('app.current_user_id', :uid, true)"</span><span class="p">),</span> <span class="p">{</span><span class="s1">'uid'</span><span class="p">:</span> <span class="nb">str</span><span class="p">(</span><span class="n">current_user</span><span class="o">.</span><span class="n">id</span><span class="p">)},</span> <span class="p">)</span> <span class="k">async</span> <span class="k">with</span> <span class="n">session</span><span class="o">.</span><span class="n">begin</span><span class="p">():</span> <span class="k">yield</span> <span class="n">session</span> </code></pre></div> <p>Key points: - RLS policies are enforced at the DB level -- application bugs cannot bypass - current_setting() reads session-level configuration variables - FORCE ROW LEVEL SECURITY applies to table owner (superuser) too - Always test RLS with the application's database role (not superuser) - Performance: RLS adds WHERE clause to every query -- index the filtered column</p>