The following bug has been logged on the website:
Bug reference: 17891
Logged by: smartkeyerror
Email address: smartkeyerror@gmail.com
PostgreSQL version: 15.0
Operating system: Ubuntu 20.04
Description:
Schemas, table and view:
I created 2 schemas:
1 table in the 1st schema and a view in the 2nd schema (the view is a simple
select on the table)
Groups:
I created 4 groups:
for each schema, there is a group for owners (name started by go_) and a
group for readers (name started by gr_)
the table in the 1st schema is owned by a group owner (dedicated for the
schema)
the view in the 2nd schema is owned by the other group owner
User and Grants:
I created a user sas_lect in the 2 groups for readers (gr_*)
I gave SELECT grant on the groups for readers
With the user sas_lect, when I try to select the table, it works
When I try to select the view, it fails: permission denied on the table
If I remove the group owner from the table and the view, (put back gpadmin
as owner):
With the user sas_lect, when I try to select the table, it works
When I try to select the view, it works
```
create user sas_lect password '123';
create user sas_owner password '123';
create role gr_za_ifs_data;
create role go_za_ifs_data;
create role gr_zp_ifs_views;
create role go_zp_ifs_views;
grant gr_za_ifs_data to sas_lect;
grant gr_zp_ifs_views to sas_lect;
grant go_za_ifs_data to sas_owner;
grant go_zp_ifs_views to sas_owner;
-- Create 2 schemas
drop schema if exists za_ifs_data cascade;
create schema za_ifs_data;
drop schema if exists zp_ifs_views cascade;
create schema zp_ifs_views;
alter schema za_ifs_data owner to go_za_ifs_data;
alter schema zp_ifs_views owner to go_zp_ifs_views;
grant usage on schema za_ifs_data to gr_za_ifs_data;
grant usage on schema zp_ifs_views to gr_zp_ifs_views;
-- Create 1 table in the 1st schema and 1 view on the previous table in the
2nd schema
drop table if exists za_ifs_data.t1 cascade;
create table za_ifs_data.t1 (a int);
insert into za_ifs_data.t1 select generate_series(1,3);
alter table za_ifs_data.t1 owner to go_za_ifs_data;
drop view if exists zp_ifs_views.t1_view;
create view zp_ifs_views.t1_view as select * from za_ifs_data.t1;
alter view zp_ifs_views.t1_view owner to go_zp_ifs_views;
-- Give SELECT grant on the table and the view
grant select on table za_ifs_data.t1 to gr_za_ifs_data;
grant select on table zp_ifs_views.t1_view to gr_zp_ifs_views;
-- Check the SELECT grants
select *
from information_schema.table_privileges priv
where priv.privilege_type = 'SELECT'
and table_schema in ('za_ifs_data','zp_ifs_views');
\du+ sas_lect
\dp+ za_ifs_data.t1;
\dp+ zp_ifs_views.t1_view;
-- Check SELECT on the table and the view for the user sas_lect
select * from za_ifs_data.t1;
select * from zp_ifs_views.t1_view;
-- Change owners of the table and the view
alter table za_ifs_data.t1 owner to gpadmin;
alter view zp_ifs_views.t1_view owner to gpadmin;
-- Check SELECT on the table and the view for the user sas_lect
select * from za_ifs_data.t1;
select * from zp_ifs_views.t1_view;
```
