---
title: 'Row Level Security: Defense in Depth'
authors: ['jbrown']
date: 2025-11-20T12:00:00
tags: ['technical', 'rust', 'clickhouse', 'postgres', 'security']
summary: 'Improving data security using RLS in PostgreSQL and ClickHouse'
---
Svix is the enterprise-ready webhooks sending service. Using Svix, you can build a secure,
reliable, and scalable webhook platform in minutes. Looking to send webhooks securely?
[Give Svix a try!](https://www.svix.com)
When building SaaS products, there are two main approaches
for storing customer data: **single-tenant**, where a separate database (either physical or logical) is provisioned
for each customer, and **multi-tenant**, where multiple customers' data is co-mingled in a single database.
Essentially all high-growth applications rely on a multi-tenant architecture, because it enables rapid customer onboarding
and greatly simplifies backend development and operations; however, when using a multi-tenant architecture, it's absolutely
critical to enforce access controls throughout the backend application to ensure that customers can't access one
another's data. There are an endless number of techniques for doing so, and today we're going to discuss _row-level security_.
Recall that most databases[^most-databases] are structured as such:
- Tables[^tables] represent distinct kinds of records
- Columns[^columns] represent individual fields within a family of records
- Rows[^rows] represent individual records
Traditional SQL `GRANT` statements control the first two of these; they allow a
database user to access tables and their constituent columns. This is great for
separating concerns in your applications — for example, you can use
traditional table/column permissions to prevent your BI
infrastructure from accessing any field that might
contain customer PII
— but they won't help with the multi-tenancy problem.
_Row-Level Security Policies_ (a.k.a. "RLS") are a relatively modern database feature[^modern] which allows a database administrator
or developer to attach filters to a table which will be used at runtime to determine whether a row can be accessed. This is a perfect
fit for improving security of multi-tenant database-driven applications.
### PostgreSQL Example
Let's start out with PostgreSQL and imagine a fairly simple set of tables for recording financial transactions:
```sql
CREATE TABLE accounts(
id BIGSERIAL PRIMARY KEY,
name CHARACTER VARYING NOT NULL
);
CREATE TABLE wallets(
id BIGSERIAL PRIMARY KEY,
account_id BIGINT NOT NULL REFERENCES accounts(id),
balance_cents BIGINT NOT NULL
);
CREATE TABLE transactions(
id BIGSERIAL PRIMARY KEY,
source_wallet_id BIGINT NOT NULL REFERENCES wallets(id),
destination_wallet_id BIGINT NOT NULL REFERENCES wallets(id),
amount_cents BIGINT NOT NULL
);
```
This schema allows us to hold multiple customers' data in a single database, segmenting by the `account_id`. However, it's up to us
to make sure that every application query appropriately passes the `account_id` filter, and if anybody ever forgets, or if a
[vulnerability](https://en.wikipedia.org/wiki/SQL_injection) in our application allows a customer to generate arbitrary SQL, they
could potentially view another user's data.
We can improve this with row-level security policies! We'll start with the `accounts` table:
```sql
CREATE FUNCTION current_account() RETURNS bigint
AS $$
SELECT COALESCE(current_setting('current_request.account_id', true)::bigint,-1)
$$
LANGUAGE SQL;
CREATE POLICY accounts_by_id ON accounts
FOR ALL
TO PUBLIC
USING (accounts.id = current_account());
ALTER TABLE accounts ENABLE ROW LEVEL SECURITY, FORCE ROW LEVEL SECURITY;
```
- First, we define a function returning the account for the current request. We'll populate this by calling `SELECT set_config('current_request.account_id', ?, true)` at the beginning of each database transaction. If we ever fail to do this, no rows will be visible at all; the system will fail safe
- Next, we make a policy for the table. We define it for `ALL` actions (SELECT, UPDATE, etc), applying to everybody ("PUBLIC"), and with the filter `accounts.id = current_account()`, referencing the helper function we just wrote.
- Finally, we turn on row-level security for the table. By default, RLS won't apply to the (PostgreSQL) user who _owns_ the table, so it's important to pass `FORCE ROW LEVEL SECURITY` when testing if you're using the same user to create and query the table[^user].
After running this, we should be able to verify it:
```sql
SELECT set_config('current_request.account_id', '1', true);
SELECT * FROM accounts;
```
Voila, only row 1 is returned.
We can do something similar for wallets:
```sql
CREATE POLICY wallets_by_account_id ON wallets
FOR ALL
TO PUBLIC
USING (wallets.account_id = current_account());
ALTER TABLE wallets ENABLE ROW LEVEL SECURITY, FORCE ROW LEVEL SECURITY;
```
However, the `transactions` table is a bit trickier; we want any party to a financial transaction to be able to view it, but the account IDs aren't
on the table. In this case, we have two choices. For a smaller system, you can get away with simply querying the `wallets` table at runtime:
```sql
CREATE POLICY transactions_by_wallet ON transactions
FOR ALL
TO PUBLIC
USING (
EXISTS (
SELECT id
FROM wallets
WHERE account_id = current_account()
AND (id = source_wallet_id OR id = destination_wallet_id)
)
);
ALTER TABLE transactions ENABLE ROW LEVEL SECURITY, FORCE ROW LEVEL SECURITY;
```
This works, but it's expensive because now you're always joining against the wallets table, as this EXPLAIN plan shows:
```text
Seq Scan on transactions (cost=0.00..18665.42 rows=680 width=32)
Filter: (SubPlan 1)
SubPlan 1
-> Bitmap Heap Scan on wallets (cost=8.32..13.71 rows=1 width=0)
Recheck Cond: ((id = transactions.source_wallet_id) OR (id = transactions.destination_wallet_id))
Filter: ((account_id = COALESCE((current_setting('current_request.account_id'::text, true))::bigint, '-1'::bigint)) AND (account_id = COALESCE((current_s
etting('current_request.account_id'::text, true))::bigint, '-1'::bigint)))
-> BitmapOr (cost=8.32..8.32 rows=2 width=0)
-> Bitmap Index Scan on wallets_pkey (cost=0.00..4.16 rows=1 width=0)
Index Cond: (id = transactions.source_wallet_id)
-> Bitmap Index Scan on wallets_pkey (cost=0.00..4.16 rows=1 width=0)
Index Cond: (id = transactions.destination_wallet_id)
```
You can avoid this performance penalty by denormalizing the `source_account_id`
and `destination_account_id` onto the transactions table, at a cost of having
to make sure that you always atomically update all of the transactions if
a wallet ever changes hands. Obviously, whether or not this makes sense depends
on your application and your underlying data model.
In Rust, we use this by using a wrapper type
called `SecureTransaction`[^SecureTransaction]. We
implement Axum's [`FromRequestParts`](https://docs.rs/axum-core/0.3.4/axum_core/extract/trait.FromRequestParts.html) trait on
this `SecureTransaction` wrapper to automatically build it from our request authentication system, so there's never a point where
it can be attached to an invalid customer.
This might look something like the following:
```rust
use anyhow::Result;
use sea_orm::{DatabaseTransaction, DatabaseConnection, Statement, DatabaseBackend, ConnectionTrait, TransactionTrait};
/// A safe newtype wrapper representing an account in the system
pub struct AccountId(i32);
impl From for sea_orm::Value {
fn from(account_id: AccountId) -> Self {
sea_orm::Value::Int(Some(account_id.0))
}
}
impl AccountId {
fn get_from_request_session(extensions: &axum::http::Extensions) -> Result {
// this depends on your authentication system, but might involve look up in a session ID in a
// database or parsing a JWT
todo!();
}
}
/// Our wrapper around a connection that has an RLS context
pub struct SecureTransaction(DatabaseTransaction);
impl SecureTransaction {
pub async fn begin(conn: &DatabaseConnection, account_id: AccountId) -> Result {
let txn = conn.begin().await?;
let stmt = Statement::from_sql_and_values(
DatabaseBackend::Postgres,
"SELECT set_config('current_request.account_id', $1, true)",
[account_id.into()]
);
txn.execute(stmt).await?;
Ok(Self(txn));
}
}
struct AppState {
// This structure is where you keep all of your shared application state like your
// database connection pools; let's pretend you have a db_pool object that can
// give back a sea_orm::DatabaseConnection
}
impl axum::extract::FromRequestParts for SecureTransaction {
type Rejection = Error;
async fn from_request_parts(parts: &mut axum::http::request::Parts, state: &AppState) -> Result {
let account_id = AccountId::get_from_request_session(&parts.extensions).await?;
SecureTransaction::begin(&state.db_pool, account_id).await
}
}
```
An Axum handler function can then request a SecureTransaction as a parameter and through the magic of Axum's dependency
injection, one will be created:
```rust
use axum::{Json, Router, get};
use crate::state::AppState;
use crate::db::{SecureTransaction, models::Wallet};
use crate::api::ListTransactionsResponse;
async fn list_wallets(
db: SecureTransaction,
) -> Result> {
let wallets = Wallet::list(db).await?;
Ok(Json(ListTransactionsResponse::new(wallets)));
}
#[tokio::main]
async fn main() -> anyhow::Result<()> {
let state = AppState::new();
let app = Router::new()
.route("/wallet/", get(list_wallets))
.with_state(state);
let listener = tokio::net::TcpListener::bind("0.0.0.0:8000").await?;
axum::serve(listener, app).await?;
Ok(())
}
```
Note that it's important to attach this information to a database transaction (as opposed to a _session_) so that, if you're using
connection pooling tools such as [pgbouncer](https://www.pgbouncer.org/) or [proxysql](https://proxysql.com/), you don't accidentally
end up sharing a single connection between two different authentication contexts.
While using this type-system wrapper helps us not forget to use secure connections, I want
to emphasize that this system _fails secure_, and if we accidentally use a regular `DatabaseConnection` somewhere, the result is simply
that a feature doesn't work, but customer data is never exposed.
### ClickHouse Example
[ClickHouse](https://clickhouse.com/) is the other relational SQL database that we [use here at Svix](../getting-started-with-rust-and-clickhouse/), and it also [supports row-level security policies](https://clickhouse.com/docs/sql-reference/statements/create/row-policy).
These look a lot like RLS policies in PostgreSQL, but the syntax is slightly different. Let's start out by creating
the same tables as we did in PostgreSQL:
```sql
CREATE TABLE accounts(
id BIGINT,
name CHARACTER VARYING
) ENGINE = MergeTree
ORDER BY id;
CREATE TABLE wallets(
id BIGINT,
account_id BIGINT,
balance_cents BIGINT
) Engine = MergeTree
ORDER BY id;
CREATE TABLE transactions(
id BIGINT,
source_wallet_id BIGINT,
destination_wallet_id BIGINT,
amount_cents BIGINT,
) ENGINE = MergeTree
ORDER BY id;
```
As before, we can create a policy on the accounts table:
```sql
CREATE FUNCTION current_account AS () -> getSettingOrDefault('SQL_account_id', -1)::BIGINT;
CREATE ROW POLICY accounts_by_id
ON accounts
AS RESTRICTIVE
USING id = current_account()
TO ALL EXCEPT default;
```
A few notes here:
- Custom setting names in ClickHouse must start with the prefix `SQL_`, so we do so.
- The `default` user, much like the `postgres` role, is tricky to put limits on, so we exclude it entirely.
- ClickHouse settings can't be changed once `readonly` mode is 1, so a good approach is to, at the beginning of your request, set the variables you want (including sensitive ones like `SQL_account_id`), then flip `readonly` mode to 1.
- There's no need to enable RLS for a table with an `ALTER TABLE ... ENABLE ROW LEVEL SECURITY` statement; once a policy is in place, RLS is enforced.
Policies on associated tables have the same constraints as PostgreSQL. Unfortunately, we can't use quite the same
solution as in PostgreSQL (since you aren't allowed to have correlated sub-queries in a RLS policy in ClickHouse),
but we can emulate it:
```sql
CREATE ROW POLICY transactions_by_wallet
ON transactions
AS RESTRICTIVE
USING (
(source_wallet_id IN (SELECT id FROM wallets WHERE account_id = current_account()))
OR
(destination_wallet_id IN (SELECT id FROM wallets WHERE account_id = current_account()))
)
TO ALL EXCEPT default;
```
The same caveats about the cost of the `JOIN` versus the cost of updates apply here, with the extra detail
that ClickHouse doesn't even have an `UPDATE` statement, so any change would necessarily be a (very expensive)
mutation. If you're using ClickHouse, your data is probably immutable, so you don't have to worry as much
about maintaining referential integrity through data mutations!
RLS policies are just one element of a "defense in depth" approach to securing web applications, but they are
a powerful tool to keep in your toolbox. Stay tuned at [https://www.svix.com/blog](https://www.svix.com/blog) for
future posts about databases, security, and application development! Be
sure to follow us on [Github](https://github.com/svix) or [RSS](https://www.svix.com/blog/rss/) for
the latest updates for the [Svix webhook service](https://www.svix.com),
or join the discussion on [our community Slack](https://www.svix.com/slack/).
Are you excited to build reliable, secure, data-driven applications for server-to-server communication?
[Come work with us!](https://www.svix.com/careers/)
[^most-databases]: All of the relational/SQL databases, and many but not all of the non-relational databases. Also: spreadsheets!
[^tables]: Sometimes called ["Collections"](https://www.mongodb.com/docs/manual/reference/glossary/#std-term-collection) or ["Column Families"](https://www.scylladb.com/glossary/cassandra-column-family/)
[^columns]: Sometimes also called "fields"
[^rows]: Sometimes also called "documents"
[^modern]: [PostgreSQL](https://www.postgresql.org/) gained support for RLS policies in [version 9.5](https://www.postgresql.org/docs/9.5/release-9-5.html), released in 2016. [MySQL](https://www.mysql.com/) does not support RLS policies, but does support [CREATE VIEW...WITH CHECK OPTION](https://dev.mysql.com/doc/refman/5.7/en/view-check-option.html) since [version 5.0.2](https://downloads.mysql.com/docs/mysql-5.0-relnotes-en.pdf) in 2004, which can be used to implement RLS-like policies on top of a VIEW.
[^user]:
Note also that the default `postgres` role has the special permission "Bypass RLS". In general, you should not do any important
work as the `postgres` role.
[^SecureTransaction]:
In our production case, it's actually a little more complicated because this system lives alongside our
[typed database connections](../preventing-db-misuse-at-compile-time/)