In the SaaS world, providers must offer tenant isolations for their customers and their data. This is a key requirement when offering services at scale. At Kong, we've invested a lot of time to provide a scalable and seamless approach for developers to avoid introducing breaches in our systems.

In this article, we'll explore the challenges of tenant scoping and how we address them effectively.

What cards do we have in our hand?

Separated databases

This approach provides a pure data isolation as the data is separated from the rest of the stack. This is how you can reach the maximum isolation of the data.

However, resource requirements and operational costs are high, which can trickle down to the product itself.

Shared database, separate schema

Putting all tenants in the same database but in separate schemas reduces the resource requirements and is easier to manage at a scale from an operational point of view. However, regarding the application side, it still creates operational costs. This means, for example, a data migration will still have to run on all schemas — same problem as separate databases.

Shared database, shared schema

If all the tenants use the same database and schema it means that the data of all tenants are all together. This can scale as much as we want based on the application consumption. 

However, we have a data isolation issue here. Because all tenants are mixed together in the same datastore, we need a security layer to ensure that applications can't leak organizations' data.

One possibility would be to have a “trust the programmer” approach, where we rely on the engineering team's diligence to write and properly review the code. But we all are humans and make mistakes. 

To address this security issue, there's the concept of row-level security, which puts us in between shared database, shared schema and shared database, separate schema.

Row-level security

Row-level security (or RLS) defines the practice of applying security checks in a datastore per row, meaning that the actor of the query will only process the data they’re supposed to have access to. This is more fine-grained than database-level security or schema-level security that the two previously mentioned approaches would apply.

In this article, we'll dive into the PostgreSQL implementation of this feature using Row Security Policies. 

User management

Depending on how you manage your user, you might choose different approaches for user management/permission. Here we're going to take the simple approach of having one admin user and one RLS user. 

We need to alter the role of myapp_admin to be sure it can bypass row-level security policies for some specific operations like data migrations.

Schema design

To achieve tenant isolation, some design considerations are needed when creating the tables. We need to add a column for the tenant identifier, which we'll call “tenant_id”. Depending on the database design, it can either be a NOT NULL column or a foreign key to the tenant table in the database. In Konnect we're using a microservice architecture, which means that the tenant table is only in our identity service; this leads us to use a NOT NULL column without a foreign key in the design.

Example:

Table RLS policies

Now that the table has been properly created, we need to create the policies. This includes the functions to be applied by the policies and the policies themselves.

We'll first define the functions we need, set get and unset.

Set_config ref: https://pgpedia.info/s/set_config.html

In the function, you can see we're using the “set_config” API from PostgreSQL which allows us to store data in the context of the session or the transaction. In the current implementation, we're storing it at the SESSION level and not the transaction. This is because we ensure via the connection pooling that we're scoping the session per tenant.

After bootstrapping the functions that the policies will use, we have to apply a policy to it to ensure the tenant isolation. The policy would look like so:

In this SQL statement, we specify that we want every statement to use tenant_id = get_tenant(). This results in a contextual CHECK constraint on the SQL statement. That means that every SQL query will have this constraint happen in it once the policy is enabled. This means that regardless of the query state, this will always happen. 

For example: Select * from APIs where name = “bar”; will end up being processed as Select * from APIs where name = “bar” and tenant_id = get_tenant();

By extension, a statement like this Select * from apis where tenant_id = “01b0616c-4ad2-4aa1-be81-dfd34d194d8f”; will end up Select * from apis where tenant_id = “01b0616c-4ad2-4aa1-be81-dfd34d194d8f” and tenant_id = get_tenant(); leading to no result if the two IDs are different.

The same applies on any SQL query like INSERT, UPDATE etc. In the case of those, if the constraint isn't met by having a tenant_id different from the result of get_tenant(); the query will error out.

Then we enable it:

On another note, we would prefer to set the tenant_id directly using the function rather than using the application layer to set it. Like this:

Concept of the flow

Transitioning to row-level security policies

This can be achieved smoothly if you have strong end-to-end test suites. At Kong we heavily rely on end-to-end tests and our test spins a new tenant for every run. With this approach, we're sure that when we switch a non-RLS user to an RLS user if there's an issue in a query / code it will be spotted in our continuous integration.

Implementations

At Kong, we use Golang and TypeScript as primary languages for our microservices. We developed for both of these languages tooling to ensure the RLS adoption is smooth and secure. Let's dive into the Golang approach for this post.

In the PSQL functions we're setting the config per sessions and not per transaction. This is because both languages rely on connection pooling; which provides us a hook to set and unset the tenant when acquiring the connection.

NodeJs implementation uses async local storage to store the data in the request lifecycle; Golang uses the standard library context. Both implementations use shared internal libraries that developers would import and use to have a homogeneous way of managing identities. 

Golang Authentication middleware example:

Golang

The Golang standard library doesn't offer the capabilities to wire hooks before getting the connection and after releasing the connection; that's why we need to use a third-party library. At Kong, we're using PGXPOOL from pgx. Pgx pool also provides a wrapper to make it compatible with std lib by exporting *sql.DB: https://github.com/jackc/pgx/blob/master/stdlib/sql.go This is useful when you're using an ORM which is only compatible with *sql.DB.

Below is the example of how a pgxpool would be configured regarding the RLS part:

Documentation: https://pkg.go.dev/github.com/jackc/pgx/v5/pgxpool#Config

When you read the line returning "true” when no auth in the context it seems like a code smell, but we're talking about two different things in that case. When the auth is not set, there won't be an invocation of “set_tenant” psql function, meaning it won't return any data from a table that has a security policy enabled. We want to always return true in our case otherwise the connection won't be acquired but it should be from the pool point of view.

Then you can use the pgxpool to execute queries seamlessly.

Conclusion

Row-level security might initially seem challenging to implement, but it's essential when using a tenant-shared schema.

Without RLS you can't ever be sure that the tenant has been properly isolated and put all your trust in the code and in your test suites. With RLS you have the insurance that database queries are properly tenant scoped.

At Kong, we’ve built our internal tooling around the RLS approach, meaning that by default a query is tenant scoped. Get familiar with the Row Security Policies concepts and secure your tenant data.