Create an ALTER DEFAULT PRIVILEGES command, which allows users to adjust

the privileges that will be applied to subsequently-created objects.

Such adjustments are always per owning role, and can be restricted to objects
created in particular schemas too.  A notable benefit is that users can
override the traditional default privilege settings, eg, the PUBLIC EXECUTE
privilege traditionally granted by default for functions.

Petr Jelinek

3 files changed, 185 insertions, 1 deletions

diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index 809b6562171..24239276dbf 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -836,6 +836,117 @@ SELECT has_sequence_privilege('x_seq', 'USAGE');
  t
 (1 row)
 
+-- test default ACLs
+\c -
+CREATE SCHEMA testns;
+GRANT ALL ON SCHEMA testns TO regressuser1;
+CREATE TABLE testns.acltest1 (x int);
+SELECT has_table_privilege('regressuser1', 'testns.acltest1', 'SELECT'); -- no
+ has_table_privilege 
+---------------------
+ f
+(1 row)
+
+SELECT has_table_privilege('regressuser1', 'testns.acltest1', 'INSERT'); -- no
+ has_table_privilege 
+---------------------
+ f
+(1 row)
+
+ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT SELECT ON TABLE TO public;
+SELECT has_table_privilege('regressuser1', 'testns.acltest1', 'SELECT'); -- no
+ has_table_privilege 
+---------------------
+ f
+(1 row)
+
+SELECT has_table_privilege('regressuser1', 'testns.acltest1', 'INSERT'); -- no
+ has_table_privilege 
+---------------------
+ f
+(1 row)
+
+DROP TABLE testns.acltest1;
+CREATE TABLE testns.acltest1 (x int);
+SELECT has_table_privilege('regressuser1', 'testns.acltest1', 'SELECT'); -- yes
+ has_table_privilege 
+---------------------
+ t
+(1 row)
+
+SELECT has_table_privilege('regressuser1', 'testns.acltest1', 'INSERT'); -- no
+ has_table_privilege 
+---------------------
+ f
+(1 row)
+
+ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT INSERT ON TABLE TO regressuser1;
+DROP TABLE testns.acltest1;
+CREATE TABLE testns.acltest1 (x int);
+SELECT has_table_privilege('regressuser1', 'testns.acltest1', 'SELECT'); -- yes
+ has_table_privilege 
+---------------------
+ t
+(1 row)
+
+SELECT has_table_privilege('regressuser1', 'testns.acltest1', 'INSERT'); -- yes
+ has_table_privilege 
+---------------------
+ t
+(1 row)
+
+ALTER DEFAULT PRIVILEGES IN SCHEMA testns REVOKE INSERT ON TABLE FROM regressuser1;
+DROP TABLE testns.acltest1;
+CREATE TABLE testns.acltest1 (x int);
+SELECT has_table_privilege('regressuser1', 'testns.acltest1', 'SELECT'); -- yes
+ has_table_privilege 
+---------------------
+ t
+(1 row)
+
+SELECT has_table_privilege('regressuser1', 'testns.acltest1', 'INSERT'); -- no
+ has_table_privilege 
+---------------------
+ f
+(1 row)
+
+ALTER DEFAULT PRIVILEGES FOR ROLE regressuser1 REVOKE EXECUTE ON FUNCTION FROM public;
+SET ROLE regressuser1;
+CREATE FUNCTION testns.foo() RETURNS int AS 'select 1' LANGUAGE sql;
+SELECT has_function_privilege('regressuser2', 'testns.foo()', 'EXECUTE'); -- no
+ has_function_privilege 
+------------------------
+ f
+(1 row)
+
+ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT EXECUTE ON FUNCTION to public;
+DROP FUNCTION testns.foo();
+CREATE FUNCTION testns.foo() RETURNS int AS 'select 1' LANGUAGE sql;
+SELECT has_function_privilege('regressuser2', 'testns.foo()', 'EXECUTE'); -- yes
+ has_function_privilege 
+------------------------
+ t
+(1 row)
+
+DROP FUNCTION testns.foo();
+RESET ROLE;
+SELECT count(*)
+  FROM pg_default_acl d LEFT JOIN pg_namespace n ON defaclnamespace = n.oid
+  WHERE nspname = 'testns';
+ count 
+-------
+     2
+(1 row)
+
+DROP SCHEMA testns CASCADE;
+NOTICE:  drop cascades to table testns.acltest1
+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;
+ defaclrole | defaclnamespace | defaclobjtype | defaclacl 
+------------+-----------------+---------------+-----------
+(0 rows)
+
 -- clean up
 \c
 drop sequence x_seq;
@@ -860,7 +971,9 @@ DROP TABLE atestp1;
 DROP TABLE atestp2;
 DROP GROUP regressgroup1;
 DROP GROUP regressgroup2;
+-- these are needed to clean up permissions
 REVOKE USAGE ON LANGUAGE sql FROM regressuser1;
+DROP OWNED BY regressuser1;
 DROP USER regressuser1;
 DROP USER regressuser2;
 DROP USER regressuser3;
diff --git a/src/test/regress/expected/sanity_check.out b/src/test/regress/expected/sanity_check.out
index 7213192d5f3..1994edc905e 100644
--- a/src/test/regress/expected/sanity_check.out
+++ b/src/test/regress/expected/sanity_check.out
@@ -95,6 +95,7 @@ SELECT relname, relhasindex
  pg_constraint           | t
  pg_conversion           | t
  pg_database             | t
+ pg_default_acl          | t
  pg_depend               | t
  pg_description          | t
  pg_enum                 | t
@@ -151,7 +152,7 @@ SELECT relname, relhasindex
  timetz_tbl              | f
  tinterval_tbl           | f
  varchar_tbl             | f
-(140 rows)
+(141 rows)
 
 --
 -- another sanity check: every system catalog that has OIDs should have
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index 917e8e5da4e..eaa879efa22 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -484,6 +484,73 @@ SET SESSION AUTHORIZATION regressuser2;
 
 SELECT has_sequence_privilege('x_seq', 'USAGE');
 
+
+-- test default ACLs
+\c -
+
+CREATE SCHEMA testns;
+GRANT ALL ON SCHEMA testns TO regressuser1;
+
+CREATE TABLE testns.acltest1 (x int);
+SELECT has_table_privilege('regressuser1', 'testns.acltest1', 'SELECT'); -- no
+SELECT has_table_privilege('regressuser1', 'testns.acltest1', 'INSERT'); -- no
+
+ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT SELECT ON TABLE TO public;
+
+SELECT has_table_privilege('regressuser1', 'testns.acltest1', 'SELECT'); -- no
+SELECT has_table_privilege('regressuser1', 'testns.acltest1', 'INSERT'); -- no
+
+DROP TABLE testns.acltest1;
+CREATE TABLE testns.acltest1 (x int);
+
+SELECT has_table_privilege('regressuser1', 'testns.acltest1', 'SELECT'); -- yes
+SELECT has_table_privilege('regressuser1', 'testns.acltest1', 'INSERT'); -- no
+
+ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT INSERT ON TABLE TO regressuser1;
+
+DROP TABLE testns.acltest1;
+CREATE TABLE testns.acltest1 (x int);
+
+SELECT has_table_privilege('regressuser1', 'testns.acltest1', 'SELECT'); -- yes
+SELECT has_table_privilege('regressuser1', 'testns.acltest1', 'INSERT'); -- yes
+
+ALTER DEFAULT PRIVILEGES IN SCHEMA testns REVOKE INSERT ON TABLE FROM regressuser1;
+
+DROP TABLE testns.acltest1;
+CREATE TABLE testns.acltest1 (x int);
+
+SELECT has_table_privilege('regressuser1', 'testns.acltest1', 'SELECT'); -- yes
+SELECT has_table_privilege('regressuser1', 'testns.acltest1', 'INSERT'); -- no
+
+ALTER DEFAULT PRIVILEGES FOR ROLE regressuser1 REVOKE EXECUTE ON FUNCTION FROM public;
+
+SET ROLE regressuser1;
+
+CREATE FUNCTION testns.foo() RETURNS int AS 'select 1' LANGUAGE sql;
+
+SELECT has_function_privilege('regressuser2', 'testns.foo()', 'EXECUTE'); -- no
+
+ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT EXECUTE ON FUNCTION to public;
+
+DROP FUNCTION testns.foo();
+CREATE FUNCTION testns.foo() RETURNS int AS 'select 1' LANGUAGE sql;
+
+SELECT has_function_privilege('regressuser2', 'testns.foo()', 'EXECUTE'); -- yes
+
+DROP FUNCTION testns.foo();
+
+RESET ROLE;
+
+SELECT count(*)
+  FROM pg_default_acl d LEFT JOIN pg_namespace n ON defaclnamespace = n.oid
+  WHERE nspname = 'testns';
+
+DROP SCHEMA testns 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;
+
 -- clean up
 
 \c
@@ -513,7 +580,10 @@ DROP TABLE atestp2;
 DROP GROUP regressgroup1;
 DROP GROUP regressgroup2;
 
+-- these are needed to clean up permissions
 REVOKE USAGE ON LANGUAGE sql FROM regressuser1;
+DROP OWNED BY regressuser1;
+
 DROP USER regressuser1;
 DROP USER regressuser2;
 DROP USER regressuser3;