[PATCH] Fix that NOSUPERUSER implies REPLICATION unless specified contrarily - Mailing list pgsql-hackers

Also add some regression tests for that behaviour.

Found after seing a report about it in IRC by Daniel Grace.
---src/backend/commands/user.c              |    3 +-src/test/regress/expected/privileges.out |   35
++++++++++++++++++++++++++++src/test/regress/sql/privileges.sql     |   37 ++++++++++++++++++++++++++++++3 files
changed,74 insertions(+), 1 deletions(-)
 

diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index f13eb28..f917184 100644
--- a/src/backend/commands/user.c
+++ b/src/backend/commands/user.c
@@ -244,7 +244,8 @@ CreateRole(CreateRoleStmt *stmt)         * Superusers get replication by default, but only if
 * NOREPLICATION wasn't explicitly mentioned         */
 
-        if (!(disreplication && intVal(disreplication->arg) == 0))
+        if (issuper &&
+            !(disreplication && intVal(disreplication->arg) == 0))            isreplication = 1;    }    if
(dinherit)
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index 5cda230..11aaa3e 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -12,6 +12,7 @@ DROP ROLE IF EXISTS regressuser3;DROP ROLE IF EXISTS regressuser4;DROP ROLE IF EXISTS
regressuser5;DROPROLE IF EXISTS regressuser6;
 
+DROP ROLE IF EXISTS regressusercreaterole;SELECT lo_unlink(oid) FROM pg_largeobject_metadata; lo_unlink -----------
@@ -26,6 +27,7 @@ CREATE USER regressuser4;CREATE USER regressuser5;CREATE USER regressuser5;    -- duplicateERROR:
role"regressuser5" already exists
 
+CREATE USER regressusercreaterole CREATEROLE;CREATE GROUP regressgroup1;CREATE GROUP regressgroup2 WITH USER
regressuser1,regressuser2;ALTER GROUP regressgroup1 ADD USER regressuser4;
 
@@ -1216,6 +1218,36 @@ SELECT has_function_privilege('regressuser1', 'testns.testfunc(int)', 'EXECUTE')SET
client_min_messagesTO 'warning';DROP SCHEMA testns CASCADE;RESET client_min_messages;
 
+-- CREATEROLE/SUPERUSER/REPLICATION tests
+\c
+CREATE USER regressuser7 SUPERUSER;
+DROP USER regressuser7;
+CREATE USER regressuser7 NOSUPERUSER;
+DROP USER regressuser7;
+CREATE USER regressuser7 SUPERUSER NOREPLICATION;
+DROP USER regressuser7;
+SET SESSION AUTHORIZATION regressuser1;
+CREATE USER regressuser7;  --fail
+ERROR:  permission denied to create role
+DROP USER regressuser7;  --fail
+ERROR:  permission denied to drop role
+SET SESSION AUTHORIZATION regressusercreaterole;
+CREATE USER regressuser7 SUPERUSER;  --fail
+ERROR:  must be superuser to create superusers
+DROP USER regressuser7; --fail
+ERROR:  role "regressuser7" does not exist
+CREATE USER regressuser7 NOSUPERUSER;
+DROP USER regressuser7;
+CREATE USER regressuser7 CREATEROLE;
+DROP USER regressuser7;
+CREATE USER regressuser7 NOSUPERUSER NOREPLICATION NOCREATEROLE;
+DROP USER regressuser7;
+CREATE USER regressuser7 REPLICATION;  --fail
+ERROR:  must be superuser to create replication users
+DROP USER regressuser7; --fail
+ERROR:  role "regressuser7" does not exist
+CREATE USER regressuser7 NOREPLICATION;
+DROP USER regressuser7;-- clean up\cdrop sequence x_seq;
@@ -1260,3 +1292,6 @@ DROP USER regressuser4;DROP USER regressuser5;DROP USER regressuser6;ERROR:  role "regressuser6"
doesnot exist
 
+DROP USER regressuser7;
+ERROR:  role "regressuser7" does not exist
+DROP USER regressusercreaterole;
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index a87ce77..d01455f 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -16,6 +16,7 @@ DROP ROLE IF EXISTS regressuser3;DROP ROLE IF EXISTS regressuser4;DROP ROLE IF EXISTS
regressuser5;DROPROLE IF EXISTS regressuser6;
 
+DROP ROLE IF EXISTS regressusercreaterole;SELECT lo_unlink(oid) FROM pg_largeobject_metadata;
@@ -29,6 +30,7 @@ CREATE USER regressuser3;CREATE USER regressuser4;CREATE USER regressuser5;CREATE USER regressuser5;
 -- duplicate
 
+CREATE USER regressusercreaterole CREATEROLE;CREATE GROUP regressgroup1;CREATE GROUP regressgroup2 WITH USER
regressuser1,regressuser2;
 
@@ -670,6 +672,39 @@ SET client_min_messages TO 'warning';DROP SCHEMA testns CASCADE;RESET client_min_messages;
+-- CREATEROLE/SUPERUSER/REPLICATION tests
+\c
+CREATE USER regressuser7 SUPERUSER;
+DROP USER regressuser7;
+
+CREATE USER regressuser7 NOSUPERUSER;
+DROP USER regressuser7;
+
+CREATE USER regressuser7 SUPERUSER NOREPLICATION;
+DROP USER regressuser7;
+
+SET SESSION AUTHORIZATION regressuser1;
+CREATE USER regressuser7;  --fail
+DROP USER regressuser7;  --fail
+
+SET SESSION AUTHORIZATION regressusercreaterole;
+CREATE USER regressuser7 SUPERUSER;  --fail
+DROP USER regressuser7; --fail
+
+CREATE USER regressuser7 NOSUPERUSER;
+DROP USER regressuser7;
+
+CREATE USER regressuser7 CREATEROLE;
+DROP USER regressuser7;
+
+CREATE USER regressuser7 NOSUPERUSER NOREPLICATION NOCREATEROLE;
+DROP USER regressuser7;
+
+CREATE USER regressuser7 REPLICATION;  --fail
+DROP USER regressuser7; --fail
+
+CREATE USER regressuser7 NOREPLICATION;
+DROP USER regressuser7;-- clean up
@@ -712,3 +747,5 @@ DROP USER regressuser3;DROP USER regressuser4;DROP USER regressuser5;DROP USER regressuser6;
+DROP USER regressuser7;
+DROP USER regressusercreaterole;
-- 
1.7.5.rc1.16.g9db1.dirty