Why the DCL "ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> GRANT SELECT ON TABLES TO <user>" does not seem to work.

Products

• VMware Tanzu SQL

Issue/Introduction

Scenario which does not seem to work:
If there are three users: gpadmin(the superuser), user1 and user2.
1. gpadmin user runs:
ALTER DEFAULT PRIVILEGES IN SCHEMA schema1 GRANT SELECT ON TABLES TO user1;

2. gpadmin user runs:
GRANT SELECT ON ALL TABLES IN schema1 public TO user1;

3. user2 creates a table in schema1
CREATE TABLE schema1.table2 (a int, b int);

4. Result: user1 does not have access to the table schema1.table2 as it was created by user2 even though the DEFAULT PRIVILEGES for the schema was set by gpadmin to grant select access to user1.

ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> GRANT SELECT ON TABLES TO <user>

is normally used with:

GRANT SELECT ON ALL TABLES IN <schema> public TO <user>

The first statement (ALTER DEFAULT PRIVILEGES..) will set the privileges on a table when it is created. It will not affect/change the privilege on existing tables.
The second statement (GRANT SELECT ...) is to grant SELECT on all the existing tables only.

Environment

Product Version: 1.9

Resolution

The resolution is for user2 to run the statement "ALTER DEFAULT PRIVILEGES IN SCHEMA schema1 GRANT SELECT ON TABLES TO user1" .
From then on, user1 will have SELECT PRIVILEGE on all the new tables created by user2.

This is explained in PostgreSQL: Documentation: 15: ALTER DEFAULT PRIVILEGES

You can change default privileges only for objects that will be created by yourself or by roles that you are a member of.

The statement "ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> GRANT SELECT ON TABLES TO userA" only takes effect on the tables newly created by the user who created the default privilege.