Why isn't row level security enabled for Postgres views?

Question

I need strict control of the reading and writing of my Postgres data. Updatable views have always provided very good, strict, control of the reading of my data and allows me to add valuable computed columns. With Postgres 9.5 row level security has introduced a new and powerful way to control my data. But I can't use both technologies views, and row level security together. Why?

if you enable row level security on the table and then use the updatable view on the table, does the security not work? — mehmet, Nov 22 '15 at 19:17
No because the query goes through the view defined role, not the current role. — Calebmer, Nov 22 '15 at 19:50
Then, how about setting up the row level security on the view defined role? — mehmet, Nov 22 '15 at 20:06
I have a few different roles accessing the view, so I lose that information. — Calebmer, Nov 22 '15 at 20:35

Craig Ringer · Accepted Answer · 2015-11-23T03:47:41.920

33

Basically because it wasn't possible to retroactively change how views work. I'd like to be able to support SECURITY INVOKER (or equivalent) for views but as far as I know no such feature presently exists.

You can filter access to the view its self with row security normally.

The tables accessed by the view will also have their row security rules applied. However, they'll see the current_user as the view creator because views access tables (and other views) with the rights of the user who created/owns the view.

Maybe it'd be worth raising this on pgsql-hackers if you're willing to step in and help with development of the feature you need, or pgsql-general otherwise?

That said, while views access tables as the creating user and change current_user accordingly, they don't prevent you from using custom GUCs, the session_user, or other contextual information in row security policies. You can use row security with views, just not (usefully) to filter based on current_user.

edited Nov 23 '15 at 03:47

answered Nov 23 '15 at 03:36

Craig Ringer

307,061
76
688
778

How would it change how view's work? Isn't RLS fundamentally just the addition of some `WHERE` clauses? And isn't a view basically just a SQL statement? – Calebmer Nov 23 '15 at 03:41
2

@Calebmer Views access tables used by the view with the access rights of the user who created the view. To allow row security to usefully filter access to tables accessed via a view based on the "top level" user who accessed the view would require changing how views access the tables in the view so that `current_user` is not set, etc. We have `session_user`, but that doesn't change when you `SET SESSION AUTHORIZATION`, so it's not useful if you're using pooled connections via pgbouncer or similar. – Craig Ringer Nov 23 '15 at 03:46
16

Wow.. this really needs to be mentioned in the Policies documentation page. I just discovered this problem in our application where all the tables are found in a private schema, and they are only made available to the external API via a view in a different schema. Because this view was created by the superuser, the RLS was completely broken even though it had been tested and worked fine against the real table. – deinspanjer Jun 29 '17 at 14:27
1

I did post a message there, but it is pending moderation. – deinspanjer Jun 30 '17 at 00:59
1

could I use functions instead of views? who is current_user when a function runs? – Alex Jan 06 '21 at 10:26
1

Seems that yes: functions have `SECURITY INVOKER` set by default, see https://www.postgresql.org/docs/current/sql-createfunction.html – Alex Jan 06 '21 at 10:47
1

So to fix this, should one just ALTER the view's owner to the db user that has policies? – pyramation Apr 20 '21 at 20:17
1

There is a patch worked on to finally address this issue: https://www.postgresql.org/message-id/flat/b66dd6d6-ad3e-c6f2-8b90-47be773da240%40cybertec.at (https://commitfest.postgresql.org/37/3466/). – mkurz Mar 03 '22 at 21:45

Laurenz Albe · Answer 2 · 2023-01-23T08:32:24.940

20

You can do this from PostgreSQL v15 on, which introduced the security_invoker option on views. If you turn that on, permissions on the underlying tables are checked as the user who calls the view, and RLS policies for the invoking user are used.

You can change existing views with

ALTER VIEW view_name SET (security_invoker = on);

edited Jan 23 '23 at 08:32

answered Mar 22 '22 at 13:39

Laurenz Albe

209,280
17
206
263

3

If someone need it too, the command is CREATE OR REPLACE VIEW "SomeView" WITH (security_invoker=on) AS SELECT ... – Rhuan Barros Jan 22 '23 at 21:24
but now the invoking user needs access to the underlying tables for the view to work properly. Can you have both features (table abstraction AND row level security)? – dnk8n Apr 05 '23 at 09:49
@dnk8n There are ways but not in a comment. – Laurenz Albe Apr 05 '23 at 12:33

score 8 · Answer 3 · answered Oct 09 '20 at 16:21

8

The row level security policy can still be applied in the WHERE clause of the view. For example:

WHERE my_security_policy_function(person_id)

answered Oct 09 '20 at 16:21

Chris G

431
1
7
8

Why isn't row level security enabled for Postgres views?

3 Answers3

Linked

Related