Row Level Security for Tenants in Postgres

Row-level security (RLS) in Postgres is a feature that allows you to control which rows a user is allowed to access in a particular table. It enables you to define security policies at the row level based on certain conditions, such as user roles or specific attributes in the data. Most commonly this is used to limit access based on the database user connecting, but it can also be handy to ensure data safety for multi-tenant applications.

Creating tables with row level security

We're going to assume our tenants in this case are part of an organization, and we have an events table with events that always correspond to an organization:

CREATE TABLE organization (
    org_id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    name VARCHAR(255) UNIQUE,
    created_at TIMESTAMPTZ default now(),
    deleted_at TIMESTAMPTZ default now(),
);

CREATE TABLE events (
  org_id UUID,
  event_type TEXT,
  event_value INT,
  occurred_at TIMESTAMPTZ default now(),
);

We're going to turn on RLS on our events table.

ALTER TABLE events ENABLE ROW LEVEL SECURITY;

And then set a policy that is enforced based on the connected database user.

CREATE POLICY event_isolation_policy
  ON events
  USING (org_id::TEXT = current_user);

Now if you had a org id of c4062d63-335e-4631-b03c-504d0eb88122 and created a database user with that login, when they connected to the database and queried the events table they'd only receive their events.

Using Session Variables

The above works great when you're giving out raw database access. But creating a new database user and connecting with that unique user for each new request that comes in is a lot of overhead and takes away many of the tools that exist for managing and scaling Postgres connections.

Instead of a user per customer, what we're going to do is set a session variable when we connect.

CREATE POLICY event_session_user
  ON events
  TO application
  USING (org_id = NULLIF(current_setting('rls.org_id', TRUE), '')::uuid);

Now when you connect in your session you'll set the value, and then can query the events table:

SET rls.org_id = 'c4062d63-335e-4631-b03c-504d0eb88122';
SELECT * FROM events;

For example, in a web application, you might set the app.current_org_id variable in the application component based on the current user's authentication. Use a function to get current org_id from the request and then pass in the current org in the connection.

@app.before_request
def before_request():
    org_id = get_current_org_id(request)
    g.db = psycopg2.connect(database="mydb")
    with g.db.cursor() as cur:
        cur.execute("SET app.current_org_id = %s", (org_id,))

With this setup, any queries executed within the request will automatically be filtered based on the current tenant ID, ensuring that each tenant can only access their own data.

Keep in mind that when designing your app for multi-tenancy, ideally you have that ord_id in every table.

Conclusion

Postgres Row-Level Security provides a powerful mechanism for securing multi-tenant applications, allowing you to enforce data isolation and privacy at the row level. By defining policies based on tenant IDs, org IDs, or other criteria, you can ensure that each tenant can only access their own data, enhancing the overall security of your application.

Check out our browser based tutorial for learning more about Row Level Security and session variables.