Row Level Security: Defense in Depth

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¹ are structured as such:

• Tables² represent distinct kinds of records
• Columns³ represent individual fields within a family of records
• 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⁵ 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:

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 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:

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⁶.

After running this, we should be able to verify it:

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:

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:

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:

 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⁷. We implement Axum's FromRequestParts 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:

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<AccountId> 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<Self> {
        // 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<Self> {
        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<AppState> for SecureTransaction {
    type Rejection = Error;

    async fn from_request_parts(parts: &mut axum::http::request::Parts, state: &AppState) -> Result<Self> {
        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:

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<Json<ListWalletsResponse>> {
    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 or proxysql, 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 is the other relational SQL database that we use here at Svix, and it also supports row-level security policies. 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:

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:

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:

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 for future posts about databases, security, and application development! Be sure to follow us on Github or RSS for the latest updates for the Svix webhook service, or join the discussion on our community Slack.

Are you excited to build reliable, secure, data-driven applications for server-to-server communication? Come work with us!