Altering default privileges on schemas

Extend ALTER DEFAULT PRIVILEGES command to schemas.

Author: Matheus Oliveira
Reviewed-by: Petr Jelínek, Ashutosh Sharma

https://commitfest.postgresql.org/13/887/

2 files changed, 96 insertions, 0 deletions

diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index f349980759..c6e7031bef 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -1356,6 +1356,64 @@ SELECT has_table_privilege('regress_user1', 'testns.acltest1', 'INSERT'); -- no
 (1 row)
 
 ALTER DEFAULT PRIVILEGES FOR ROLE regress_user1 REVOKE EXECUTE ON FUNCTIONS FROM public;
+ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT USAGE ON SCHEMAS TO regress_user2; -- error
+ERROR:  cannot use IN SCHEMA clause when using GRANT/REVOKE ON SCHEMAS
+ALTER DEFAULT PRIVILEGES GRANT USAGE ON SCHEMAS TO regress_user2;
+CREATE SCHEMA testns2;
+SELECT has_schema_privilege('regress_user2', 'testns2', 'USAGE'); -- yes
+ has_schema_privilege 
+----------------------
+ t
+(1 row)
+
+SELECT has_schema_privilege('regress_user2', 'testns2', 'CREATE'); -- no
+ has_schema_privilege 
+----------------------
+ f
+(1 row)
+
+ALTER DEFAULT PRIVILEGES REVOKE USAGE ON SCHEMAS FROM regress_user2;
+CREATE SCHEMA testns3;
+SELECT has_schema_privilege('regress_user2', 'testns3', 'USAGE'); -- no
+ has_schema_privilege 
+----------------------
+ f
+(1 row)
+
+SELECT has_schema_privilege('regress_user2', 'testns3', 'CREATE'); -- no
+ has_schema_privilege 
+----------------------
+ f
+(1 row)
+
+ALTER DEFAULT PRIVILEGES GRANT ALL ON SCHEMAS TO regress_user2;
+CREATE SCHEMA testns4;
+SELECT has_schema_privilege('regress_user2', 'testns4', 'USAGE'); -- yes
+ has_schema_privilege 
+----------------------
+ t
+(1 row)
+
+SELECT has_schema_privilege('regress_user2', 'testns4', 'CREATE'); -- yes
+ has_schema_privilege 
+----------------------
+ t
+(1 row)
+
+ALTER DEFAULT PRIVILEGES REVOKE ALL ON SCHEMAS FROM regress_user2;
+CREATE SCHEMA testns5;
+SELECT has_schema_privilege('regress_user2', 'testns5', 'USAGE'); -- no
+ has_schema_privilege 
+----------------------
+ f
+(1 row)
+
+SELECT has_schema_privilege('regress_user2', 'testns5', 'CREATE'); -- no
+ has_schema_privilege 
+----------------------
+ f
+(1 row)
+
 SET ROLE regress_user1;
 CREATE FUNCTION testns.foo() RETURNS int AS 'select 1' LANGUAGE sql;
 SELECT has_function_privilege('regress_user2', 'testns.foo()', 'EXECUTE'); -- no
@@ -1403,6 +1461,10 @@ SELECT count(*)
 
 DROP SCHEMA testns CASCADE;
 NOTICE:  drop cascades to table testns.acltest1
+DROP SCHEMA testns2 CASCADE;
+DROP SCHEMA testns3 CASCADE;
+DROP SCHEMA testns4 CASCADE;
+DROP SCHEMA testns5 CASCADE;
 SELECT d.*     -- check that entries went away
   FROM pg_default_acl d LEFT JOIN pg_namespace n ON defaclnamespace = n.oid
   WHERE nspname IS NULL AND defaclnamespace != 0;
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index 166e903012..38215954da 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -816,6 +816,36 @@ SELECT has_table_privilege('regress_user1', 'testns.acltest1', 'INSERT'); -- no
 
 ALTER DEFAULT PRIVILEGES FOR ROLE regress_user1 REVOKE EXECUTE ON FUNCTIONS FROM public;
 
+ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT USAGE ON SCHEMAS TO regress_user2; -- error
+
+ALTER DEFAULT PRIVILEGES GRANT USAGE ON SCHEMAS TO regress_user2;
+
+CREATE SCHEMA testns2;
+
+SELECT has_schema_privilege('regress_user2', 'testns2', 'USAGE'); -- yes
+SELECT has_schema_privilege('regress_user2', 'testns2', 'CREATE'); -- no
+
+ALTER DEFAULT PRIVILEGES REVOKE USAGE ON SCHEMAS FROM regress_user2;
+
+CREATE SCHEMA testns3;
+
+SELECT has_schema_privilege('regress_user2', 'testns3', 'USAGE'); -- no
+SELECT has_schema_privilege('regress_user2', 'testns3', 'CREATE'); -- no
+
+ALTER DEFAULT PRIVILEGES GRANT ALL ON SCHEMAS TO regress_user2;
+
+CREATE SCHEMA testns4;
+
+SELECT has_schema_privilege('regress_user2', 'testns4', 'USAGE'); -- yes
+SELECT has_schema_privilege('regress_user2', 'testns4', 'CREATE'); -- yes
+
+ALTER DEFAULT PRIVILEGES REVOKE ALL ON SCHEMAS FROM regress_user2;
+
+CREATE SCHEMA testns5;
+
+SELECT has_schema_privilege('regress_user2', 'testns5', 'USAGE'); -- no
+SELECT has_schema_privilege('regress_user2', 'testns5', 'CREATE'); -- no
+
 SET ROLE regress_user1;
 
 CREATE FUNCTION testns.foo() RETURNS int AS 'select 1' LANGUAGE sql;
@@ -853,6 +883,10 @@ SELECT count(*)
   WHERE nspname = 'testns';
 
 DROP SCHEMA testns CASCADE;
+DROP SCHEMA testns2 CASCADE;
+DROP SCHEMA testns3 CASCADE;
+DROP SCHEMA testns4 CASCADE;
+DROP SCHEMA testns5 CASCADE;
 
 SELECT d.*     -- check that entries went away
   FROM pg_default_acl d LEFT JOIN pg_namespace n ON defaclnamespace = n.oid