Underrated Postgres: Build Multi-Tenancy with Row-Level Security

Building multi-tenant applications requires developers to ensure correct queries at every step along the way. Typically, we’d fall back on using WHERE clauses and pass along necessary constraints. Having to redo the same pattern over and over is error-prone, and even a single missed check can leak data to other users.

Multi-Tenancy and Access Control

When building SaaS or any multi-tenant application, you always face the same nightmare: ensuring the right tenant only sees their own data. Usually, the solution is sprinkling WHERE customer_id = ? all over your queries like parmesan on pasta. Forget it once? Congrats, you just leaked another customer’s invoices.

This approach is brittle, repetitive, and frankly beneath us as professionals. PostgreSQL has a better way: Row-Level Security (RLS). With RLS, Postgres itself enforces those row filters. Think of it as having a paranoid DBA strapped to your queries, yelling: “No, Alice, you’re not allowed to peek at Bob’s data!”

However, only few developers are aware of row-level security (or RLS for short). A big mistake!

What is Row-Level Security?

Row-Level Security (RLS) is Postgres’ built-in mechanism for restricting which rows a given role can SELECT, INSERT, UPDATE, or DELETE.

• Regular table grants decide what commands you can run (GRANT SELECT).
• RLS policies decide which rows you can run them on.
• CLS (Column-Level Security, yes, it’s a thing) policies decide which columns you can see.

If you enable RLS on a table without defining any policies, nobody except superusers will be able to access the table. They are deny-by-default. Nobody sees anything. Not even Gandalf. Be careful!

Policies are written as SQL expressions:

• USING defines which rows are visible for SELECT, UPDATE, DELETE.
• WITH CHECK defines which rows may be written on INSERT or UPDATE.

Multiple policies can be combined. More on this later.

Invoicing Database Tables

Let’s start easy for now and build the application. By the way, the whole source code is available on GitHub.

For demonstration, we’ll build a small service. To keep it simple, I’ll be focusing on the invoicing part of an online store. By definition, an online shop must be multi-tenant if we assume a customer to be a tenant. I know, I know. Not a perfect association, but bear with me.

Our world’s tiniest invoicing service will need three basic tables:

• Customers (our tenants)
• Invoices (belonging to customers)
• Invoice positions (invoice line items)

Customers

That said, let’s quickly define a few basic database tables. First of all, the most important one. We need our “tenant,” the customer. The customer table will be most important for our row-level security approach.

CREATE TABLE customers (
  customer_id   UUID NOT NULL
    PRIMARY KEY,
  customer_name TEXT NOT NULL
);

Invoices

Now that we have our “tenants,” we need the actual tables for invoicing. Again, for ease of understanding, we go with one of the most simplistic implementations possible. We will only create two tables, one for the invoices themselves and another for the positions on the invoice.

CREATE TABLE invoices (
  invoice_id   UUID NOT NULL
    PRIMARY KEY,
  customer_id  UUID NOT NULL
    REFERENCES customers,
  invoice_date TIMESTAMPTZ NOT NULL
);

CREATE INDEX invoices_customer_id_index
  ON invoices (customer_id);

CREATE INDEX invoices_invoice_date_index
  ON invoices (invoice_date DESC);

CREATE TABLE invoice_positions (
  invoice_position_id UUID NOT NULL,
  invoice_id          UUID NOT NULL
    REFERENCES invoices,
  customer_id         UUID
    REFERENCES customers,
  name                TEXT NOT NULL,
  description         TEXT,
  amount              INTEGER NOT NULL ,
  price               REAL NOT NULL,
  position            INTEGER NOT NULL,
  PRIMARY KEY (invoice_position_id, invoice_id)
);

As we can see, invoices and invoice_positions reference customers so that we can enforce tenant boundaries through policies.

Invoicing Service Architecture

For building the service itself, I’ll go with Go(lang), pgx for the Postgres connection (due to its enhanced support for additional data types, such as UUID), and goyave for the simplified implementation of the REST API URL mappings.

The service itself will be designed with a single set of URLs for customers, account managers, and administrators. While, for a real-world implementation, it would make sense to add some additional layer of access control, for the sake of the demo, we’ll only handle permissions in the database using row-level access control.

This may be controversial but elegant: fewer moving parts, fewer bugs.

I’ll keep it brief here. As mentioned, you’ll find the complete source code on GitHub.

Router Mapping

As mentioned before, we’ll only be using one set of URLs for all users. So far, we’ve only defined customers, but others will be added as we go.

func createRoutes(pool *pgxpool.Pool) func(
  server *goyave.Server, router *goyave.Router
) {

  return func(server *goyave.Server, router *goyave.Router) {
    router = router.Middleware(
      tenantized.NewTenantAwareMiddleware(pool)
    )

    router.Get("/customers", listCustomers)
    router.Post("/customers", createCustomer)

    subrouter := router.Subrouter("/customers/{customerId}")
    subrouter.Get("/", showCustomer)
    subrouter.Get("/invoices", listInvoices)
    subrouter.Post("/invoices", createInvoice)
    subrouter.Get("/invoices/{invoiceId}", showInvoice)
  }
}

Implementing Tenant-Awareness via Middleware

Middleware in Go HTTP frameworks typically handles aspects shared across multiple URL mappings. That sounds like the perfect solution for our tenant-awareness implementation. So, let’s see how we can use it.

func (m *TenantAwareMiddleware) Handle(
  next goyave.Handler // Next request handler or middleware
) goyave.Handler {

  return func(response *goyave.Response, request *goyave.Request) {
    ctx := request.Context()

    tx, err := m.pool.Begin(ctx)
    if err != nil {
      response.Error(
        fmt.Sprintf("could not start transaction: %s", err.Error())
      )
      return
    }

    // Inject transaction into request context
    ctx = transactional.WithTransaction(ctx, tx)
    request = request.WithContext(ctx)

    // Apply user role and tenant to transaction
    if !applyRowLevelSecurity(tx, ctx, response, request) {
      return
    }

    // Run the actual request handler
    next(response, request)

    // Finish up the transaction depending on the status code
    status := response.GetStatus()
    if status >= 200 && status < 300 {
      if err := tx.Commit(context.Background()); err != nil {
         log.Println("commit failed:", err)
      }
        } else {
          if err := tx.Rollback(context.Background()); err != nil {
            log.Println("rollback failed:", err)
          }
       }
   }
}

What we can see, every request immediately starts a database transaction and stores it within the request context.

Afterwards, some “magic” happens in applyRowLevelSecurity (that we’ll look at in a few seconds), then runs the actual request handler (next), before coming back. Eventually, depending on the status code, we’ll either commit or rollback the transaction. Fully automatically.

Applying Row-Level Security

Now we need to get the real magic implemented. For this, we use two levels of access permissions. Here, we already have the code for customers, account managers, and administrators. We’ll be adding other types of users as we go. In this case we already prepare those two additional user roles. Our code can stay the same, and all later changes are happening in the database only.

func applyRowLevelSecurity(
  tx pgx.Tx, ctx context.Context,
  response *goyave.Response, request *goyave.Request,
) bool {

  userRole := request.Header().Get("X-User-Role")
  if userRole != "administrator" &&
    !accountManagerRole.MatchString(userRole) {
      userRole = "customer"
  }

  // Since we made sure the role is valid, we can safely use it
  _, err := tx.Exec(ctx, fmt.Sprintf("SET ROLE %s", userRole))
  if err != nil {
    response.Error("failed to set tenant")
    return false
  }

  if userRole == "customer" {
    // Apply tenant since we assume a customer transaction
    if !applyTenantPermission(tx, ctx, response, request) {
      return false
    }
  }

  return true
}

The above code looks at a potentially existing header X-User-Role to determine if the user is an administrator or account manager. If not available, it must be a customer.

As mentioned before, this should be handled using an actual authorization system and a JWT token.

If the user is a customer, we apply a second level of access control with the following code.

func applyTenantPermission(
  tx pgx.Tx, ctx context.Context,
  response *goyave.Response, request *goyave.Request,
) bool {

  // Extract tenant ID from request
  // In the real world, this would want to be a JWT token
  tenant := request.Header().Get("X-Customer-ID")
  if tenant == "" {
    response.Status(http.StatusBadRequest)
    response.Error("missing X-Customer-ID header")
    return false
  }

  tenantId, err := uuid.Parse(tenant)
  if err != nil {
    response.Status(http.StatusBadRequest)
    response.Error("invalid X-Customer-ID")
    return false
  }

  // Since we made sure the UUID is valid, we can safely use it
  _, err = tx.Exec(ctx, fmt.Sprintf(
    "SET LOCAL app.current_customer_id = '%s'", tenantId.String(),
  ))
  if err != nil {
    response.Error("failed to set tenant")
    return false
  }
  return true
}

This code fragment looks at a second header X-Customer-ID to find the customer id of the requester. Again, please use JWT tokens in the real world.

RLS Transaction Setup

Both access control levels set specific elements as part of the transaction setup. In applyRowLevelSecurity we set the user’s role, which is either a customer, an account manager, or an administrator. For account managers, we actually use their user role directly (after checking it).

In applyTenantPermission we set the customer id into the transaction if the user is a customer.

We’ll see the details on how this will work in a few moments. However, there is one more thing to look at first.

Retrieving Customers

When retrieving a customer or a customer’s invoices, we don’t have to remember to add where clauses to the query. We can leave the query as simple as it gets.

For simplicity of the blog post, here is one example of how the customer list is retrieved. All other implementations will look similar, not caring one bit about which customers are visible or not. It’ll all be handled by the database itself.

func listCustomers(
  response *goyave.Response, request *goyave.Request
) {

  ctx := request.Context()
  // Retrieve active transaction from request context
  tx := transactional.FromContext(ctx)

  rows, err := tx.Query(ctx,
    `SELECT customer_id, customer_name FROM customers`,
  )
  if err != nil {
    response.Error(fmt.Sprintf("db query error: %s", err.Error()))
    return
  }
  defer rows.Close()

  customers := make([]Customer, 0)
  for rows.Next() {
    var customer Customer
    if err := rows.Scan(&customer.Id, &customer.Name); err != nil {
      response.Error(fmt.Sprintf("db scan error: %s", err.Error()))
    }
    customers = append(customers, customer)
  }

  response.JSON(http.StatusOK, customers)
}

As we can see, we retrieve the existing transaction from the request context and run the query. All the rest is basic goyave and pgx stuff to read the database data and return the result to the user.

Get Some Test Data

Before we go to the actual RLS implementation, let’s quickly add some demo data. In this case, we’ll create two companies you might have heard about before, and one invoice. See for yourself.

INSERT INTO customers (customer_id, customer_name) VALUES
(gen_random_uuid(), 'Pied Piper'),
(gen_random_uuid(), 'Hooli');

INSERT INTO invoices (invoice_id, customer_id, invoice_date)
SELECT gen_random_uuid(), customer_id, now()
FROM customers
WHERE customer_name = 'Pied Piper';

INSERT INTO invoice_positions
SELECT
  gen_random_uuid(),
  invoice_id,
  customer_id,
  'The Box - Signature Edition',
  'A Hooli box, personally signed by our CEO Gavin Belson',
  10,
  999999.99
FROM invoices
LIMIT 1;

Magic Tenant-Awareness (Row-Level Security)

Alright, looks like we’re ready to do the crazy stuff.

Enabling Row-Level Security

To make row-level security a thing, we need a few steps. We’ve seen above that we’ll have three user roles that need to be created. So let’s do this first.

CREATE ROLE customer NOBYPASSRLS;

GRANT SELECT ON customers TO customer;
GRANT SELECT ON invoices TO customer;
GRANT SELECT ON invoice_positions TO customer;

Afterwards, we can enable row-level security on the tables.

Important: Remember that you need to have superuser permissions from this point onwards. If RLS is enabled but your user cannot bypass it, you’ll be stuck.

ALTER TABLE customers ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoice_positions ENABLE ROW LEVEL SECURITY;

Done. RLS is enabled. Easy eh?!

Access Policies for Customers

Now that we have enabled RLS, we need to ensure that customers can see their own records. To do this, row-level security uses policies as explained above.

For customers, the policies are easy enough. We kept it simple. Every table that a customer has to access contains a customer_id field. Our policy tests the app.current_customer_id value against that field’s value. If it matches, the row will be returned. We limit this policy to the customer role, though.

The policies themselves are only defined for select queries, meaning customers cannot add, update, or delete records. Not even their own.

CREATE POLICY customers_tenant_isolation
  ON customers
  FOR SELECT
  TO customer
  USING (customer_id::TEXT =
    CURRENT_SETTING('app.current_customer_id', FALSE));

CREATE POLICY invoices_tenant_isolation
  ON invoices
  FOR SELECT
  TO customer
  USING (customer_id::TEXT =
    CURRENT_SETTING('app.current_customer_id', FALSE));

CREATE POLICY invoice_positions_tenant_isolation
  ON invoice_positions
  FOR SELECT
  TO customer
  USING (customer_id::TEXT =
    CURRENT_SETTING('app.current_customer_id', FALSE));

Testing Customer Access

Ok, time to test our access control implementation for the first time. Let’s try it on the database level first.

In psql we can create the transaction, set our role, the customer id, and query the data, before rolling back the transaction.

invoicing=# BEGIN;
SET ROLE customer;
-- Set Hooli as customer
SET LOCAL app.current_customer_id = '510cc323-d818-494b-b1bb-48d3f8540d13';
SELECT * from customers;
ROLLBACK;
BEGIN
SET
SET
             customer_id              | customer_name
--------------------------------------+---------------
 510cc323-d818-494b-b1bb-48d3f8540d13 | Hooli
(1 row)

ROLLBACK

invoicing=# BEGIN;
SET ROLE customer;
-- Set Pied Piper as custoemr
SET LOCAL app.current_customer_id = 'bddff152-4107-42fd-9e5f-06becf0486c4';
SELECT * from customers;
ROLLBACK;
BEGIN
SET
SET
             customer_id              | customer_name
--------------------------------------+---------------
 bddff152-4107-42fd-9e5f-06becf0486c4 | Pied Piper
(1 row)

ROLLBACK
invoicing=#

As we can see, each customer only sees their own record. RLS helped us filter the customer records.

That said, the RLS policy automatically adds the additional where clause to the query. Not just figuratively, but in practice. Executing the query and asking for the query plan, we can see that Postgres added the filter to the actual query.

invoicing=# BEGIN;
SET ROLE customer;
-- Set Pied Piper as custoemr
SET LOCAL app.current_customer_id = 'bddff152-4107-42fd-9e5f-06becf0486c4';
EXPLAIN VERBOSE SELECT * from customers;
ROLLBACK;
BEGIN
SET
SET
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Seq Scan on public.customers  (cost=0.00..31.40 rows=5 width=48)
   Output: customer_id, customer_name
   Filter: ((customers.customer_id)::text = current_setting('app.current_customer_id'::text, false))
(3 rows)

ROLLBACK
invoicing=#

Testing the REST API

And since we already have our REST API, let’s quickly give it a test, too. Using CURL we can execute requests against it and pass along the required context.

Just as with the database query, we see that our filtering was applied automatically.

$ curl \
  -H 'X-Customer-ID: bddff152-4107-42fd-9e5f-06becf0486c4' \
  http://127.0.0.1:8080/customers
[{"id":"bddff152-4107-42fd-9e5f-06becf0486c4","name":"Pied Piper"}]

$ curl \
  -H 'X-Customer-ID: 510cc323-d818-494b-b1bb-48d3f8540d13' \
  http://127.0.0.1:8080/customers
[{"id":"510cc323-d818-494b-b1bb-48d3f8540d13","name":"Hooli"}]

Merging Access Policies

Now that our customers are working, it is time to go deeper. We already know that we also want to have account managers and administrators.

To enable them, we need more policies. Luckily, RLS supports multiple policies out of the box. However, with multiple policies, PostgreSQL then has to decide: how do these rules combine?

By default, Postgres behaves permissively. Multiple policies for the same command are merged with an OR. Meaning, if any policy allows a row, the row is visible.

Sometimes, however, an OR isn’t what you want. What if you want to apply additional filters that stack together? That’s where AS RESTRICTIVE comes in.

Restrictive policies are combined with AND instead of OR.

As an example, we could say that customers can only see active invoices:

-- Restrict customers to their own rows
CREATE POLICY customers_own
  ON invoices
  FOR SELECT
  TO customer
  USING (customer_id::TEXT =
    CURRENT_SETTING('app.current_customer_id', FALSE))
  AS RESTRICTIVE;

-- Further restrict to only active invoices
CREATE POLICY active_only
  ON invoices
  FOR SELECT
  TO customer
  USING (status = 'active')
  AS RESTRICTIVE;

Enabling Account Managers

Now let’s get back to work. We want account managers who are assigned to one or more customer accounts. Therefore, we’ll add two more tables to define account managers and assign their customers.

CREATE TABLE account_managers (
  account_manager_id UUID NOT NULL
    PRIMARY KEY,
  manager_name       TEXT NOT NULL,
  db_role            TEXT NOT NULL UNIQUE
);

CREATE TABLE customer_account_managers (
  customer_id        UUID NOT NULL
    REFERENCES customers(customer_id),
  account_manager_id UUID NOT NULL
    REFERENCES account_managers(account_manager_id),
  PRIMARY KEY (customer_id, account_manager_id)
);

That db_role field is important: it links real Postgres roles to logical managers, so we can SET ROLE and have policies filter correctly.

Let’s also add two account managers while we’re at it.

INSERT INTO account_managers (
  account_manager_id, manager_name, db_role
) VALUES (
  gen_random_uuid(), 'Account Manager 1', 'account_manager_1'
), (
  gen_random_uuid(), 'Account Manager 2', 'account_manager_2'
);

Creating Account Manager Permissions

First, we need the roles and general grants on the necessary tables. For the roles, it is vital to prevent them from bypassing RLS policies. 

CREATE ROLE account_manager_1 NOBYPASSRLS;
CREATE ROLE account_manager_2 NOBYPASSRLS;

GRANT SELECT
  ON customers
  TO account_manager_1, account_manager_2;
GRANT SELECT
  ON invoices
  TO account_manager_1, account_manager_2;
GRANT SELECT
  ON invoice_positions
  TO account_manager_1, account_manager_2;
GRANT SELECT
  ON account_managers
  TO account_manager_1, account_manager_2;
GRANT SELECT
  ON customer_account_managers
  TO account_manager_1, account_manager_2;

And the actual policies. For the sake of the blog post, here is just one of them. But like the others before, they’re pretty much identical, looking up if the requested customer (of the row) is inside the account manager’s portfolio. If not, it’s filtered out. Unfortunately, due to the difference of USING and WITH CHECK (for the different SQL operations), we need to duplicate the query.

CREATE POLICY manager_can_see_assigned_customers
ON customers
TO account_manager_1, account_manager_2
USING (
  customer_id IN (
    SELECT cam.customer_id
    FROM customer_account_managers cam
      JOIN account_managers am
      ON cam.account_manager_id = am.account_manager_id
    WHERE am.db_role = current_user
  )
)
WITH CHECK (
  customer_id IN (
    SELECT cam.customer_id
    FROM customer_account_managers cam
      JOIN account_managers am
      ON cam.account_manager_id = am.account_manager_id
    WHERE am.db_role = current_user
  )
);

Testing Account Managers

Ok then, let’s also give this a quick test. In the case of account managers, they all have their own user role. Hence, we only have to set the role and no customer id. Assigned customers are automatically applied by our policy.

invoicing=# BEGIN;
SET ROLE account_manager_1;
SELECT * from customers;
ROLLBACK;
BEGIN
SET
             customer_id              | customer_name
--------------------------------------+---------------
 510cc323-d818-494b-b1bb-48d3f8540d13 | Hooli
 bddff152-4107-42fd-9e5f-06becf0486c4 | Pied Piper
(2 rows)

ROLLBACK

invoicing=# BEGIN;
SET ROLE account_manager_2;
SELECT * from customers;
ROLLBACK;
BEGIN
SET
             customer_id              | customer_name
--------------------------------------+---------------
 510cc323-d818-494b-b1bb-48d3f8540d13 | Hooli
(1 row)

ROLLBACK
invoicing=#

And again, the same is true for our REST API.

$ curl -H 'X-User-Role: account_manager_1' \
  http://127.0.0.1:8080/customers
[{"id":"510cc323-d818-494b-b1bb-48d3f8540d13","name":"Hooli"},{"id":"bddff152-4107-42fd-9e5f-06becf0486c4","name":"Pied Piper"}]

$ curl -H 'X-User-Role: account_manager_2' \
  http://127.0.0.1:8080/customers
[{"id":"510cc323-d818-494b-b1bb-48d3f8540d13","name":"Hooli"}]

Application Administrators Can See Everyone

Last but not least, our service will have administrators. Those are different from database superusers. However, they have access to all customers. To implement that, we have two options.

First, we can just bypass RLS altogether for administrators. That is easy and practical, if there are no other tables that even administrators shouldn’t be able to see (like another application).

CREATE ROLE administrator BYPASSRLS;

Or we go the long route, which is very similar to before. We create the role, add the general grants (I left them out above, but they would also apply there).

CREATE ROLE administrator;

GRANT SELECT ON customers TO administrator;
GRANT SELECT ON invoices TO administrator;
GRANT SELECT ON invoice_positions TO administrator;
GRANT SELECT ON account_managers TO administrator;
GRANT SELECT ON customer_account_managers TO administrator;

And create the policies. Again, for simplicity, only one example of the policy. The other ones are similar, differing only in the policy name and the table they’re applied to.

CREATE POLICY admin_can_see_all_customers
  ON customers
  TO administrator
  USING (TRUE)
  WITH CHECK (TRUE);

The critical bit on this policy is that USING and WITH CHECK just “return” true, and basically, voids all checks.

Multi-Tenancy Doesn’t Have To Be Hard

Sounds too easy? Try it for yourself the full source code is available at GitHub. Feel free to play with it, and adjust it. See how row-level security can simplify your application code, moving the aspect of “visibility” to the database.

I strongly believe that moving the accessibility check into RLS policies can significantly improve the readability of your application codebase while enhancing security. No more “oops, missed the filtering.”

It’s also very extensible. If you want, you’re able to build a full-blown RBAC (role-based access control) system using a few tables and policies.

However, there is a lot of boilerplating that has to be done here. That’s why tools like Vela provide a more straightforward approach to RLS using an easy-to-use user interface and API.

Vela enables you to define role templates, easily apply them across different databases, and synchronize users. You want to learn more on how Vela can help you? Book your call now.

Either way, implementing multi-tenancy should use row-level security. In Postgres. Always.

Topics

Share blog post

Tags