During the last couple of weeks, we’ve made major improvements to our csv import process, increasing its speed and reliability, getting almost a 2x performance. Tinybird Analytics is now able to ingest around 680,000 rows per second – in the smallest Tinybird paid account.
We’ve also been working on ways to ensure your data is only accessible in the way you want it to, so we’ve added SQL filters to Auth Tokens.
Define who sees what via our Auth Tokens with SQL filters:
In order to read, append or import data into Tinybird, you’ll need an Auth Token with the right permissions. When building an application that queries any of your tables, you can either use the default token that is automatically created along with the table or programmatically generate an Auth Token with a specific scope for it. You would do it as follows:
curl -X POST https://api.tinybird.co/v0/tokens/?name=test&scope=DATASOURCES:READ:table_name&token=$TOKEN
But now, with the new SQL filter capabilities, in addition to creating Auth Tokens which have read access to a single table, you can also create Auth tokens which have access to a subset of a table; or more specifically, to those rows that meet a specific criteria.
Imagine you have a table containing real estate information for different cities around the world. Most likely, at some point, you will need to only expose data for a particular city to one or many of your users. Instead of splitting the original table in lots of different ones or create a backend that adds the required filters, adding a simple SQL filter scope to your Auth Token would limit the access that your application has to your data and prevent data leaks.
curl -X POST https://api.tinybird.co/v0/tokens/?name=test&scope=DATASOURCES:READ:table_name:city==Vancouver&token=$TOKEN
Using the Auth token defined above, when running a query against your table, only rows with ‘Vancouver’ in the city column would be taken into account. It doesn’t matter if it’s an aggregation or a filter, only data for Vancouver would be considered.
Let us show you a running example. For it, we will use the nyctaxi dataset we’ve used in many other occasions. As you can see below, we are performing exactly the same query but using different Auth Tokens, which is counting rows (easy, uh?).
Let’s start by running the query with the default
// This Auth token has read permissions and no filters const READ_TOKEN = 'p.eyJ1IjogImMzZTMwNDIxLTYwNzctNGZhMS1iMjY1LWQwM2JhZDIzZGRlOCIsICJpZCI6ICI2MThhNDhlOC0zM2NjLTQ3ODctODI0Ni00YmUwYmMwODQ2MDEifQ.5lp7N8vJnOE4UhehyroQLvoZ_fUEAMivTMgc4ChKlDU'; var nyc_taxi = tinybird(READ_TOKEN).datasource('nyc_taxi') var res = await nyc_taxi.query(` SELECT count(*) c FROM _ `) console.log(res.data['c'], res.statistics.elapsed)
Below, we are using a different Auth Token which contains the
READ:payment_type==4 filtered scope. Only the rows with that payment_type id will be used in the queries.
// This Auth token has read permissions with a `READ:payment_type==4` filter const READ_TOKEN_PAYMENT_TYPE_4 = 'p.eyJ1IjogImMzZTMwNDIxLTYwNzctNGZhMS1iMjY1LWQwM2JhZDIzZGRlOCIsICJpZCI6ICI0YTI5MTA1ZS1hMzlmLTQyNjItYmRkNS00ODE3NGI4YTc1MmQifQ.kfnzf9hcs9wh0CVlGuFTrcD1W2Z1AHDXNViv6MEZHIo'; var nyc_taxi = tinybird(READ_TOKEN_PAYMENT_TYPE_4).datasource('nyc_taxi') var res = await nyc_taxi.query(` SELECT count(*) c FROM _ `) console.log(res.data['c'], res.statistics.elapsed)
As you can see, both queries are the same, but the results are different depending on the Auth Token (filtered or not) you use. And yeah, no back-end code needed for it.
This opens lots of possibilities, especially when used dynamically within your applications (Auth Tokens can be created/modified at runtime), so you can seamlessly integrate your Tinybird Analytics APIs with any permissions systems with just a few lines of code.