Hacker News Reader

Comment by mkurz | Hacker News Reader

Be aware when using RLS with views: By default the RLS policy will be executed with the permissions of the owner of the view instead with the permissions of the user executing the current query. This way it can easily happen that the RLS policy will be bypassed because the owner of the view is a admin account or the same account that owns the underlying table (see the the gotchas section of the original post).

However, upcoming PostgreSQL 15 adds support for security invoker views: https://github.com/postgres/postgres/commit/7faa5fc84bf46ea6... That means you can then define the security_invoker attribute when creating a view and this "... causes the underlying base relations to be checked against the privileges of the user of the view rather than the view owner" (see https://www.postgresql.org/docs/15/sql-createview.html) PG15 beta 1 release notes: https://www.postgresql.org/about/news/postgresql-15-beta-1-r...

View on HN

This was a shock to us after using RLS for a while. The solution outlined here worked great for us: https://www.benburwell.com/posts/row-level-security-postgres...

So they create table-valued functions which support the "SECURITY INVOKER" security context, and then select from that function to form the view. I suppose there's a feature request somewhere to support the "SECURITY INVOKER" feature for views directly?

Well well lookie here:

commitdiff 2022-03-22: Add support for security invoker views. - https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...

discussion 2021-12-17: [PATCH] Add reloption for views to enable RLS - https://www.postgresql.org/message-id/b66dd6d6-ad3e-c6f2-8b9...

explanatory blog post: 2022-03-22: Waiting for PostgreSQL 15 – Add support for security invoker views. - https://www.depesz.com/2022/03/22/waiting-for-postgresql-15-...

This seems to be slated for PG15: https://www.postgresql.org/docs/15/release-15.html#id-1.11.6...

> E.1.3.1.6. Privileges: Allow view access to be controlled by privileges of the view user (Christoph Heiss) Previously, view access could only be based on the view owner.

Syntatically it will look like:

    CREATE VIEW vista WITH (security_invoker=true) AS SELECT 'Hello World';

That seems like a bug to me, and a significant one as well.

The underlining promise of RLS (sometimes even referred to as “virtual private database”) in an RDBMS, is that data should never leak because it’s handled transparently by the db.

This seems like a significant leakage point that the user has to personally manage.

This pretty much mirrors stored procedures though which have the option of running as the definer or the invoker. Breaking this with “when RLS is enabled stored procedures and views set to run with the permissions of the definer intersect with the RLS policy of the invoker” is crazy weird.

Maybe it would actually be good behavior but it would super super unintuitive.

Isn’t it also super unintuitive that developers will have to manage partitioning customer data at the Application Layer even though they think they are using a Data Layer security policy.

When RLS is enabled, their should never be a situation where Customer A might be able to gain access to Customer B data. That’s literally the entire objective of the feature.

This is giving people a false sense of security, and for multi-tenant applications has massive consequences since this will leak data.

Assuming correct, it's an absolutely horrible default that needs to be changed.

Imagine if the default Unix permission was to set files with setuid.

Reply on news.ycombinator.com