Row-level security in Tinybird

Row-level security is a critical part of database-driven application development. It’s very common to have tables in your application databases that contain data from many different sources, and perhaps from different clients. If that's the case, you need to control which users can access specific rows. In this post, I’ll share how to achieve row-level security (or row level access control) in Tinybird.

As its name implies, row-level security lets you define which of your users can access which rows of a table. There are different ways to implement row-level security, but the most common approach is based on the value of one or more columns in the table.

For example, if you have a column called Customer ID, you could create a rule that says Customer A can only read rows where the Customer ID column has a value of ‘CustomerA’. Of course, for this to work, you need to know that Customer A generated the query in the first place. Where you get this information depends on your specific system, but in Tinybird, you’ll know who the user is based on their Authorization Token. More on this in a bit.

When do you need row-level security?

Let’s say you're a SaaS company that bills your customers based on their usage. Every user on your platform is interacting with the same system, generating a bunch of data about the resources they’ve used. You capture this data in your application and send it to a Tinybird Data Source using Kafka or maybe the Events API. Each row has a customer ID that describes who generated the usage data. You build a Pipe on this Data Source to aggregate usage by customer ID to work out how much resource the customer has consumed and generate an invoice. This is a pretty standard usage-based billing use case.

Now, if you’re only using this table for internal reporting, you would only need to limit table access to the necessary people in your company. You most likely wouldn’t need access control based on specific rows (customers).

But, what if you wanted to let your end users monitor their resource usage so they could see how their work was affecting their bill?

That is an external, user-facing analytics use case, and it has become table stakes for usage-based billing; your customers expect you to provide that level of observability, ideally in real time, so they can see up-to-date usage numbers.

When you expose this data to your users, however, you'll have some concerns. You obviously need an architecture that supports the scale, concurrency, and latency requirements, and you also need a security policy to restrict user access to the appropriate scope. Can you imagine using a product where other users could publicly view your usage of the platform, and vice versa? Nope. This is obvious, but your customers should not be able to access other customers’ usage data.

To achieve this, you’ll use row-level security.

Implementing row-level security in Tinybird

Tinybird controls access to Pipes and Data Sources with Authorization tokens. Whenever a user sends a request to a Tinybird API endpoint, the request must include an Authorization Token in the header. Before the request is served, Tinybird evaluates the permissions granted to that token. The most basic permissions are read and write, which are pretty self-explanatory.

But, you can also modify read permissions on a Tinybird token using a column expression. By adding a simple SQL filter statement to the read scope of the token, you can implement row-level security in Tinybird. It's kind of like adding a specific WHERE clause to every query initiated with that token.

There are two ways to approach row-level security in Tinybird. You can apply filters on the Data Source scope, or on the Pipe scope.

Adding filters on the Data Source scope

If you set up a token with filtering on the Data Source read scope, it will affect every Pipe for which that token has read permission.

For example, if you have created multiple Pipes that query a single underlying table, and you only want those Pipes to access rows where a column had a certain value, you'd set up the filter at the Data Source scope. This is useful when you have many APIs that expose data from the same Data Source and you want to impost the same security policy across each one.

Keep in mind this applies only to that specific token. Other tokens can access the same Data Source and Pipes based on their unique read scopes.

Adding filters on the Pipe scope

Applying the filter on the Pipe read scope only impacts that specific Pipe. Instead of filtering out rows from the underlying table, it will filter out rows from the results of the Pipe. In this sense, you can almost think of this like a HAVING clause. It's filtering the results of the query, not rows from the table.

An example

Let’s go back to the usage based billing example I described above. That Data Source that contains all your customers' usage events? Let's call it usage, and let's say the customer IDs are stored in a column called customer_id. Here's how that table schema might look in a Tinybird .datasource file:

Now imagine you created a Pipe with this SQL and published it as an API:

It’s a primitive example, but you get the point.

If you make a request to this API using a token with a standard read scope on the usage Data Source, you will be able to see data for any given customer_id.

To limit this token to only return usage for Customer A, you can add the filter customer_id = ‘CustomerA’ to the usage Data Source read scope for the token.

How to do it in the Tinybird UI

To do this in the Tinybird UI, click "Auth Tokens" in the left nav, then add a new token by clicking the plus icon next to Workspace Tokens or  "New Token" at the top right. Add a read scope to the Pipe by clicking "Add Pipe scope" and selecting the Pipe.  Then click “Add Data Source scope”, choose the Data Source, and write your SQL expression. You can click "Test" to make sure it's a valid filter, then click "Add". Easy enough.

Do it programmatically with the REST API

Of course, it makes no sense to manually go into the Tinybird UI every time you add a new customer. Instead, you can use the Tinybird REST API to programmatically add new tokens.

To create the same token using the REST API, all you need to do is issue a POST request to the Tokens endpoint with name and scope query parameters.

The format of your scope parameter will look like this: DATASOURCES:READ:datasource_name:sql_filter

Here’s how that looks in a curl command with the above example. Note the use of the $admin_token which has write permissions on the Token API.

You can check out the API reference for the Tokens endpoint for further reading.

Hopefully this basic example helps you implement row-level security as you build with Tinybird. If you have any questions, you know where to find us.

‍