SECURITY INVOKER support for views in Redshift

0

This question relates to AWS Redshift and permissions. Currently, for views in Redshift, permissions to underlying tables in the view query are checked based on the permission of the "definer" (creator) of the view.

This can be problematic from an application security perspective, as views could be shared with other users and, inadvertently, access to tables could be granted to users who are not supposed to have it.

This is consistent with the default behavior in PostgreSQL, but in version 15, support for a security_invoker (true|false) parameter was added to allow for having the permissions be checked based on the user querying (invoking) the view.

Similarly, in Redshift, for stored procedures, an option for whether to use the privileges of the procedure DEFINER or INVOKER exists.

Is there any appetite for introducing support for using the privileges of the "invoker" of views in Redshift, too? This would be tremendously useful and make the product better. Or is this behavior supported through another approach?

best regards,

Lars

Lars
asked 8 months ago272 views
2 Answers
0

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)

profile pictureAWS
answered 8 months ago
0

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
Lars
answered 8 months ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions