4 ms·
> PostgreSQL 15 lets users create views that query data using the permissions of the caller, not the view creator. This option, called security_invoker, adds an
by hellcow 4y ago
> PostgreSQL 15 lets users create views that query data using the permissions of the caller, not the view creator. This option, called security_invoker, adds an additional layer of protection to ensure that view callers have the correct permissions for working with the underlying data.
Thank you, kind friends. This is a huge QOL improvement when using row-level security with views and is the top reason I'll be upgrading from Postgres 13 to 15.
- brailsafe 4y agoWhat might you use it for? I love Postgres and am always looking for inspiration
- nrmitchi 4y agoI'm not doing this, but it would be very useful when using row level security in a multi-tenant application. You can create a single view for "all active orders" (or whatever, just an example) and querying that view from different users would now give you the correct (user limited) results. It sounds like previously this was not the case.
- hellcow 4y agoExactly right. We can isolate customers from one another with policies on the table, so once the policy is in place, it's actually impossible for us to write code that exposes data from one customer to any others. But if you created a view on that table, querying the view would expose all the underlying data in the table, effectively removing the policy. Previously the only way I found to get around this was to define a function with security_invoker, then create a view based on that function. But this change removes the need for this extra function, and you can create views that use row-level security directly.
- no_wizard 4y agoI suspect this will make Supabase very happy! They really believe in row level security as a major line of defense so I imagine this makes it even better
- ggregoire 4y agoThis will be great for PostgREST too.
- tiffanyh 4y agoThis should have always been the default behavior. Is it the default now? Shocked it never was since it is a major source of data leaks (which completely defeats the purpose of using row-level security).
- deleted 4y ago[deleted]
- tremon 4y agoYou're mixing up two orthogonal concepts. In the absence of row-level security, allowing a user read access to a view only (which may only have a filtered or condensed view of the underlying tables) is the more secure solution. If such a view would only work if the user also had access to the raw underlying tables, that would be a major source of data leaks. In the presence of row-level security, the same concept allows you to create a system where (for example) the user can still access aggregate data for parts of the table where the individual rows are hidden to them. A simple example: a manager has full access to the salary records of his direct reports, but there's also a view that shows the wage balance per-team. If that view were to have invoke-as-user permissions, the manager would still only be able to view his own teams' wage balance. By making that view execute as security_definer instead, the admin can once again exercise full control over who gets access to which data. So no, I don't think it should not be the default behaviour, and no, it does not completely defeat the purpose of row-level security.
- deleted 4y ago[deleted]
- wharfjumper 4y agoEcho this. It's going to simplify a bunch of view definitions and policies.