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

[Robert Haas]

On Tue, Apr 12, 2011 at 4:14 PM, Andres Freund and...@anarazel.de wrote:
 Also add some regression tests for that behaviour.

 Found after seing a report about it in IRC by Daniel Grace.

This patch didn't apply cleanly for me, but I committed the basic fix.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

[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