SECURITY INVOKER support for views in Redshift

For views it is always the privileges of the invoker that are evaluated at run time.

From the docs page below https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_VIEW.html

To create a standard view, you need access to the underlying tables, or to underlying views. To query a standard view, you need select privileges for the view itself, but you don't need select privileges for the underlying tables. (In a case where you create a view that references a table or view in another schema, or if you create a view that references a materialized view, you might need additional permissions.) To query a late binding view, you need select privileges for the late binding view itself. You should also make sure the owner of the late binding view has select privileges to the referenced objects (tables, views, or user-defined functions)

Many thanks for the reply.

Based on my testing, views do seem to be executed using the permissions of the owner, not the invoker. This is aligned with the behavior in PostgreSQL.

The docs state:

You should also make sure the owner of the late binding view has select privileges to the referenced objects (tables, views, or user-defined functions)

This is consistent with the model where access control is based on the ownership and the permissions granted by the owner.

This example illustrates that the owner permissions are used. It would be great if Redshift could add the SECURITY INVOKER option similar to Redshift stored procedures and PostgreSQL security_invoker for views.

-- Demo of views being executed with permissions of view owner, not view invoker

-- Create users

set session authorization default;

create user "user_a" with password 'user_a_A123';
create user "user_b" with password 'user_b_A123';

grant create on schema public to "user_a";

-- Create table

create table "public"."table_a" (dx varchar(255), value int);
insert into "public"."table_a" values ('ABC', 10);

-- Revoke select from public and user_a, and grant select to user_b

revoke select on table "public"."table_a" from public;
revoke select on table "public"."table_a" from "user_a";
grant select on table "public"."table_a" to "user_b";

-- Set current user to user_a

set session authorization "user_a";

-- Verify that user_a cannot read table_a (permission denied)

select * from "public"."table_a";

-- Create view and grant permission to user_a and user_b

create view "public"."view_a" as select * from "public"."table_a";

grant select on "public"."view_a" to "user_a";
grant select on "public"."view_a" to "user_b";

-- Observe that user_a cannot read view_a (permission denied) despite having read access to the view
-- due to no access to the underlying table table_a

select * from "public"."view_a";

-- Set current user to user_b which has access to both the view and the table

set session authorization "user_b";

-- Verify that to user_b can read table_a

select * from "public"."table_a";

-- Observe that user_b cannot read the view (permission denied) since user_a has no access to the underlying table,
-- which indicates that the view is executed with permissions of the view owner user_a, and not the invoking user_b

select * from "public"."view_a";

-- Clean up

set session authorization default;
revoke create on schema public from "user_a";
drop view if exists "public"."view_a";
drop table if exists "public"."table_a";
drop user "user_a";
drop user "user_b";

PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.54052