[HACKERS] [PATCH] Fix that NOSUPERUSER implies REPLICATION unless specified contrarily

[Andres Freund]

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;
 DROP ROLE 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;      -- duplicate
 ERROR:  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_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
+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
 \c
 drop sequence x_seq;
@@ -1260,3 +1292,6 @@ DROP USER regressuser4;
 DROP USER regressuser5;
 DROP USER regressuser6;
 ERROR:  role "regressuser6" does not 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;
 DROP ROLE 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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers