What are some rules of thumb when choosing between RLS and security barrier views?

[chipilov]

Hello,

As far as I understand, there are 2 main ways to secure data in PostgreSQL:

• Row Level Security (https://www.postgresql.org/docs/13/ddl-rowsecurity.html)
• Security Barrier Views (https://www.postgresql.org/docs/13/rules-privileges.htm)

I understand that the 2 can often be combined so that RLS provides access control on the row axis (i.e. it only allows certain rows to be visible) while the view provides access control on the column axis (i.e. it only allows certain columns to be visible).

However, it seems to me that as long as the view is marked with security_barrier, it can also provide access control on the row axis by using an appropriate WHERE clause (and possibly a CHECK OPTION for updates/inserts). In Supabase's case, I assume that the WHERE clause of such views can take advantage of the predefined auth.uid() function, e.g.:

CREATE VIEW public.phone_number WITH (security_barrier) AS
SELECT person, phone 
FROM private_schema.phone_data AS pd
WHERE auth.uid() = pd.uid;

With that in mind, what are some rules of thumb about when to use RLS and when to use security barrier views?

[steve-chavez]

Hey @chipilov,

With that in mind, what are some rules of thumb about when to use RLS and when to use security barrier views?

Security barrier views predate policies, they were used at a certain point to implement row-level security. See this blog post for more details.

With the security barrier approach you basically have to recreate the RLS functionality manually in your VIEWs. It works, but it's more complicated.

With RLS you can be fine-grained about statement type(INSERT/UPDATE/DELETE) and the postgres role for which the policy is applied.

while the view provides access control on the column axis (i.e. it only allows certain columns to be visible)

Note that you can also combine regular VIEWs with RLS, see #1501. That would give you the same ability to hide a column.

As far as I understand, there are 2 main ways to secure data in PostgreSQL:

There's also table-level/column-level security through GRANTs, you could REVOKE SELECT(your_col) ON your_table FROM authenticated to prevent access to a particular column.

What are some rules of thumb when choosing between RLS and security barrier views?

I'd recommend sticking to RLS and combining it with regular views as mentioned above.

[chipilov]

Hi @steve-chavez,

Thanks for the leads and tips, I'd like to follow-up on 3 things:

#1501 had a link to https://postgrest.org/en/stable/schema_structure.html and I particularly liked the schema isolation tip at the top:

It is recommended that you don’t expose tables on your API schema. Instead expose views and stored procedures which insulate the internal details from the outside world.

I plan to do exactly that and if we assume this setup, then it seems to me that using security_barrier views is actually a little easier for the following reason:

I come from the Java/Spring world where usually (not always possible, but usually) you define the permissions of a REST API on the controller level (e.g. with some kind of annotation on the controller method). The benefit of this is that it's very easy to audit endpoints because you have both the input/output data definitions of the API and its permissions in a single place so it's easier to reason about them. Similarly, if we assume that in Supabase I will use views as the public API (as suggested in the linked article) it would be very convenient to keep both the data definitions AND the permissions rules right there in the view itself.

The alternative to this would be to be have to inspect both the view AND the RLS rules (and possibly any additional GRANTs/REVOKEs) which is, of course, possible but seems less convenient than just looking at the view definition.

The drawback of this, of course, is that if several views make use of the same table, they'd have to repeat the rules (instead of all taking advantage of a single set of rules on the table). In theory, this does NOT sound DRY, but in my experience it has the benefit of explicitness which can avoid mistakes (e.g. setting up RLS based on a certain use-case and then re-using them by another view for a different use case).

Other than that, I understand that RLS is actually a little more flexible because it allows you to set different rules for SELECT/INSERT/UPDATE whereas views are more rigid that way (although I think you can easily overcome that by using views for SELECTs and functions/procedures for inserts/updates).

Am I missing anything technical which makes RLS more secure than secure_barrier views? I know that RLS have their own drawbacks which need to be taken into consideration (e.g. see the section starting from "Be aware however that such accesses can create race conditions that could allow information leakage if care is not taken. As an example, consider the following table design:" here: https://www.postgresql.org/docs/13/ddl-rowsecurity.html) - do security_barrier views have more security loopholes to consider?

I realize that some of what I wrote above is a matter of opinion and someone might reasonably have a different opinion (e.g. that RLS are easier to manage), I just want to make sure that I am not using any factually incorrect information to form said opinion.

[steve-chavez]

The benefit of this is that it's very easy to audit endpoints because you have both the input/output data definitions of the API and its permissions in a single place so it's easier to reason about them.

Yes, I see the benefits. Note that you can also have a "declarative structure" by organizing your DDL into files, here's an example.

Am I missing anything technical which makes RLS more secure than secure_barrier views?

Not really, it's only that it's more work to do RLS with security barriers as you mentioned above.

As an example, consider the following table design:" here: https://www.postgresql.org/docs/13/ddl-rowsecurity.html) - do security_barrier views have more security loopholes to consider?

I'd suggest testing regardless of the approach. You can do tests at the db level with pgTap, Also an example here

[chipilov]

I'd suggest testing regardless of the approach. You can do tests at the db level with pgTap, Also an example here

Great tip, thanks @steve-chavez!