Okay, last talk of the day, I think it will be quite intense and interesting.
So please welcome Jimi Angelacos and he will tell us why RLS sucks and if we can use it.
Thank you very much.
Thank you, thank you for being here.
This is all about knowledge sharing, right?
I'm not out to impress anyone.
I was trying, well let me get into that in a moment.
Let me talk about myself and my favorite subject.
So I'm a systems and database architect, I'm based in Edinburgh in Scotland.
I've been using open-source and trying to contribute for the past 95 years.
I've been using Postgres exclusively for the past 16 years and now I'm a contributor to the project.
My book is coming out.
It's going to print very shortly.
Postgres scale mistakes from Manning.
I also have another book that I co-wrote with other very interesting Postgres people.
And I've also written a Postgres extension called PG status for visualization of internal statistics.
And time series analysis of those statistics.
Now let's get into what this talk is about.
First of all, we're going to see what role level security is.
Then we're going to talk about when to use it, how it works, and how to use it.
Makes sense.
We're also going to examine what's wrong with it and what we can do about that.
And also some other things to try that are interesting with role level security.
So the motivation coming back to what I said before,
motivation is I had a customer that wanted their applications users to not be able to see each other's data.
But what's interesting about this customers that they were used to the fact that their application was usually badly coded.
And they were like, I don't trust my application.
It would leak data from one customer to the other.
And so they were looking for a solution for that.
Because their application used to have things like in the past, don't try it now.
Slash user slash 1234 slash data.
And if you guessed the URL of the other user, the idea of the other user, you were able to see their data.
Nice.
So something that can protect you against that is role level security.
So role level security gives you a finer-grained control over which rows are visible to which
users.
And we're talking about database users now.
So it provides additional security beyond the table or column level privileges that we're used to with the grant statements in SQL.
So it's a type of access control list.
And it saves you the effort of doing your security filtering in the application layer, right?
Because if you have it in the database, it's already there.
The data's already protected.
You don't need to write code to do that.
So when would you use role level security?
Some scenarios are you could have confidential data.
And you want to restrict access to sensitive records.
For example, let's say your doctor, you don't want your other staff to be able to see the medical records.
Only you should be able to see them.
Just an example.
Or it could be departmental separation or separation of roles in your organization.
For example, only the HR department should be able to see HR related content in someone and so on.
Only finance should be able to see finance and so on.
Another use case is multi-tenancy.
So if you have systems that are used by multiple tenants, let's say by multiple customers,
you want them to be able to see only their own data in the database.
So it does give you finer grain visibility control.
You select which roles out of the same table a specific user can see.
So you don't have to have one table for each customer and have 10,000 tables with their data.
You can have a table that says transactions.
So how does RLS work?
Similarly, what you see as the result of RLS is that from the user's perspective,
the rows that are not supposed to see don't exist for them.
So if let's say there's a thousand rows in the table and you're only supposed to be able to see 100 rows out of that table,
if you do a select count, the number you will get back is 100.
The other rows do not exist for you.
You don't even know how many they are.
So you can tell that RLS is built into the internals of Postgres and it's like a barrier that doesn't allow you to see the things you're not supposed to see.
So how does the barrier function?
Key concepts are the policies where you define the conditions for reading and modifying rows in that table.
And also there's the security barrier that needs to be applied to things like views,
so that people are not allowed to bypass the row level security by doing clever tricks with the query optimizer, let's say.
So, please move to the next slide.
So how does RLS work?
It is exactly literally an access control list.
It's not like an access control list.
So internally, what you're doing is you are effectively adding where to your original query.
So it's like show me the transactions where user equals that.
So the user is only able to see those transactions.
And the policies that you create can be permissive or restrictive.
What is the difference?
So permissive policies, policy A and policy B are the default.
And it means that if any of the permissive policies match, right, if I, for example, policy A is that I'm in the finance department,
I don't need policy B to be able to read the data because it's OR.
Get it? So if one of the policies that are permissive are satisfied for me, the user, I can read the data.
Restrictive policies require an end.
So if let's say there's a policy C that forbids something, but policy D allows it,
because they're restrictive both have to allow it.
So as the result of policy C not being satisfied and being restrictive, it forbids access to the data.
Clear? Cool.
All of this is visible in the Postgres catalog inside PG policy.
And PG policy has columns like policy RLID, which table, it's the idea of the table that the policy refers to.
Policy Command does the policy apply to select inserts, updates, deletes, or all of the above.
Is it permissive to or restrictive false?
And this is where the access list, access control list thing comes in.
You get an array of roles that the policy applies to.
So it tells you that the policy applies for these users.
And then you get the specifics of the rules. You get the using clause.
We'll see what that is.
And the with check clause that define the policy.
So let's see how you define the policy.
First of all, what you have to do is enable role level security by default it's turned off.
And why is it turned off by default? Because by default it denies everything.
So you wouldn't be able to read any rows from the table.
So now, by enabling role level security without the policy, I've blocked everyone out.
So I need to create the policy that allows access to the rows of that table.
So I create the policy, cost policy, on the table customer, for all SQL commands,
to public, which means that it applies to everyone.
The policy applies to every single user in the system.
Using asterisk, we will see why not.
Using customer user, which is the column in the table, equals current user.
So I want current user to only be able to see the rows that have the same name as him or her or it.
So whatever the current user is, this will get applied to my query.
So it's like I'm adding the statement, let's say my queries select star from table.
So excuse me.
So select star from customer.
What I wrote is effectively turning the query into select star from customer, where customer user equals current user.
So that's what the using clause does.
It adds a condition to the original query.
But I did have a clickbait title.
It does suck, but our list sucks too.
Why?
Because there are a few things that are wrong with the current implementation of row level security.
Not in the sense that they didn't think things through back when the implemented it.
But right now it doesn't work for a lot of people.
Because it assumes that your application works in a certain specific way.
And you may have noticed that people generally don't have their data separated by which database user it belongs to.
Also, most of the time you don't want Postgres to manage your applications users.
Another problem with the row system that RLS depends upon is that it has global scope, so it applies for all databases that you have on the server.
So you have no fine-grained control over which users can see what in which database.
Additionally, the RLS system doesn't give you any sort of reasonable way to store user attributes or preferences.
So you have to put those in your application anyway.
So in most cases, your application is probably going to connect to the database with the connection string with the fixed user that is just one user.
And that is a real problem.
Because when you have something deployed like my customer did, and they suddenly decided that they required row level security, they didn't have the data separated by a user by database user.
They didn't even have a second database user in the database.
So additionally, writing to the database and reading with a single user makes all they think difficult.
Because from the perspective of the Postgres system, it's only one user doing everything.
And changing the application usually requires a significant rewrite.
So that you're able to make the application extract which users logged in and then use that user to pass it to Postgres to login to Postgres to get the data that they need.
So if you already have a developed application, you don't want to do that.
Additionally, aligning application and database users is tedious.
You end up with a spaghetti of grant statements that overlap or add to each other.
And also you have to keep them in sync.
So whatever's in your application needs to be synced to the role system in Postgres.
So that's a job in itself.
So what can we do about that?
Are there ways to enjoy the benefits of row level security without actually using the role systems directly?
So one possible solution is we can set variables in our connection or session or transaction.
And we can use those variables in the policy.
There's nothing preventing us from using them in the policy.
So we can create a policy that says create policy on transaction for all to public using tenant equals current setting which is how you get the value of a variable in the current environment.
And setting app dot tenant.
Why is it prefix by app?
Because you don't want to mix this with actual Postgres configuration.
So you create a prefix like my app that separates those variables from Postgres variables.
So this works.
It actually works.
So you can set app tenant equals mega corp in your connection.
And the policy will retrieve from the environment the current value of app dot tenant.
And find that it is mega corp in the allow access.
So that's a way to do it with a single application user.
With a single database user connecting from the application.
But what if you're feeling paranoid and someone tries to connect as mega corp because they know the name of the company.
So what do you do usually?
You turn everything into a UID. That's the account, yes.
It's not impossible, but it's much harder.
So that's the layer of obscurity to what you're doing here.
So by making app dot tenant the UID, they can't just throw in a lucky guess of a company name and see their data.
Cool.
So what you do is you set app dot tenant to be a string that represents a UID.
And then you cast it in the policy to a UID to make sure that the data type is correct when you're performing the checking.
And that's it.
So something to be aware of is depending on whether you're using connection pooling or not.
You may want to set local this value because set local restricts it to the current transaction.
So if you have like a connection pooling scenario where you fear that this variable may leak to another session,
you can just set it local and have it only there for the duration of the transaction.
So wrap everything that the application doing inside the transaction and then this poof goes away.
When the transaction is completed or rolled back, this doesn't exist anymore, so it can't leak to the next user.
Would you like us to take this step further?
Cool.
Next level, we have the access control list that we implemented and we add roll based access control to it.
So we construct an explicit access control list and an explicit roll based system around it.
So how do we do that? We add an ACL column to the table.
The rule has an ACL.
So we change the table transaction, we add the column ACL that contains an array of UIDs.
And we just say it can't be null, it has to be at least an empty array of UIDs to avoid errors.
So an array of UID in the case as we saw that we are using UIDs as identifiers for the rolls.
So what we do then is we set the rolls that are granted access in the access control list.
So you write a statement like set or set local, app tenant rolls, this array of two D's.
Because I may need both rolls to access this data.
Remember, I may need to be in finance, but I may also need to be a director in order to be able to see this roll.
Okay. So what would the roll level security policy look like for such a thing?
You would type create policy, transfer all policy on transaction, which is our table for all commands to public everyone.
Using ACL, double ampersand, current setting app tenant rolls.
You know what the double ampersand operator does here.
Okay, so it's an array operator and it's make sure that whatever is that these two arrays have shared elements.
So if any of the elements are there, it becomes true and therefore allows access to the roll.
Okay.
And never forget to alter table transaction to enable actually the roll level security that you created the policy for.
So what does this do?
This policy checks if any of the tenant rolls are inside the access control list for that roll.
Right? Remember, this is roll based. Forget about table level security.
So that's why we have an ACL column in every single roll of the table.
The rolls can have attributes that define their privileges.
And like Postgres rolls, they can be thought of as groups.
Remember, Postgres user is a Postgres roll that can log in.
Right? So when we're talking about rolls, we're talking about Postgres users.
And you can also use them as groups because you can accumulate privileges by being assigned multiple rolls.
So rolls can be granted to other rolls and then you have an aggregate of the privileges.
Which is basically paralleling PostgresQL roll system.
It works exactly like this.
Do you want to dive even deeper?
How would you protect this system from your application?
Remember, the application can connect to the database, read and write to the database.
So it can mess this up.
It can set the access control list to empty for everyone, for example.
But you don't want the application to be able to do that, do you?
So if you don't want it to even be aware of how it can change rolls and policies,
you hide the system from the application.
Why?
Because you don't trust your own code or you don't trust the third part of the application that has been bestowed to you.
And you want to add access control to something that didn't have it.
So let's assume that we're working with an app that is written in Django.
What you would do from the database side is you would create a roll for that application, for example, create a roll Django.
You would create your tables as normally.
So for table transaction, you would have your usual example columns here, ID, which is, of course, random UUID.
And amount created that and you add a column called ACL.
So the ACL is in array of UUIDs as we discussed before.
It's not an old default empty array of UUIDs.
So in order to speed up enforcement of the policy, we would like to have an index that supports array operations on the table.
So what you do is you create index on the table transaction using Djinn that supports array operations.
And you just create this index on the ACL column with the modifier array ops.
And that lets you perform the double ampersand operation very quickly from the index.
So you can check instantly to see if someone has access to the roll.
You then parallel Postgres' own system of rolls.
And you create a table called tenant roll.
Let's assume that in the application your tenant is called tenant.
And you start in a table called tenant.
So you create a table tenant roll that has a roll ID, a roll name, and a roll description.
So that humans can understand what the roll is about.
And then you create the table of membership to that roll.
So all you need is tenant ID and roll ID.
And you need them to point to the other tables to the table tenant for tenants and to the table tenant roll for rolls.
And that is sufficient to implement a roll-based access control system.
All you need to know is which roll everyone belongs to.
And then you can determine with the policy from the ACL if that roll or the aggregate of rolls that the user is using allows them to access the data.
Of course you create an index on this table for speeding up things.
So what have we done here?
Effectively we have removed the ability of the database user.
Sorry, this is what we're doing now.
We're removing the ability of the database user Django to see these tables.
So we're revoking every privilege on tenant roll from Django.
So that the application doesn't even know these tables of rolls and memberships exist.
And then we revoke select on the table that we actually want to read from so that the application can't do a select star from the table and find out that there's an ACL.
And then we grant back permissions to select to the application user.
But we don't grant them for the ACL column.
So that's how you make a column invisible to a user.
You remove their ability to select star from the table by revoking all privileges and then you grant back the privileges.
So they can select all the columns that you want them to.
So we create the policy.
Trans our list on transaction using our usual trick ACL.
Double ampersand current setting app tenant rolls equals true.
And that is sufficient to grant access.
And then alter table transaction and enable roll level security.
Okay.
Now this system, the inner, inner workings of the system are hidden from Django or whatever application you have.
How can it regulate access to the data?
You expose the functionality of the system through functions.
So you write a function that's called create tenant roll that allows the application to create rolls.
So roll name, roll description returns you you ID.
Here's the you ID of the new roll that you've just created.
Get tenant rolls is I'm passing it a tenant ID tell me which rolls this tenant has in the system.
Makes sense.
Finally set tenant rolls.
So for this tenant, make sure that they have rolls 1, 2, and 3.
Set tenant rolls sets the rolls for a specific tenant.
And returns Boolean that was able to successfully set the rolls.
So there's one more thing that we need.
For each of the tables, we need a function that adds the roll to that roll.
Right.
So you need an add roll to ACL function.
And you also need a removal from roll ACL type function.
These can be called by the application itself.
For example, by overriding Django's save method.
It's important to note that these functions should be written as security defining.
So that they have the privileges of the user that created them and not the privileges of the user Django.
Otherwise the user Django wouldn't be able to select and set those values.
But they're not able to do it by reading and writing to the database directly.
They're only able to access this system through functions.
And remember when using security defining, you should probably at the bottom of your function definition.
Set search path equals public and then PG temp.
So people aren't able to easily override your function by creating a temp function.
So let's talk about a few things that can catch you out when using our less.
The first and foremost, right.
Policies can add overhead to queries.
So everything becomes slower.
Why?
You may have complex conditions like using a Boolean that gets calculated from a join of three or four other tables.
Right. That would slow down access because for every single row that you would bring back from that table,
it would have to perform that select of three tables and join to see if it satisfies the condition.
So complex conditions can be a problem.
So try to keep your policies simple and explicit when using our less.
Another thing to remember is that the super user bypasses all RLS checks.
It doesn't apply to them.
So the Postgres user or whatever super user you have can read whatever they want from database without the RLS blank to them.
Table owners also can bypass row level security because it's their table after all.
But not if you force row level security.
So if you alter table force row level security, then you make the policy apply to the table owner as well.
That's what force means.
You should also try to set a restrictive delete policy because it's easy to assume that,
you know, deleting is writing to the table. It's the same as updating, right?
No, it's not. So if you have an update policy and you assume that people are not able to delete rows because they can't modify rows in the table, then you are wrong.
If they can read the rows and there's no delete policy, they can also delete them.
So make sure that you make that restrictive so that it is exclusive with other permissions.
And also make sure that you reset your variables so that they don't leak from session to session.
And other people get the privileges that they shouldn't have.
Which is, it's worth mentioning that PG Bouncer's statement mode will not work with set and set local because everything, it's like auto commit, right?
Everything is in, it's own isolated transaction, so you can't really set a statement in the same transaction as the command you're running.
So it won't work.
Finally, if you have views, as we mentioned, you want to make sure that nobody can take advantage of your view and bypass row level security,
by tricking the query optimizer. Let's say by creating a function that they define as cost zero point zero zero zero zero.
So the optimizer will select it always, right?
And such a function can leak data.
Because for example, you can put in it. It may not be able to read the data, but you may cheat and put in a notice.
And it can leak the data through the notice statement, right, which is not a select.
So if you define the view with bracket security barrier, that means that this is not allowed.
So that's about it.
Here's some discount codes for the books that I mentioned, and I will be very happy to take your questions.
You know the front?
You.
I will repeat your question.
I was wondering, is the wait just in your solution to begin the set of variables in this session?
Is there a wait to do that in just one trip to the server together, the select?
Right, so the question is if you can set the variable in one trip to the server without, you mean without issuing two statements.
First the set and then you just wrap everything in a transaction, and it does both things at the same time.
It's better than nothing.
I don't think you can set the connection.
I don't think it's safe or sane to set the variable in your connection string, but you could do that.
You could probably.
But the standard way people do it is usually they open the session as they set the variable and then they use the variable through the policy or whatever.
In the second question, might not have an answer.
Is there a wait to secure a field level rather than low level?
I mean, even finer than just.
There's already a column level security.
You can grant access to specific columns.
Also depending on a variable set in the session?
No, this is role-based, so it would have to do with Postgres roles.
But you can use the same trick to implement a similar system for specific columns if you want to dive to the third level.
It's a lot of work.
Up there.
A simple question.
How does the optimizer cope?
If you've got a table with a million rows, 12 of which the user can actually see, and you join on that table,
does the optimizer know about that or does it pick the?
No, but if there's a policy, you mean for the ACL, right?
Yeah, that's why we created the index.
So the optimizer knows to use the index for the ACL.
Okay.
So if there's only 12 protected rows on the table, and you have an index on the ACL,
then it knows to select from that index.
So does the optimizer kind of know that it's a more complex query involved with an index and some values?
Does it just unroll it and treat it like a query with all that stuff in it?
Sorry, what I'm saying is if you've got an index and certain values in the index,
the optimizer just goes, okay, you've said select from where blah blah blah.
And it sort of internally adds and index is blah blah blah.
So it just, well, thank you.
I'm glad you were able to get your question.
But any other question?
Yeah, all these tables that you created for the rows or these ACL columns you created can be indexed.
And the optimizer does use the indexes.
So it basically modifies the query to apply the policies.
Yes.
Can you have a row row level security for tables that are linked to each other through foreign key relationships?
Like you have the UI design run table, you want a little security based on that and then you connect to other tables or you have to copy that.
Basically you need a joiner.
Can you do that?
Yeah, the joiner's work if you have access to the rows.
If you don't have access to the rows, it's the same thing as the rows not existing for you.
So the joiner won't work, right?
But join's work.
Yeah.
So if you have access to transactions and you want to get, let's say, the payment details for the transaction.
As long as you can see the transaction ID you're fine.
And if you wanted to protect the payment details as well, you would set an ACL on them as well.
On the other table.
Separate ACL.
Yeah.
But the joiner already protects you because you can't see those rows that you can't see.
So you can't join them with anything.
Yeah.
But the application could select anything it likes from the secondary.
Yes.
So the basic use case here was avoid inadvertent leakage of data through application bugs.
Right?
So it's not intentionally crippling the application so that it's not able to select its own data.
We're just making sure that the developers that wrote it that we don't trust haven't exposed any bugs that let another customer tweak their URL or something to see data that they're not supposed to.
And the other question.
Okay.
Thank you very much.
Thank you.