Patch to add regression tests for SCHEMA

Started by robinsalmost 13 years ago20 messages

robins

tharakan@gmail.com

almost 13 years ago

1 attachment(s)

Hi,

Please find attached a patch to take 'make check' code-coverage of SCHEMA
from 33% to 98%.

Any feedback is more than welcome.

p.s.: I am currently working on more regression tests (USER / VIEW /
DISCARD etc). Please let me know if I need to post these as one large
patch, instead of submitting one patch at a time.
--
Robins
Tharakan

Attachments:

regress-schema.patchapplication/octet-stream; name=regress-schema.patchDownload

From 7a46d51d8338c686d61f4cc5c700a23c83cae07a Mon Sep 17 00:00:00 2001
From: Robins Tharakan <robins@pobox.com>
Date: Sat, 16 Mar 2013 04:20:46 +0530
Subject: [PATCH] Add regression tests for SCHEMA

---
 src/test/regress/expected/schema.out |  144 ++++++++++++++++++++++++++++++++++
 src/test/regress/parallel_schedule   |    2 +-
 src/test/regress/sql/schema.sql      |  142 +++++++++++++++++++++++++++++++++
 3 files changed, 287 insertions(+), 1 deletion(-)
 create mode 100644 src/test/regress/expected/schema.out
 create mode 100644 src/test/regress/sql/schema.sql

diff --git a/src/test/regress/expected/schema.out b/src/test/regress/expected/schema.out
new file mode 100644
index 0000000..d506011
--- /dev/null
+++ b/src/test/regress/expected/schema.out
@@ -0,0 +1,144 @@
+--
+-- SCHEMA Commands
+--
+CREATE SCHEMA t1;
+DROP SCHEMA t1;
+-- Should fail. Disallow CREATE SCHEMA by ROLE with insufficient permission
+CREATE ROLE r1;
+SET ROLE r1;
+CREATE SCHEMA t2;
+ERROR:  permission denied for database regression
+RESET ROLE;
+DROP ROLE r1;
+-- Should fail. Disallow CREATE SCHEMA if using a reserved name
+CREATE SCHEMA pg_asdf;
+ERROR:  unacceptable schema name "pg_asdf"
+DETAIL:  The prefix "pg_" is reserved for system schemas.
+-- Should fail. Disallow CREATE SCHEMA if already exists
+CREATE SCHEMA public;
+ERROR:  schema "public" already exists
+-- Ensure CREATE SCHEMA uses current_user (and not necessarily session_user)
+CREATE ROLE r1;
+GRANT CREATE ON DATABASE regression to r1;
+SET ROLE r1;
+CREATE SCHEMA t2;
+SELECT schema_owner
+FROM information_schema.schemata
+WHERE schema_name <> 'information_schema'
+  AND schema_name !~ E'^pg_';
+ schema_owner 
+--------------
+ r1
+(1 row)
+
+DROP SCHEMA t2;
+RESET ROLE;
+REVOKE CREATE ON DATABASE regression FROM r1;
+DROP ROLE r1;
+-- RENAME SCHEMA
+CREATE SCHEMA s1;
+ALTER SCHEMA s1 RENAME TO ss1;
+DROP SCHEMA ss1;
+-- ALTER SCHEMA ok for user created IN ROLE of one with CREATE DATABASE rights
+CREATE ROLE r3;
+GRANT CREATE ON DATABASE regression to r3;
+CREATE ROLE r4 IN ROLE r3;
+SET ROLE r3;
+CREATE SCHEMA s2;
+SET ROLE r4;
+ALTER SCHEMA s2 RENAME TO s2a;
+ALTER SCHEMA s2a OWNER TO r4;
+DROP SCHEMA s2a;
+RESET ROLE;
+REVOKE CREATE ON DATABASE regression FROM r3;
+SET ROLE r3;
+RESET ROLE;
+DROP ROLE r4;
+DROP ROLE r3;
+-- Should work, REASSIGN OWNED objects to another OWNER
+CREATE ROLE r3;
+GRANT CREATE ON DATABASE regression to r3;
+CREATE ROLE r4;
+SET ROLE r3;
+CREATE SCHEMA s2;
+RESET ROLE;
+REVOKE CREATE ON DATABASE regression FROM r3;
+REASSIGN OWNED BY r3 TO r4;
+SET ROLE r4;
+DROP SCHEMA s2;
+RESET ROLE;
+DROP ROLE r4;
+DROP ROLE r3;
+-- Should fail. Shouldn't RENAME SCHEMA if invalid / already existing / etc.
+CREATE SCHEMA s1;
+CREATE SCHEMA s2;
+ALTER SCHEMA s2 RENAME TO s1;
+ERROR:  schema "s1" already exists
+ALTER SCHEMA s2 RENAME TO public;
+ERROR:  schema "public" already exists
+ALTER SCHEMA s2 RENAME TO pg_asdf;
+ERROR:  unacceptable schema name "pg_asdf"
+DETAIL:  The prefix "pg_" is reserved for system schemas.
+DROP SCHEMA s1;
+DROP SCHEMA s2;
+-- Should fail. Shouldn't ALTER SCHEMA if not OWNER
+CREATE SCHEMA s1;
+CREATE ROLE r1;
+SET ROLE r1;
+ALTER SCHEMA s1 RENAME TO ss1;
+ERROR:  must be owner of schema s1
+ALTER SCHEMA s1 OWNER TO r1;
+ERROR:  must be owner of schema s1
+RESET ROLE;
+DROP SCHEMA s1;
+DROP ROLE r1;
+-- Should work. Non-Owner with CREATE ON DATABASE priviledge can RENAME SCHEMA
+CREATE ROLE r1;
+GRANT CREATE ON DATABASE regression to r1;
+SET ROLE r1;
+CREATE SCHEMA s1;
+RESET ROLE;
+ALTER SCHEMA s1 RENAME TO ss1;
+DROP SCHEMA ss1;
+REVOKE CREATE ON DATABASE regression FROM r1;
+DROP ROLE r1;
+-- Should fail. OWNER without CREATE ON DATABASE can't ALTER OWNER SCHEMA
+CREATE ROLE r1;
+CREATE SCHEMA s1 AUTHORIZATION r1;
+CREATE ROLE r2;
+GRANT r2 TO r1;
+SET ROLE r1;
+ALTER SCHEMA s1 RENAME TO ss1;
+ERROR:  permission denied for database regression
+ALTER SCHEMA s1 OWNER TO r2;
+ERROR:  permission denied for database regression
+RESET ROLE;
+DROP SCHEMA s1;
+REVOKE r2 FROM r1;
+DROP ROLE r2;
+DROP ROLE r1;
+-- Should work. Try to have multiple OWNERships for a ROLE
+CREATE ROLE r1;
+CREATE ROLE r2;
+CREATE SCHEMA s1;
+GRANT CREATE ON SCHEMA s1 TO r1;
+GRANT ALL ON SCHEMA s1 TO r2;
+ALTER SCHEMA s1 OWNER TO r1;
+REVOKE CREATE ON SCHEMA s1 FROM r1;
+REVOKE ALL ON SCHEMA s1 FROM r2;
+DROP SCHEMA s1;
+DROP ROLE r2;
+DROP ROLE r1;
+-- Change OWNER of SCHEMA
+CREATE SCHEMA s1;
+CREATE ROLE r1;
+ALTER SCHEMA s1 OWNER TO r1;
+DROP SCHEMA s1;
+DROP ROLE r1;
+-- Should fail. Can't change OWNER of SCHEMA if doesn't exist/invalid name/etc.
+ALTER SCHEMA s1 RENAME TO ss1;
+ERROR:  schema "s1" does not exist
+CREATE ROLE r1;
+ALTER SCHEMA s1 OWNER TO r1;
+ERROR:  schema "s1" does not exist
+DROP ROLE r1;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 2af28b1..7d23aaa 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -78,7 +78,7 @@ ignore: random
 # ----------
 # Another group of parallel tests
 # ----------
-test: select_into select_distinct select_distinct_on select_implicit select_having subselect union case join aggregates transactions random portals arrays btree_index hash_index update namespace prepared_xacts delete
+test: select_into select_distinct select_distinct_on select_implicit select_having subselect union case join aggregates transactions random portals arrays btree_index hash_index update namespace prepared_xacts delete schema
 
 # ----------
 # Another group of parallel tests
diff --git a/src/test/regress/sql/schema.sql b/src/test/regress/sql/schema.sql
new file mode 100644
index 0000000..b7150ff
--- /dev/null
+++ b/src/test/regress/sql/schema.sql
@@ -0,0 +1,142 @@
+--
+-- SCHEMA Commands
+--
+
+CREATE SCHEMA t1;
+DROP SCHEMA t1;
+
+-- Should fail. Disallow CREATE SCHEMA by ROLE with insufficient permission
+CREATE ROLE r1;
+SET ROLE r1;
+CREATE SCHEMA t2;
+RESET ROLE;
+DROP ROLE r1;
+
+-- Should fail. Disallow CREATE SCHEMA if using a reserved name
+CREATE SCHEMA pg_asdf;
+
+-- Should fail. Disallow CREATE SCHEMA if already exists
+CREATE SCHEMA public;
+
+-- Ensure CREATE SCHEMA uses current_user (and not necessarily session_user)
+CREATE ROLE r1;
+GRANT CREATE ON DATABASE regression to r1;
+SET ROLE r1;
+CREATE SCHEMA t2;
+SELECT schema_owner
+FROM information_schema.schemata
+WHERE schema_name <> 'information_schema'
+  AND schema_name !~ E'^pg_';
+DROP SCHEMA t2;
+RESET ROLE;
+REVOKE CREATE ON DATABASE regression FROM r1;
+DROP ROLE r1;
+
+-- RENAME SCHEMA
+CREATE SCHEMA s1;
+ALTER SCHEMA s1 RENAME TO ss1;
+DROP SCHEMA ss1;
+
+-- ALTER SCHEMA ok for user created IN ROLE of one with CREATE DATABASE rights
+CREATE ROLE r3;
+GRANT CREATE ON DATABASE regression to r3;
+CREATE ROLE r4 IN ROLE r3;
+SET ROLE r3;
+CREATE SCHEMA s2;
+SET ROLE r4;
+ALTER SCHEMA s2 RENAME TO s2a;
+ALTER SCHEMA s2a OWNER TO r4;
+DROP SCHEMA s2a;
+RESET ROLE;
+REVOKE CREATE ON DATABASE regression FROM r3;
+SET ROLE r3;
+RESET ROLE;
+DROP ROLE r4;
+DROP ROLE r3;
+
+-- Should work, REASSIGN OWNED objects to another OWNER
+CREATE ROLE r3;
+GRANT CREATE ON DATABASE regression to r3;
+CREATE ROLE r4;
+SET ROLE r3;
+CREATE SCHEMA s2;
+RESET ROLE;
+REVOKE CREATE ON DATABASE regression FROM r3;
+REASSIGN OWNED BY r3 TO r4;
+SET ROLE r4;
+DROP SCHEMA s2;
+RESET ROLE;
+DROP ROLE r4;
+DROP ROLE r3;
+
+-- Should fail. Shouldn't RENAME SCHEMA if invalid / already existing / etc.
+CREATE SCHEMA s1;
+CREATE SCHEMA s2;
+ALTER SCHEMA s2 RENAME TO s1;
+ALTER SCHEMA s2 RENAME TO public;
+ALTER SCHEMA s2 RENAME TO pg_asdf;
+DROP SCHEMA s1;
+DROP SCHEMA s2;
+
+-- Should fail. Shouldn't ALTER SCHEMA if not OWNER
+CREATE SCHEMA s1;
+CREATE ROLE r1;
+SET ROLE r1;
+ALTER SCHEMA s1 RENAME TO ss1;
+ALTER SCHEMA s1 OWNER TO r1;
+RESET ROLE;
+DROP SCHEMA s1;
+DROP ROLE r1;
+
+-- Should work. Non-Owner with CREATE ON DATABASE priviledge can RENAME SCHEMA
+CREATE ROLE r1;
+GRANT CREATE ON DATABASE regression to r1;
+SET ROLE r1;
+CREATE SCHEMA s1;
+RESET ROLE;
+ALTER SCHEMA s1 RENAME TO ss1;
+DROP SCHEMA ss1;
+REVOKE CREATE ON DATABASE regression FROM r1;
+DROP ROLE r1;
+
+-- Should fail. OWNER without CREATE ON DATABASE can't ALTER OWNER SCHEMA
+CREATE ROLE r1;
+CREATE SCHEMA s1 AUTHORIZATION r1;
+CREATE ROLE r2;
+GRANT r2 TO r1;
+SET ROLE r1;
+ALTER SCHEMA s1 RENAME TO ss1;
+ALTER SCHEMA s1 OWNER TO r2;
+RESET ROLE;
+DROP SCHEMA s1;
+REVOKE r2 FROM r1;
+DROP ROLE r2;
+DROP ROLE r1;
+
+-- Should work. Try to have multiple OWNERships for a ROLE
+CREATE ROLE r1;
+CREATE ROLE r2;
+CREATE SCHEMA s1;
+GRANT CREATE ON SCHEMA s1 TO r1;
+GRANT ALL ON SCHEMA s1 TO r2;
+ALTER SCHEMA s1 OWNER TO r1;
+REVOKE CREATE ON SCHEMA s1 FROM r1;
+REVOKE ALL ON SCHEMA s1 FROM r2;
+DROP SCHEMA s1;
+DROP ROLE r2;
+DROP ROLE r1;
+
+-- Change OWNER of SCHEMA
+CREATE SCHEMA s1;
+CREATE ROLE r1;
+ALTER SCHEMA s1 OWNER TO r1;
+DROP SCHEMA s1;
+DROP ROLE r1;
+
+-- Should fail. Can't change OWNER of SCHEMA if doesn't exist/invalid name/etc.
+ALTER SCHEMA s1 RENAME TO ss1;
+CREATE ROLE r1;
+ALTER SCHEMA s1 OWNER TO r1;
+DROP ROLE r1;
+
+
-- 
1.7.10.4

Alvaro Herrera

alvherre@2ndquadrant.com

almost 13 years ago

In reply to: robins (#1)

Re: Patch to add regression tests for SCHEMA

robins escribió:

Hi,

Please find attached a patch to take 'make check' code-coverage of SCHEMA
from 33% to 98%.

Any feedback is more than welcome.

I think you should use more explicit names for shared objects such as
roles -- i.e. not "r1" but "regression_user_1" and so on. (But be
careful about role names used by other tests). The issue is that these
tests might be run in a database that contains other stuff; certainly we
don't want to drop or otherwise affect previously existing roles.

p.s.: I am currently working on more regression tests (USER / VIEW /
DISCARD etc). Please let me know if I need to post these as one large
patch, instead of submitting one patch at a time.

I think separate patches is better. Are you adding these patches to the
upcoming commitfest, for evaluation? https://commitfest.postgresql.org

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

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

robins

tharakan@gmail.com

almost 13 years ago

In reply to: Alvaro Herrera (#2)

Re: Patch to add regression tests for SCHEMA

Thanks Alvaro.

Since the tests were running fine, I didn't bother with elaborate names,
but since you're concerned guess I'll make that change and re-submit.

And yes, I've already submitted (to Commitfest) another patch related to
regression tests for SEQUENCE.
Would submit the SCHEMA patch once the above change is done.
--
Robins

On 18 March 2013 09:42, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:

Show quoted text

robins escribió:

Hi,

Please find attached a patch to take 'make check' code-coverage of SCHEMA
from 33% to 98%.

Any feedback is more than welcome.

I think you should use more explicit names for shared objects such as
roles -- i.e. not "r1" but "regression_user_1" and so on. (But be
careful about role names used by other tests). The issue is that these
tests might be run in a database that contains other stuff; certainly we
don't want to drop or otherwise affect previously existing roles.

p.s.: I am currently working on more regression tests (USER / VIEW /
DISCARD etc). Please let me know if I need to post these as one large
patch, instead of submitting one patch at a time.

I think separate patches is better. Are you adding these patches to the
upcoming commitfest, for evaluation? https://commitfest.postgresql.org

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

robins

tharakan@gmail.com

almost 13 years ago

In reply to: robins (#3)

1 attachment(s)

Re: Patch to add regression tests for SCHEMA

Hi,

Attached is an updated patch that uses better schema / role names.

--
Robins Tharakan

On 18 March 2013 12:59, robins <tharakan@gmail.com> wrote:

Show quoted text

Thanks Alvaro.

Since the tests were running fine, I didn't bother with elaborate names,
but since you're concerned guess I'll make that change and re-submit.

And yes, I've already submitted (to Commitfest) another patch related to
regression tests for SEQUENCE.
Would submit the SCHEMA patch once the above change is done.
--
Robins

On 18 March 2013 09:42, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:

robins escribió:

Hi,

Please find attached a patch to take 'make check' code-coverage of

SCHEMA

from 33% to 98%.

Any feedback is more than welcome.

I think you should use more explicit names for shared objects such as
roles -- i.e. not "r1" but "regression_user_1" and so on. (But be
careful about role names used by other tests). The issue is that these
tests might be run in a database that contains other stuff; certainly we
don't want to drop or otherwise affect previously existing roles.

p.s.: I am currently working on more regression tests (USER / VIEW /
DISCARD etc). Please let me know if I need to post these as one large
patch, instead of submitting one patch at a time.

I think separate patches is better. Are you adding these patches to the
upcoming commitfest, for evaluation? https://commitfest.postgresql.org

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Attachments:

regress_schema_v2.patchapplication/octet-stream; name=regress_schema_v2.patchDownload

From 3d3c8bf658f57cb2dd7d4688f65d769616e01b31 Mon Sep 17 00:00:00 2001
From: Robins Tharakan <robins@pobox.com>
Date: Sat, 16 Mar 2013 04:20:46 +0530
Subject: [PATCH] Add regression tests for SCHEMA

---
 src/test/regress/expected/schema.out |  144 ++++++++++++++++++++++++++++++++++
 src/test/regress/parallel_schedule   |    2 +-
 src/test/regress/sql/schema.sql      |  141 +++++++++++++++++++++++++++++++++
 3 files changed, 286 insertions(+), 1 deletion(-)
 create mode 100644 src/test/regress/expected/schema.out
 create mode 100644 src/test/regress/sql/schema.sql

diff --git a/src/test/regress/expected/schema.out b/src/test/regress/expected/schema.out
new file mode 100644
index 0000000..8a87337
--- /dev/null
+++ b/src/test/regress/expected/schema.out
@@ -0,0 +1,144 @@
+--
+-- SCHEMA Commands
+--
+CREATE SCHEMA schema_sch1;
+DROP SCHEMA schema_sch1;
+-- Should fail. Disallow CREATE SCHEMA by ROLE with insufficient permission
+CREATE ROLE role_sch1;
+SET ROLE role_sch1;
+CREATE SCHEMA schema_sch2;
+ERROR:  permission denied for database regression
+RESET ROLE;
+DROP ROLE role_sch1;
+-- Should fail. Disallow CREATE SCHEMA if using a reserved name
+CREATE SCHEMA pg_asdf;
+ERROR:  unacceptable schema name "pg_asdf"
+DETAIL:  The prefix "pg_" is reserved for system schemas.
+-- Should fail. Disallow CREATE SCHEMA if already exists
+CREATE SCHEMA public;
+ERROR:  schema "public" already exists
+-- Ensure CREATE SCHEMA uses current_user (and not necessarily session_user)
+CREATE ROLE role_sch1;
+GRANT CREATE ON DATABASE regression to role_sch1;
+SET ROLE role_sch1;
+CREATE SCHEMA schema_sch2;
+SELECT schema_owner
+FROM information_schema.schemata
+WHERE schema_name <> 'information_schema'
+  AND schema_name !~ E'^pg_';
+ schema_owner 
+--------------
+ role_sch1
+(1 row)
+
+DROP SCHEMA schema_sch2;
+RESET ROLE;
+REVOKE CREATE ON DATABASE regression FROM role_sch1;
+DROP ROLE role_sch1;
+-- RENAME SCHEMA
+CREATE SCHEMA schema_sch1;
+ALTER SCHEMA schema_sch1 RENAME TO schema_sch2;
+DROP SCHEMA schema_sch2;
+-- ALTER SCHEMA ok for user created IN ROLE of one with CREATE DATABASE rights
+CREATE ROLE role_sch3;
+GRANT CREATE ON DATABASE regression to role_sch3;
+CREATE ROLE role_sch4 IN ROLE role_sch3;
+SET ROLE role_sch3;
+CREATE SCHEMA schema_sch3;
+SET ROLE role_sch4;
+ALTER SCHEMA schema_sch3 RENAME TO schema_sch5;
+ALTER SCHEMA schema_sch5 OWNER TO role_sch4;
+DROP SCHEMA schema_sch5;
+RESET ROLE;
+REVOKE CREATE ON DATABASE regression FROM role_sch3;
+SET ROLE role_sch3;
+RESET ROLE;
+DROP ROLE role_sch4;
+DROP ROLE role_sch3;
+-- Should work, REASSIGN OWNED objects to another OWNER
+CREATE ROLE role_sch3;
+GRANT CREATE ON DATABASE regression to role_sch3;
+CREATE ROLE role_sch4;
+SET ROLE role_sch3;
+CREATE SCHEMA schema_sch3;
+RESET ROLE;
+REVOKE CREATE ON DATABASE regression FROM role_sch3;
+REASSIGN OWNED BY role_sch3 TO role_sch4;
+SET ROLE role_sch4;
+DROP SCHEMA schema_sch3;
+RESET ROLE;
+DROP ROLE role_sch4;
+DROP ROLE role_sch3;
+-- Should fail. Shouldn't RENAME SCHEMA if invalid / already existing / etc.
+CREATE SCHEMA schema_sch1;
+CREATE SCHEMA schema_sch3;
+ALTER SCHEMA schema_sch3 RENAME TO schema_sch1;
+ERROR:  schema "schema_sch1" already exists
+ALTER SCHEMA schema_sch3 RENAME TO public;
+ERROR:  schema "public" already exists
+ALTER SCHEMA schema_sch3 RENAME TO pg_asdf;
+ERROR:  unacceptable schema name "pg_asdf"
+DETAIL:  The prefix "pg_" is reserved for system schemas.
+DROP SCHEMA schema_sch1;
+DROP SCHEMA schema_sch3;
+-- Should fail. Shouldn't ALTER SCHEMA if not OWNER
+CREATE SCHEMA schema_sch1;
+CREATE ROLE role_sch1;
+SET ROLE role_sch1;
+ALTER SCHEMA schema_sch1 RENAME TO schema_sch2;
+ERROR:  must be owner of schema schema_sch1
+ALTER SCHEMA schema_sch1 OWNER TO role_sch1;
+ERROR:  must be owner of schema schema_sch1
+RESET ROLE;
+DROP SCHEMA schema_sch1;
+DROP ROLE role_sch1;
+-- Should work. Non-Owner with CREATE ON DATABASE priviledge can RENAME SCHEMA
+CREATE ROLE role_sch1;
+GRANT CREATE ON DATABASE regression to role_sch1;
+SET ROLE role_sch1;
+CREATE SCHEMA schema_sch1;
+RESET ROLE;
+ALTER SCHEMA schema_sch1 RENAME TO schema_sch2;
+DROP SCHEMA schema_sch2;
+REVOKE CREATE ON DATABASE regression FROM role_sch1;
+DROP ROLE role_sch1;
+-- Should fail. OWNER without CREATE ON DATABASE can't ALTER OWNER SCHEMA
+CREATE ROLE role_sch1;
+CREATE SCHEMA schema_sch1 AUTHORIZATION role_sch1;
+CREATE ROLE role_sch2;
+GRANT role_sch2 TO role_sch1;
+SET ROLE role_sch1;
+ALTER SCHEMA schema_sch1 RENAME TO schema_sch2;
+ERROR:  permission denied for database regression
+ALTER SCHEMA schema_sch1 OWNER TO role_sch2;
+ERROR:  permission denied for database regression
+RESET ROLE;
+DROP SCHEMA schema_sch1;
+REVOKE role_sch2 FROM role_sch1;
+DROP ROLE role_sch2;
+DROP ROLE role_sch1;
+-- Should work. Try to have multiple OWNERships for a ROLE
+CREATE ROLE role_sch1;
+CREATE ROLE role_sch2;
+CREATE SCHEMA schema_sch1;
+GRANT CREATE ON SCHEMA schema_sch1 TO role_sch1;
+GRANT ALL ON SCHEMA schema_sch1 TO role_sch2;
+ALTER SCHEMA schema_sch1 OWNER TO role_sch1;
+REVOKE CREATE ON SCHEMA schema_sch1 FROM role_sch1;
+REVOKE ALL ON SCHEMA schema_sch1 FROM role_sch2;
+DROP SCHEMA schema_sch1;
+DROP ROLE role_sch2;
+DROP ROLE role_sch1;
+-- Change OWNER of SCHEMA
+CREATE SCHEMA schema_sch1;
+CREATE ROLE role_sch1;
+ALTER SCHEMA schema_sch1 OWNER TO role_sch1;
+DROP SCHEMA schema_sch1;
+DROP ROLE role_sch1;
+-- Should fail. Can't change OWNER of SCHEMA if doesn't exist/invalid name/etc.
+ALTER SCHEMA schema_sch1 RENAME TO schema_sch2;
+ERROR:  schema "schema_sch1" does not exist
+CREATE ROLE role_sch1;
+ALTER SCHEMA schema_sch1 OWNER TO role_sch1;
+ERROR:  schema "schema_sch1" does not exist
+DROP ROLE role_sch1;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 2af28b1..7d23aaa 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -78,7 +78,7 @@ ignore: random
 # ----------
 # Another group of parallel tests
 # ----------
-test: select_into select_distinct select_distinct_on select_implicit select_having subselect union case join aggregates transactions random portals arrays btree_index hash_index update namespace prepared_xacts delete
+test: select_into select_distinct select_distinct_on select_implicit select_having subselect union case join aggregates transactions random portals arrays btree_index hash_index update namespace prepared_xacts delete schema
 
 # ----------
 # Another group of parallel tests
diff --git a/src/test/regress/sql/schema.sql b/src/test/regress/sql/schema.sql
new file mode 100644
index 0000000..0207729
--- /dev/null
+++ b/src/test/regress/sql/schema.sql
@@ -0,0 +1,141 @@
+--
+-- SCHEMA Commands
+--
+
+CREATE SCHEMA schema_sch1;
+DROP SCHEMA schema_sch1;
+
+-- Should fail. Disallow CREATE SCHEMA by ROLE with insufficient permission
+CREATE ROLE role_sch1;
+SET ROLE role_sch1;
+CREATE SCHEMA schema_sch2;
+RESET ROLE;
+DROP ROLE role_sch1;
+
+-- Should fail. Disallow CREATE SCHEMA if using a reserved name
+CREATE SCHEMA pg_asdf;
+
+-- Should fail. Disallow CREATE SCHEMA if already exists
+CREATE SCHEMA public;
+
+-- Ensure CREATE SCHEMA uses current_user (and not necessarily session_user)
+CREATE ROLE role_sch1;
+GRANT CREATE ON DATABASE regression to role_sch1;
+SET ROLE role_sch1;
+CREATE SCHEMA schema_sch2;
+SELECT schema_owner
+FROM information_schema.schemata
+WHERE schema_name <> 'information_schema'
+  AND schema_name !~ E'^pg_';
+DROP SCHEMA schema_sch2;
+RESET ROLE;
+REVOKE CREATE ON DATABASE regression FROM role_sch1;
+DROP ROLE role_sch1;
+
+-- RENAME SCHEMA
+CREATE SCHEMA schema_sch1;
+ALTER SCHEMA schema_sch1 RENAME TO schema_sch2;
+DROP SCHEMA schema_sch2;
+
+-- ALTER SCHEMA ok for user created IN ROLE of one with CREATE DATABASE rights
+CREATE ROLE role_sch3;
+GRANT CREATE ON DATABASE regression to role_sch3;
+CREATE ROLE role_sch4 IN ROLE role_sch3;
+SET ROLE role_sch3;
+CREATE SCHEMA schema_sch3;
+SET ROLE role_sch4;
+ALTER SCHEMA schema_sch3 RENAME TO schema_sch5;
+ALTER SCHEMA schema_sch5 OWNER TO role_sch4;
+DROP SCHEMA schema_sch5;
+RESET ROLE;
+REVOKE CREATE ON DATABASE regression FROM role_sch3;
+SET ROLE role_sch3;
+RESET ROLE;
+DROP ROLE role_sch4;
+DROP ROLE role_sch3;
+
+-- Should work, REASSIGN OWNED objects to another OWNER
+CREATE ROLE role_sch3;
+GRANT CREATE ON DATABASE regression to role_sch3;
+CREATE ROLE role_sch4;
+SET ROLE role_sch3;
+CREATE SCHEMA schema_sch3;
+RESET ROLE;
+REVOKE CREATE ON DATABASE regression FROM role_sch3;
+REASSIGN OWNED BY role_sch3 TO role_sch4;
+SET ROLE role_sch4;
+DROP SCHEMA schema_sch3;
+RESET ROLE;
+DROP ROLE role_sch4;
+DROP ROLE role_sch3;
+
+-- Should fail. Shouldn't RENAME SCHEMA if invalid / already existing / etc.
+CREATE SCHEMA schema_sch1;
+CREATE SCHEMA schema_sch3;
+ALTER SCHEMA schema_sch3 RENAME TO schema_sch1;
+ALTER SCHEMA schema_sch3 RENAME TO public;
+ALTER SCHEMA schema_sch3 RENAME TO pg_asdf;
+DROP SCHEMA schema_sch1;
+DROP SCHEMA schema_sch3;
+
+-- Should fail. Shouldn't ALTER SCHEMA if not OWNER
+CREATE SCHEMA schema_sch1;
+CREATE ROLE role_sch1;
+SET ROLE role_sch1;
+ALTER SCHEMA schema_sch1 RENAME TO schema_sch2;
+ALTER SCHEMA schema_sch1 OWNER TO role_sch1;
+RESET ROLE;
+DROP SCHEMA schema_sch1;
+DROP ROLE role_sch1;
+
+-- Should work. Non-Owner with CREATE ON DATABASE priviledge can RENAME SCHEMA
+CREATE ROLE role_sch1;
+GRANT CREATE ON DATABASE regression to role_sch1;
+SET ROLE role_sch1;
+CREATE SCHEMA schema_sch1;
+RESET ROLE;
+ALTER SCHEMA schema_sch1 RENAME TO schema_sch2;
+DROP SCHEMA schema_sch2;
+REVOKE CREATE ON DATABASE regression FROM role_sch1;
+DROP ROLE role_sch1;
+
+-- Should fail. OWNER without CREATE ON DATABASE can't ALTER OWNER SCHEMA
+CREATE ROLE role_sch1;
+CREATE SCHEMA schema_sch1 AUTHORIZATION role_sch1;
+CREATE ROLE role_sch2;
+GRANT role_sch2 TO role_sch1;
+SET ROLE role_sch1;
+ALTER SCHEMA schema_sch1 RENAME TO schema_sch2;
+ALTER SCHEMA schema_sch1 OWNER TO role_sch2;
+RESET ROLE;
+DROP SCHEMA schema_sch1;
+REVOKE role_sch2 FROM role_sch1;
+DROP ROLE role_sch2;
+DROP ROLE role_sch1;
+
+-- Should work. Try to have multiple OWNERships for a ROLE
+CREATE ROLE role_sch1;
+CREATE ROLE role_sch2;
+CREATE SCHEMA schema_sch1;
+GRANT CREATE ON SCHEMA schema_sch1 TO role_sch1;
+GRANT ALL ON SCHEMA schema_sch1 TO role_sch2;
+ALTER SCHEMA schema_sch1 OWNER TO role_sch1;
+REVOKE CREATE ON SCHEMA schema_sch1 FROM role_sch1;
+REVOKE ALL ON SCHEMA schema_sch1 FROM role_sch2;
+DROP SCHEMA schema_sch1;
+DROP ROLE role_sch2;
+DROP ROLE role_sch1;
+
+-- Change OWNER of SCHEMA
+CREATE SCHEMA schema_sch1;
+CREATE ROLE role_sch1;
+ALTER SCHEMA schema_sch1 OWNER TO role_sch1;
+DROP SCHEMA schema_sch1;
+DROP ROLE role_sch1;
+
+-- Should fail. Can't change OWNER of SCHEMA if doesn't exist/invalid name/etc.
+ALTER SCHEMA schema_sch1 RENAME TO schema_sch2;
+CREATE ROLE role_sch1;
+ALTER SCHEMA schema_sch1 OWNER TO role_sch1;
+DROP ROLE role_sch1;
+
-- 
1.7.10.4

Robins Tharakan

tharakan@gmail.com

over 12 years ago

In reply to: robins (#4)

1 attachment(s)

Re: Patch to add regression tests for SCHEMA

Hi,

Here is an updated patch that uses different schema / role names for
different tests (as per commitfest site feedback).

--
Robins Tharakan

On 18 March 2013 17:16, robins <tharakan@gmail.com> wrote:

Show quoted text

Hi,

Attached is an updated patch that uses better schema / role names.

--
Robins Tharakan

On 18 March 2013 12:59, robins <tharakan@gmail.com> wrote:

Thanks Alvaro.

Since the tests were running fine, I didn't bother with elaborate names,
but since you're concerned guess I'll make that change and re-submit.

And yes, I've already submitted (to Commitfest) another patch related to
regression tests for SEQUENCE.
Would submit the SCHEMA patch once the above change is done.
--
Robins

On 18 March 2013 09:42, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:

robins escribió:

Hi,

Please find attached a patch to take 'make check' code-coverage of

SCHEMA

from 33% to 98%.

Any feedback is more than welcome.

I think you should use more explicit names for shared objects such as
roles -- i.e. not "r1" but "regression_user_1" and so on. (But be
careful about role names used by other tests). The issue is that these
tests might be run in a database that contains other stuff; certainly we
don't want to drop or otherwise affect previously existing roles.

p.s.: I am currently working on more regression tests (USER / VIEW /
DISCARD etc). Please let me know if I need to post these as one large
patch, instead of submitting one patch at a time.

I think separate patches is better. Are you adding these patches to the
upcoming commitfest, for evaluation? https://commitfest.postgresql.org

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Attachments:

regress_schema_v3.patchapplication/octet-stream; name=regress_schema_v3.patchDownload

diff --git a/src/test/regress/expected/schema.out b/src/test/regress/expected/schema.out
new file mode 100644
index 0000000..e8c03e5
--- /dev/null
+++ b/src/test/regress/expected/schema.out
@@ -0,0 +1,145 @@
+--
+-- SCHEMA Commands
+--
+-- Should work
+CREATE SCHEMA schema_sch1;
+DROP SCHEMA schema_sch1;
+-- Should fail. Disallow CREATE SCHEMA by ROLE with insufficient permission
+CREATE ROLE role_sch2;
+SET ROLE role_sch2;
+CREATE SCHEMA schema_sch2;
+ERROR:  permission denied for database regression
+RESET ROLE;
+DROP ROLE role_sch2;
+-- Should fail. Disallow CREATE SCHEMA if using a reserved name
+CREATE SCHEMA pg_asdf;
+ERROR:  unacceptable schema name "pg_asdf"
+DETAIL:  The prefix "pg_" is reserved for system schemas.
+-- Should fail. Disallow CREATE SCHEMA if already exists
+CREATE SCHEMA public;
+ERROR:  schema "public" already exists
+-- Ensure CREATE SCHEMA uses current_user (and not necessarily session_user)
+CREATE ROLE role_sch3;
+GRANT CREATE ON DATABASE regression TO role_sch3;
+SET ROLE role_sch3;
+CREATE SCHEMA schema_sch3;
+SELECT schema_owner
+FROM information_schema.schemata
+WHERE schema_name <> 'information_schema'
+  AND schema_name !~ E'^pg_';
+ schema_owner 
+--------------
+ role_sch3
+(1 row)
+
+DROP SCHEMA schema_sch3;
+RESET ROLE;
+REVOKE CREATE ON DATABASE regression FROM role_sch3;
+DROP ROLE role_sch3;
+-- Should work. RENAME SCHEMA
+CREATE SCHEMA schema_sch4;
+ALTER SCHEMA schema_sch4 RENAME TO schema_sch4b;
+DROP SCHEMA schema_sch4b;
+-- ALTER SCHEMA ok for user created IN ROLE of one with CREATE DATABASE rights
+CREATE ROLE role_sch5;
+GRANT CREATE ON DATABASE regression to role_sch5;
+CREATE ROLE role_sch5b IN ROLE role_sch5;
+SET ROLE role_sch5;
+CREATE SCHEMA schema_sch5;
+SET ROLE role_sch5b;
+ALTER SCHEMA schema_sch5 RENAME TO schema_sch5b;
+ALTER SCHEMA schema_sch5b OWNER TO role_sch5b;
+DROP SCHEMA schema_sch5b;
+RESET ROLE;
+REVOKE CREATE ON DATABASE regression FROM role_sch5;
+SET ROLE role_sch5;
+RESET ROLE;
+DROP ROLE role_sch5b;
+DROP ROLE role_sch5;
+-- Should work, REASSIGN OWNED objects to another OWNER
+CREATE ROLE role_sch6;
+GRANT CREATE ON DATABASE regression to role_sch6;
+CREATE ROLE role_sch6b;
+SET ROLE role_sch6;
+CREATE SCHEMA schema_sch6;
+RESET ROLE;
+REVOKE CREATE ON DATABASE regression FROM role_sch6;
+REASSIGN OWNED BY role_sch6 TO role_sch6b;
+SET ROLE role_sch6b;
+DROP SCHEMA schema_sch6;
+RESET ROLE;
+DROP ROLE role_sch6b;
+DROP ROLE role_sch6;
+-- Should fail. Shouldn't RENAME SCHEMA if invalid / already existing / etc.
+CREATE SCHEMA schema_sch7;
+CREATE SCHEMA schema_sch7b;
+ALTER SCHEMA schema_sch7b RENAME TO schema_sch7;
+ERROR:  schema "schema_sch7" already exists
+ALTER SCHEMA schema_sch7b RENAME TO public;
+ERROR:  schema "public" already exists
+ALTER SCHEMA schema_sch7b RENAME TO pg_asdf;
+ERROR:  unacceptable schema name "pg_asdf"
+DETAIL:  The prefix "pg_" is reserved for system schemas.
+DROP SCHEMA schema_sch7;
+DROP SCHEMA schema_sch7b;
+-- Should fail. Shouldn't ALTER SCHEMA if not OWNER
+CREATE SCHEMA schema_sch8;
+CREATE ROLE role_sch8;
+SET ROLE role_sch8;
+ALTER SCHEMA schema_sch8 RENAME TO schema_sch8b;
+ERROR:  must be owner of schema schema_sch8
+ALTER SCHEMA schema_sch8 OWNER TO role_sch8;
+ERROR:  must be owner of schema schema_sch8
+RESET ROLE;
+DROP SCHEMA schema_sch8;
+DROP ROLE role_sch8;
+-- Should work. Non-Owner with CREATE ON DATABASE priviledge can RENAME SCHEMA
+CREATE ROLE role_sch9;
+GRANT CREATE ON DATABASE regression to role_sch9;
+SET ROLE role_sch9;
+CREATE SCHEMA schema_sch9;
+RESET ROLE;
+ALTER SCHEMA schema_sch9 RENAME TO schema_sch9b;
+DROP SCHEMA schema_sch9b;
+REVOKE CREATE ON DATABASE regression FROM role_sch9;
+DROP ROLE role_sch9;
+-- Should fail. OWNER without CREATE ON DATABASE can't ALTER OWNER SCHEMA
+CREATE ROLE role_sch10;
+CREATE SCHEMA schema_sch10 AUTHORIZATION role_sch10;
+CREATE ROLE role_sch10b;
+GRANT role_sch10b TO role_sch10;
+SET ROLE role_sch10;
+ALTER SCHEMA schema_sch10 RENAME TO schema_sch2;
+ERROR:  permission denied for database regression
+ALTER SCHEMA schema_sch10 OWNER TO role_sch10b;
+ERROR:  permission denied for database regression
+RESET ROLE;
+DROP SCHEMA schema_sch10;
+REVOKE role_sch10b FROM role_sch10;
+DROP ROLE role_sch10b;
+DROP ROLE role_sch10;
+-- Should work. Try to have multiple OWNERships for a ROLE
+CREATE ROLE role_sch11;
+CREATE ROLE role_sch11b;
+CREATE SCHEMA schema_sch11;
+GRANT CREATE ON SCHEMA schema_sch11 TO role_sch11;
+GRANT ALL ON SCHEMA schema_sch11 TO role_sch11b;
+ALTER SCHEMA schema_sch11 OWNER TO role_sch11;
+REVOKE CREATE ON SCHEMA schema_sch11 FROM role_sch11;
+REVOKE ALL ON SCHEMA schema_sch11 FROM role_sch11b;
+DROP SCHEMA schema_sch11;
+DROP ROLE role_sch11b;
+DROP ROLE role_sch11;
+-- Change OWNER of SCHEMA
+CREATE SCHEMA schema_sch12;
+CREATE ROLE role_sch12;
+ALTER SCHEMA schema_sch12 OWNER TO role_sch12;
+DROP SCHEMA schema_sch12;
+DROP ROLE role_sch12;
+-- Should fail. Can't change OWNER of SCHEMA if doesn't exist/invalid name/etc.
+ALTER SCHEMA schema_sch13 RENAME TO schema_sch13;
+ERROR:  schema "schema_sch13" does not exist
+CREATE ROLE role_sch13;
+ALTER SCHEMA schema_sch13 OWNER TO role_sch13;
+ERROR:  schema "schema_sch13" does not exist
+DROP ROLE role_sch13;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 2af28b1..7d23aaa 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -78,7 +78,7 @@ ignore: random
 # ----------
 # Another group of parallel tests
 # ----------
-test: select_into select_distinct select_distinct_on select_implicit select_having subselect union case join aggregates transactions random portals arrays btree_index hash_index update namespace prepared_xacts delete
+test: select_into select_distinct select_distinct_on select_implicit select_having subselect union case join aggregates transactions random portals arrays btree_index hash_index update namespace prepared_xacts delete schema
 
 # ----------
 # Another group of parallel tests
diff --git a/src/test/regress/sql/schema.sql b/src/test/regress/sql/schema.sql
new file mode 100644
index 0000000..5b1250e
--- /dev/null
+++ b/src/test/regress/sql/schema.sql
@@ -0,0 +1,141 @@
+--
+-- SCHEMA Commands
+--
+
+-- Should work
+CREATE SCHEMA schema_sch1;
+DROP SCHEMA schema_sch1;
+
+-- Should fail. Disallow CREATE SCHEMA by ROLE with insufficient permission
+CREATE ROLE role_sch2;
+SET ROLE role_sch2;
+CREATE SCHEMA schema_sch2;
+RESET ROLE;
+DROP ROLE role_sch2;
+
+-- Should fail. Disallow CREATE SCHEMA if using a reserved name
+CREATE SCHEMA pg_asdf;
+
+-- Should fail. Disallow CREATE SCHEMA if already exists
+CREATE SCHEMA public;
+
+-- Ensure CREATE SCHEMA uses current_user (and not necessarily session_user)
+CREATE ROLE role_sch3;
+GRANT CREATE ON DATABASE regression TO role_sch3;
+SET ROLE role_sch3;
+CREATE SCHEMA schema_sch3;
+SELECT schema_owner
+FROM information_schema.schemata
+WHERE schema_name <> 'information_schema'
+  AND schema_name !~ E'^pg_';
+DROP SCHEMA schema_sch3;
+RESET ROLE;
+REVOKE CREATE ON DATABASE regression FROM role_sch3;
+DROP ROLE role_sch3;
+
+-- Should work. RENAME SCHEMA
+CREATE SCHEMA schema_sch4;
+ALTER SCHEMA schema_sch4 RENAME TO schema_sch4b;
+DROP SCHEMA schema_sch4b;
+
+-- ALTER SCHEMA ok for user created IN ROLE of one with CREATE DATABASE rights
+CREATE ROLE role_sch5;
+GRANT CREATE ON DATABASE regression to role_sch5;
+CREATE ROLE role_sch5b IN ROLE role_sch5;
+SET ROLE role_sch5;
+CREATE SCHEMA schema_sch5;
+SET ROLE role_sch5b;
+ALTER SCHEMA schema_sch5 RENAME TO schema_sch5b;
+ALTER SCHEMA schema_sch5b OWNER TO role_sch5b;
+DROP SCHEMA schema_sch5b;
+RESET ROLE;
+REVOKE CREATE ON DATABASE regression FROM role_sch5;
+SET ROLE role_sch5;
+RESET ROLE;
+DROP ROLE role_sch5b;
+DROP ROLE role_sch5;
+
+-- Should work, REASSIGN OWNED objects to another OWNER
+CREATE ROLE role_sch6;
+GRANT CREATE ON DATABASE regression to role_sch6;
+CREATE ROLE role_sch6b;
+SET ROLE role_sch6;
+CREATE SCHEMA schema_sch6;
+RESET ROLE;
+REVOKE CREATE ON DATABASE regression FROM role_sch6;
+REASSIGN OWNED BY role_sch6 TO role_sch6b;
+SET ROLE role_sch6b;
+DROP SCHEMA schema_sch6;
+RESET ROLE;
+DROP ROLE role_sch6b;
+DROP ROLE role_sch6;
+
+-- Should fail. Shouldn't RENAME SCHEMA if invalid / already existing / etc.
+CREATE SCHEMA schema_sch7;
+CREATE SCHEMA schema_sch7b;
+ALTER SCHEMA schema_sch7b RENAME TO schema_sch7;
+ALTER SCHEMA schema_sch7b RENAME TO public;
+ALTER SCHEMA schema_sch7b RENAME TO pg_asdf;
+DROP SCHEMA schema_sch7;
+DROP SCHEMA schema_sch7b;
+
+-- Should fail. Shouldn't ALTER SCHEMA if not OWNER
+CREATE SCHEMA schema_sch8;
+CREATE ROLE role_sch8;
+SET ROLE role_sch8;
+ALTER SCHEMA schema_sch8 RENAME TO schema_sch8b;
+ALTER SCHEMA schema_sch8 OWNER TO role_sch8;
+RESET ROLE;
+DROP SCHEMA schema_sch8;
+DROP ROLE role_sch8;
+
+-- Should work. Non-Owner with CREATE ON DATABASE priviledge can RENAME SCHEMA
+CREATE ROLE role_sch9;
+GRANT CREATE ON DATABASE regression to role_sch9;
+SET ROLE role_sch9;
+CREATE SCHEMA schema_sch9;
+RESET ROLE;
+ALTER SCHEMA schema_sch9 RENAME TO schema_sch9b;
+DROP SCHEMA schema_sch9b;
+REVOKE CREATE ON DATABASE regression FROM role_sch9;
+DROP ROLE role_sch9;
+
+-- Should fail. OWNER without CREATE ON DATABASE can't ALTER OWNER SCHEMA
+CREATE ROLE role_sch10;
+CREATE SCHEMA schema_sch10 AUTHORIZATION role_sch10;
+CREATE ROLE role_sch10b;
+GRANT role_sch10b TO role_sch10;
+SET ROLE role_sch10;
+ALTER SCHEMA schema_sch10 RENAME TO schema_sch2;
+ALTER SCHEMA schema_sch10 OWNER TO role_sch10b;
+RESET ROLE;
+DROP SCHEMA schema_sch10;
+REVOKE role_sch10b FROM role_sch10;
+DROP ROLE role_sch10b;
+DROP ROLE role_sch10;
+
+-- Should work. Try to have multiple OWNERships for a ROLE
+CREATE ROLE role_sch11;
+CREATE ROLE role_sch11b;
+CREATE SCHEMA schema_sch11;
+GRANT CREATE ON SCHEMA schema_sch11 TO role_sch11;
+GRANT ALL ON SCHEMA schema_sch11 TO role_sch11b;
+ALTER SCHEMA schema_sch11 OWNER TO role_sch11;
+REVOKE CREATE ON SCHEMA schema_sch11 FROM role_sch11;
+REVOKE ALL ON SCHEMA schema_sch11 FROM role_sch11b;
+DROP SCHEMA schema_sch11;
+DROP ROLE role_sch11b;
+DROP ROLE role_sch11;
+
+-- Change OWNER of SCHEMA
+CREATE SCHEMA schema_sch12;
+CREATE ROLE role_sch12;
+ALTER SCHEMA schema_sch12 OWNER TO role_sch12;
+DROP SCHEMA schema_sch12;
+DROP ROLE role_sch12;
+
+-- Should fail. Can't change OWNER of SCHEMA if doesn't exist/invalid name/etc.
+ALTER SCHEMA schema_sch13 RENAME TO schema_sch13;
+CREATE ROLE role_sch13;
+ALTER SCHEMA schema_sch13 OWNER TO role_sch13;
+DROP ROLE role_sch13;

Robert Haas

robertmhaas@gmail.com

over 12 years ago

In reply to: Robins Tharakan (#5)

Re: Patch to add regression tests for SCHEMA

On Tue, May 7, 2013 at 7:26 PM, Robins Tharakan <tharakan@gmail.com> wrote:

Here is an updated patch that uses different schema / role names for
different tests (as per commitfest site feedback).

I'm not sure what's going on here. Reviews are to be posted to
pgsql-hackers, and then linked from the CommitFest site. Putting
reviews only on the CommitFest site is bad practice.

--
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

Robins Tharakan

tharakan@gmail.com

over 12 years ago

In reply to: Robert Haas (#6)

Re: Patch to add regression tests for SCHEMA

Completely agree. Although the poster was kind enough to intimate me by
email about his update there, but was wondering that if he hadn't, I
wouldnt' have dreamt that there is a feedback on the site, two months down
the line.

--
Robins Tharakan

On 8 May 2013 09:13, Robert Haas <robertmhaas@gmail.com> wrote:

Show quoted text

On Tue, May 7, 2013 at 7:26 PM, Robins Tharakan <tharakan@gmail.com>
wrote:

Here is an updated patch that uses different schema / role names for
different tests (as per commitfest site feedback).

I'm not sure what's going on here. Reviews are to be posted to
pgsql-hackers, and then linked from the CommitFest site. Putting
reviews only on the CommitFest site is bad practice.

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

Fabien COELHO

coelho@cri.ensmp.fr

over 12 years ago

In reply to: Robert Haas (#6)

Re: Patch to add regression tests for SCHEMA

Reviews are to be posted to pgsql-hackers, and then linked from the
CommitFest site. Putting reviews only on the CommitFest site is bad
practice.

Indeed. Sorry, shame on me!

I had not the original mail in my mailbox because I deleted it, I did not
want to create a new thread because this is /also/ bad practice as I was
recently told, and I was not motivated by fetching and reinstating the
messages in my mailbox for a short one-liner review.

Weel, I'll do better next time.

Anyway, all Robins' test cases are basically a very good thing, especially
as he tries corner cases, including checking for expected errors and
permission denials.

--
Fabien.

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

Fabien COELHO

coelho@cri.ensmp.fr

over 12 years ago

In reply to: Robins Tharakan (#5)

Re: Patch to add regression tests for SCHEMA

Dear Robins,

Here is an updated patch that uses different schema / role names for
different tests (as per commitfest site feedback).

Short review about this version of the patch:

This patch work for me.

This test is a good thing and allows schema to be thoroughly tested,
including corner cases which must fail because of errors or permissions.

Two remarks:

- test 2 bis: why name 'pg_asdf'? why not 'pg_schema_sch<some number>'
to be homogeneous with other tests?

- test 3: why not WHERE schema_name='schema_sch3' instead of two
negative comparisons? ISTM that if for some reason in the future a new
schema name is added, the test will fail.

--
Fabien.

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

#10

Robins Tharakan

tharakan@gmail.com

over 12 years ago

In reply to: Fabien COELHO (#9)

1 attachment(s)

Re: Patch to add regression tests for SCHEMA

Hi,

Please find attached an updated patch with the said changes.
I'll try to update the other patches (if they pertain to this feedback) and
update on their respective threads (as well as on Commitfest).

--
Robins Tharakan

On 8 May 2013 13:01, Fabien COELHO <coelho@cri.ensmp.fr> wrote:

Show quoted text

Dear Robins,

Here is an updated patch that uses different schema / role names for

different tests (as per commitfest site feedback).

Short review about this version of the patch:

This patch work for me.

This test is a good thing and allows schema to be thoroughly tested,
including corner cases which must fail because of errors or permissions.

Two remarks:

- test 2 bis: why name 'pg_asdf'? why not 'pg_schema_sch<some number>'
to be homogeneous with other tests?

- test 3: why not WHERE schema_name='schema_sch3' instead of two
negative comparisons? ISTM that if for some reason in the future a new
schema name is added, the test will fail.

--
Fabien.

Attachments:

regress_schema_v4.patchapplication/octet-stream; name=regress_schema_v4.patchDownload

diff --git a/src/test/regress/expected/schema.out b/src/test/regress/expected/schema.out
new file mode 100644
index 0000000..c437328
--- /dev/null
+++ b/src/test/regress/expected/schema.out
@@ -0,0 +1,146 @@
+--
+-- SCHEMA Commands
+--
+-- Should work
+CREATE SCHEMA schema_sch1;
+DROP SCHEMA schema_sch1;
+-- Should fail. Disallow CREATE SCHEMA by ROLE with insufficient permission
+CREATE ROLE role_sch2;
+SET ROLE role_sch2;
+CREATE SCHEMA schema_sch2;
+ERROR:  permission denied for database regression
+RESET ROLE;
+DROP ROLE role_sch2;
+-- Should fail. Disallow CREATE SCHEMA if using a reserved name
+CREATE SCHEMA pg_schema_sch2b;
+ERROR:  unacceptable schema name "pg_schema_sch2b"
+DETAIL:  The prefix "pg_" is reserved for system schemas.
+-- Should fail. Disallow CREATE SCHEMA if already exists
+CREATE SCHEMA schema_sch2c;
+CREATE SCHEMA schema_sch2c;
+ERROR:  schema "schema_sch2c" already exists
+DROP SCHEMA schema_sch2c;
+-- Ensure CREATE SCHEMA uses current_user (and not necessarily session_user)
+CREATE ROLE role_sch3;
+GRANT CREATE ON DATABASE regression TO role_sch3;
+SET ROLE role_sch3;
+CREATE SCHEMA schema_sch3;
+SELECT schema_owner
+FROM information_schema.schemata
+WHERE schema_name = 'schema_sch3';
+ schema_owner 
+--------------
+ role_sch3
+(1 row)
+
+DROP SCHEMA schema_sch3;
+RESET ROLE;
+REVOKE CREATE ON DATABASE regression FROM role_sch3;
+DROP ROLE role_sch3;
+-- Should work. RENAME SCHEMA
+CREATE SCHEMA schema_sch4;
+ALTER SCHEMA schema_sch4 RENAME TO schema_sch4b;
+DROP SCHEMA schema_sch4b;
+-- ALTER SCHEMA ok for user created IN ROLE of one with CREATE DATABASE rights
+CREATE ROLE role_sch5;
+GRANT CREATE ON DATABASE regression to role_sch5;
+CREATE ROLE role_sch5b IN ROLE role_sch5;
+SET ROLE role_sch5;
+CREATE SCHEMA schema_sch5;
+SET ROLE role_sch5b;
+ALTER SCHEMA schema_sch5 RENAME TO schema_sch5b;
+ALTER SCHEMA schema_sch5b OWNER TO role_sch5b;
+DROP SCHEMA schema_sch5b;
+RESET ROLE;
+REVOKE CREATE ON DATABASE regression FROM role_sch5;
+SET ROLE role_sch5;
+RESET ROLE;
+DROP ROLE role_sch5b;
+DROP ROLE role_sch5;
+-- Should work, REASSIGN OWNED objects to another OWNER
+CREATE ROLE role_sch6;
+GRANT CREATE ON DATABASE regression to role_sch6;
+CREATE ROLE role_sch6b;
+SET ROLE role_sch6;
+CREATE SCHEMA schema_sch6;
+RESET ROLE;
+REVOKE CREATE ON DATABASE regression FROM role_sch6;
+REASSIGN OWNED BY role_sch6 TO role_sch6b;
+SET ROLE role_sch6b;
+DROP SCHEMA schema_sch6;
+RESET ROLE;
+DROP ROLE role_sch6b;
+DROP ROLE role_sch6;
+-- Should fail. Shouldn't RENAME SCHEMA if invalid / already existing / etc.
+CREATE SCHEMA schema_sch7;
+CREATE SCHEMA schema_sch7b;
+ALTER SCHEMA schema_sch7b RENAME TO schema_sch7;
+ERROR:  schema "schema_sch7" already exists
+ALTER SCHEMA schema_sch7b RENAME TO public;
+ERROR:  schema "public" already exists
+ALTER SCHEMA schema_sch7b RENAME TO pg_asdf;
+ERROR:  unacceptable schema name "pg_asdf"
+DETAIL:  The prefix "pg_" is reserved for system schemas.
+DROP SCHEMA schema_sch7;
+DROP SCHEMA schema_sch7b;
+-- Should fail. Shouldn't ALTER SCHEMA if not OWNER
+CREATE SCHEMA schema_sch8;
+CREATE ROLE role_sch8;
+SET ROLE role_sch8;
+ALTER SCHEMA schema_sch8 RENAME TO schema_sch8b;
+ERROR:  must be owner of schema schema_sch8
+ALTER SCHEMA schema_sch8 OWNER TO role_sch8;
+ERROR:  must be owner of schema schema_sch8
+RESET ROLE;
+DROP SCHEMA schema_sch8;
+DROP ROLE role_sch8;
+-- Should work. Non-Owner with CREATE ON DATABASE priviledge can RENAME SCHEMA
+CREATE ROLE role_sch9;
+GRANT CREATE ON DATABASE regression to role_sch9;
+SET ROLE role_sch9;
+CREATE SCHEMA schema_sch9;
+RESET ROLE;
+ALTER SCHEMA schema_sch9 RENAME TO schema_sch9b;
+DROP SCHEMA schema_sch9b;
+REVOKE CREATE ON DATABASE regression FROM role_sch9;
+DROP ROLE role_sch9;
+-- Should fail. OWNER without CREATE ON DATABASE can't ALTER OWNER SCHEMA
+CREATE ROLE role_sch10;
+CREATE SCHEMA schema_sch10 AUTHORIZATION role_sch10;
+CREATE ROLE role_sch10b;
+GRANT role_sch10b TO role_sch10;
+SET ROLE role_sch10;
+ALTER SCHEMA schema_sch10 RENAME TO schema_sch2;
+ERROR:  permission denied for database regression
+ALTER SCHEMA schema_sch10 OWNER TO role_sch10b;
+ERROR:  permission denied for database regression
+RESET ROLE;
+DROP SCHEMA schema_sch10;
+REVOKE role_sch10b FROM role_sch10;
+DROP ROLE role_sch10b;
+DROP ROLE role_sch10;
+-- Should work. Try to have multiple OWNERships for a ROLE
+CREATE ROLE role_sch11;
+CREATE ROLE role_sch11b;
+CREATE SCHEMA schema_sch11;
+GRANT CREATE ON SCHEMA schema_sch11 TO role_sch11;
+GRANT ALL ON SCHEMA schema_sch11 TO role_sch11b;
+ALTER SCHEMA schema_sch11 OWNER TO role_sch11;
+REVOKE CREATE ON SCHEMA schema_sch11 FROM role_sch11;
+REVOKE ALL ON SCHEMA schema_sch11 FROM role_sch11b;
+DROP SCHEMA schema_sch11;
+DROP ROLE role_sch11b;
+DROP ROLE role_sch11;
+-- Change OWNER of SCHEMA
+CREATE SCHEMA schema_sch12;
+CREATE ROLE role_sch12;
+ALTER SCHEMA schema_sch12 OWNER TO role_sch12;
+DROP SCHEMA schema_sch12;
+DROP ROLE role_sch12;
+-- Should fail. Can't change OWNER of SCHEMA if doesn't exist/invalid name/etc.
+ALTER SCHEMA schema_sch13 RENAME TO schema_sch13;
+ERROR:  schema "schema_sch13" does not exist
+CREATE ROLE role_sch13;
+ALTER SCHEMA schema_sch13 OWNER TO role_sch13;
+ERROR:  schema "schema_sch13" does not exist
+DROP ROLE role_sch13;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 2af28b1..7d23aaa 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -78,7 +78,7 @@ ignore: random
 # ----------
 # Another group of parallel tests
 # ----------
-test: select_into select_distinct select_distinct_on select_implicit select_having subselect union case join aggregates transactions random portals arrays btree_index hash_index update namespace prepared_xacts delete
+test: select_into select_distinct select_distinct_on select_implicit select_having subselect union case join aggregates transactions random portals arrays btree_index hash_index update namespace prepared_xacts delete schema
 
 # ----------
 # Another group of parallel tests
diff --git a/src/test/regress/sql/schema.sql b/src/test/regress/sql/schema.sql
new file mode 100644
index 0000000..07698fa
--- /dev/null
+++ b/src/test/regress/sql/schema.sql
@@ -0,0 +1,142 @@
+--
+-- SCHEMA Commands
+--
+
+-- Should work
+CREATE SCHEMA schema_sch1;
+DROP SCHEMA schema_sch1;
+
+-- Should fail. Disallow CREATE SCHEMA by ROLE with insufficient permission
+CREATE ROLE role_sch2;
+SET ROLE role_sch2;
+CREATE SCHEMA schema_sch2;
+RESET ROLE;
+DROP ROLE role_sch2;
+
+-- Should fail. Disallow CREATE SCHEMA if using a reserved name
+CREATE SCHEMA pg_schema_sch2b;
+
+-- Should fail. Disallow CREATE SCHEMA if already exists
+CREATE SCHEMA schema_sch2c;
+CREATE SCHEMA schema_sch2c;
+DROP SCHEMA schema_sch2c;
+
+-- Ensure CREATE SCHEMA uses current_user (and not necessarily session_user)
+CREATE ROLE role_sch3;
+GRANT CREATE ON DATABASE regression TO role_sch3;
+SET ROLE role_sch3;
+CREATE SCHEMA schema_sch3;
+SELECT schema_owner
+FROM information_schema.schemata
+WHERE schema_name = 'schema_sch3';
+DROP SCHEMA schema_sch3;
+RESET ROLE;
+REVOKE CREATE ON DATABASE regression FROM role_sch3;
+DROP ROLE role_sch3;
+
+-- Should work. RENAME SCHEMA
+CREATE SCHEMA schema_sch4;
+ALTER SCHEMA schema_sch4 RENAME TO schema_sch4b;
+DROP SCHEMA schema_sch4b;
+
+-- ALTER SCHEMA ok for user created IN ROLE of one with CREATE DATABASE rights
+CREATE ROLE role_sch5;
+GRANT CREATE ON DATABASE regression to role_sch5;
+CREATE ROLE role_sch5b IN ROLE role_sch5;
+SET ROLE role_sch5;
+CREATE SCHEMA schema_sch5;
+SET ROLE role_sch5b;
+ALTER SCHEMA schema_sch5 RENAME TO schema_sch5b;
+ALTER SCHEMA schema_sch5b OWNER TO role_sch5b;
+DROP SCHEMA schema_sch5b;
+RESET ROLE;
+REVOKE CREATE ON DATABASE regression FROM role_sch5;
+SET ROLE role_sch5;
+RESET ROLE;
+DROP ROLE role_sch5b;
+DROP ROLE role_sch5;
+
+-- Should work, REASSIGN OWNED objects to another OWNER
+CREATE ROLE role_sch6;
+GRANT CREATE ON DATABASE regression to role_sch6;
+CREATE ROLE role_sch6b;
+SET ROLE role_sch6;
+CREATE SCHEMA schema_sch6;
+RESET ROLE;
+REVOKE CREATE ON DATABASE regression FROM role_sch6;
+REASSIGN OWNED BY role_sch6 TO role_sch6b;
+SET ROLE role_sch6b;
+DROP SCHEMA schema_sch6;
+RESET ROLE;
+DROP ROLE role_sch6b;
+DROP ROLE role_sch6;
+
+-- Should fail. Shouldn't RENAME SCHEMA if invalid / already existing / etc.
+CREATE SCHEMA schema_sch7;
+CREATE SCHEMA schema_sch7b;
+ALTER SCHEMA schema_sch7b RENAME TO schema_sch7;
+ALTER SCHEMA schema_sch7b RENAME TO public;
+ALTER SCHEMA schema_sch7b RENAME TO pg_asdf;
+DROP SCHEMA schema_sch7;
+DROP SCHEMA schema_sch7b;
+
+-- Should fail. Shouldn't ALTER SCHEMA if not OWNER
+CREATE SCHEMA schema_sch8;
+CREATE ROLE role_sch8;
+SET ROLE role_sch8;
+ALTER SCHEMA schema_sch8 RENAME TO schema_sch8b;
+ALTER SCHEMA schema_sch8 OWNER TO role_sch8;
+RESET ROLE;
+DROP SCHEMA schema_sch8;
+DROP ROLE role_sch8;
+
+-- Should work. Non-Owner with CREATE ON DATABASE priviledge can RENAME SCHEMA
+CREATE ROLE role_sch9;
+GRANT CREATE ON DATABASE regression to role_sch9;
+SET ROLE role_sch9;
+CREATE SCHEMA schema_sch9;
+RESET ROLE;
+ALTER SCHEMA schema_sch9 RENAME TO schema_sch9b;
+DROP SCHEMA schema_sch9b;
+REVOKE CREATE ON DATABASE regression FROM role_sch9;
+DROP ROLE role_sch9;
+
+-- Should fail. OWNER without CREATE ON DATABASE can't ALTER OWNER SCHEMA
+CREATE ROLE role_sch10;
+CREATE SCHEMA schema_sch10 AUTHORIZATION role_sch10;
+CREATE ROLE role_sch10b;
+GRANT role_sch10b TO role_sch10;
+SET ROLE role_sch10;
+ALTER SCHEMA schema_sch10 RENAME TO schema_sch2;
+ALTER SCHEMA schema_sch10 OWNER TO role_sch10b;
+RESET ROLE;
+DROP SCHEMA schema_sch10;
+REVOKE role_sch10b FROM role_sch10;
+DROP ROLE role_sch10b;
+DROP ROLE role_sch10;
+
+-- Should work. Try to have multiple OWNERships for a ROLE
+CREATE ROLE role_sch11;
+CREATE ROLE role_sch11b;
+CREATE SCHEMA schema_sch11;
+GRANT CREATE ON SCHEMA schema_sch11 TO role_sch11;
+GRANT ALL ON SCHEMA schema_sch11 TO role_sch11b;
+ALTER SCHEMA schema_sch11 OWNER TO role_sch11;
+REVOKE CREATE ON SCHEMA schema_sch11 FROM role_sch11;
+REVOKE ALL ON SCHEMA schema_sch11 FROM role_sch11b;
+DROP SCHEMA schema_sch11;
+DROP ROLE role_sch11b;
+DROP ROLE role_sch11;
+
+-- Change OWNER of SCHEMA
+CREATE SCHEMA schema_sch12;
+CREATE ROLE role_sch12;
+ALTER SCHEMA schema_sch12 OWNER TO role_sch12;
+DROP SCHEMA schema_sch12;
+DROP ROLE role_sch12;
+
+-- Should fail. Can't change OWNER of SCHEMA if doesn't exist/invalid name/etc.
+ALTER SCHEMA schema_sch13 RENAME TO schema_sch13;
+CREATE ROLE role_sch13;
+ALTER SCHEMA schema_sch13 OWNER TO role_sch13;
+DROP ROLE role_sch13;

#11

Fabien COELHO

coelho@cri.ensmp.fr

over 12 years ago

In reply to: Robins Tharakan (#10)

Re: Patch to add regression tests for SCHEMA

Please find attached an updated patch with the said changes. I'll try to
update the other patches (if they pertain to this feedback) and update
on their respective threads (as well as on Commitfest).

Ok, this new version addresses my questions.

The patch works for me (nothing to compile, the added tests pass).

I recommend its inclusion as it tests corner cases especially about
permissions and error conditions, some of which may have security
implications if they were to fail some day. So this is a good thing.

The above remark applies to all systematic but not redundant regression
tests submitted. If the overall test was to be deemed too long and slow
for developers, I would suggest to have a two-tier system with basic and
fast tests for devs and longer tests for the build farm.

--
Fabien.

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

#12

Robert Haas

robertmhaas@gmail.com

over 12 years ago

In reply to: Robins Tharakan (#10)

Re: Patch to add regression tests for SCHEMA

On Wed, May 8, 2013 at 9:19 PM, Robins Tharakan <tharakan@gmail.com> wrote:

Please find attached an updated patch with the said changes.
I'll try to update the other patches (if they pertain to this feedback) and
update on their respective threads (as well as on Commitfest).

This patch updates the parallel schedule but not the serial schedule.
Please try to remember to update both files when adding new test
files. Also, please observe the admonitions in the parallel schedule,
at top of file:

# By convention, we put no more than twenty tests in any one parallel group;
# this limits the number of connections needed to run the tests.

In this particular case, I think that adding a new set of tests isn't
the right thing anyway. Schemas are also known as namespaces, and the
existing "namespace" test is where related test cases live. Add these
tests there instead.

Rename regression users to regress_rol_nsp1 etc. per convention
established in the CREATE OPERATOR patch.

Setting patch to "Waiting on Author".

--
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

#13

Robins Tharakan

tharakan@gmail.com

over 12 years ago

In reply to: Robert Haas (#12)

1 attachment(s)

Re: Patch to add regression tests for SCHEMA

On 3 July 2013 10:19, Robert Haas <robertmhaas@gmail.com> wrote:

In this particular case, I think that adding a new set of tests isn't
the right thing anyway. Schemas are also known as namespaces, and the
existing "namespace" test is where related test cases live. Add these
tests there instead.

Rename regression users to regress_rol_nsp1 etc. per convention
established in the CREATE OPERATOR patch.

Hi Robert,

PFA an updated patch:
- Renamed ROLEs as per new feedback (although the previous naming was also
as per an earlier feedback)
- Merged tests into namespace

--
Robins Tharakan

Attachments:

regress_schema_v5.patchapplication/octet-stream; name=regress_schema_v5.patchDownload

diff --git a/src/test/regress/expected/namespace.out b/src/test/regress/expected/namespace.out
index 9187c81..675da18 100644
--- a/src/test/regress/expected/namespace.out
+++ b/src/test/regress/expected/namespace.out
@@ -61,3 +61,143 @@ SELECT COUNT(*) FROM pg_class WHERE relnamespace =
      0
 (1 row)
 
+-- Should fail. Disallow CREATE SCHEMA by ROLE with insufficient permission
+CREATE ROLE regress_role_sch2;
+SET ROLE regress_role_sch2;
+CREATE SCHEMA schema_sch2;
+ERROR:  permission denied for database regression
+RESET ROLE;
+DROP ROLE regress_role_sch2;
+-- Should fail. Disallow CREATE SCHEMA if using a reserved name
+CREATE SCHEMA pg_schema_sch2b;
+ERROR:  unacceptable schema name "pg_schema_sch2b"
+DETAIL:  The prefix "pg_" is reserved for system schemas.
+-- Should fail. Disallow CREATE SCHEMA if already exists
+CREATE SCHEMA schema_sch2c;
+CREATE SCHEMA schema_sch2c;
+ERROR:  schema "schema_sch2c" already exists
+DROP SCHEMA schema_sch2c;
+-- Ensure CREATE SCHEMA uses current_user (and not necessarily session_user)
+CREATE ROLE regress_role_sch3;
+GRANT CREATE ON DATABASE regression TO regress_role_sch3;
+SET ROLE regress_role_sch3;
+CREATE SCHEMA schema_sch3;
+SELECT schema_owner
+FROM information_schema.schemata
+WHERE schema_name = 'schema_sch3';
+   schema_owner    
+-------------------
+ regress_role_sch3
+(1 row)
+
+DROP SCHEMA schema_sch3;
+RESET ROLE;
+REVOKE CREATE ON DATABASE regression FROM regress_role_sch3;
+DROP ROLE regress_role_sch3;
+-- Should work. RENAME SCHEMA
+CREATE SCHEMA schema_sch4;
+ALTER SCHEMA schema_sch4 RENAME TO schema_sch4b;
+DROP SCHEMA schema_sch4b;
+-- ALTER SCHEMA ok for user created IN ROLE of one with CREATE DATABASE rights
+CREATE ROLE regress_role_sch5;
+GRANT CREATE ON DATABASE regression to regress_role_sch5;
+CREATE ROLE regress_role_sch5b IN ROLE regress_role_sch5;
+SET ROLE regress_role_sch5;
+CREATE SCHEMA schema_sch5;
+SET ROLE regress_role_sch5b;
+ALTER SCHEMA schema_sch5 RENAME TO schema_sch5b;
+ALTER SCHEMA schema_sch5b OWNER TO regress_role_sch5b;
+DROP SCHEMA schema_sch5b;
+RESET ROLE;
+REVOKE CREATE ON DATABASE regression FROM regress_role_sch5;
+SET ROLE regress_role_sch5;
+RESET ROLE;
+DROP ROLE regress_role_sch5b;
+DROP ROLE regress_role_sch5;
+-- Should work, REASSIGN OWNED objects to another OWNER
+CREATE ROLE regress_role_sch6;
+GRANT CREATE ON DATABASE regression to regress_role_sch6;
+CREATE ROLE regress_role_sch6b;
+SET ROLE regress_role_sch6;
+CREATE SCHEMA schema_sch6;
+RESET ROLE;
+REVOKE CREATE ON DATABASE regression FROM regress_role_sch6;
+REASSIGN OWNED BY regress_role_sch6 TO regress_role_sch6b;
+SET ROLE regress_role_sch6b;
+DROP SCHEMA schema_sch6;
+RESET ROLE;
+DROP ROLE regress_role_sch6b;
+DROP ROLE regress_role_sch6;
+-- Should fail. Shouldn't RENAME SCHEMA if invalid / already existing / etc.
+CREATE SCHEMA schema_sch7;
+CREATE SCHEMA schema_sch7b;
+ALTER SCHEMA schema_sch7b RENAME TO schema_sch7;
+ERROR:  schema "schema_sch7" already exists
+ALTER SCHEMA schema_sch7b RENAME TO public;
+ERROR:  schema "public" already exists
+ALTER SCHEMA schema_sch7b RENAME TO pg_asdf;
+ERROR:  unacceptable schema name "pg_asdf"
+DETAIL:  The prefix "pg_" is reserved for system schemas.
+DROP SCHEMA schema_sch7;
+DROP SCHEMA schema_sch7b;
+-- Should fail. Shouldn't ALTER SCHEMA if not OWNER
+CREATE SCHEMA schema_sch8;
+CREATE ROLE regress_role_sch8;
+SET ROLE regress_role_sch8;
+ALTER SCHEMA schema_sch8 RENAME TO schema_sch8b;
+ERROR:  must be owner of schema schema_sch8
+ALTER SCHEMA schema_sch8 OWNER TO regress_role_sch8;
+ERROR:  must be owner of schema schema_sch8
+RESET ROLE;
+DROP SCHEMA schema_sch8;
+DROP ROLE regress_role_sch8;
+-- Should work. Non-Owner with CREATE ON DATABASE priviledge can RENAME SCHEMA
+CREATE ROLE regress_role_sch9;
+GRANT CREATE ON DATABASE regression to regress_role_sch9;
+SET ROLE regress_role_sch9;
+CREATE SCHEMA schema_sch9;
+RESET ROLE;
+ALTER SCHEMA schema_sch9 RENAME TO schema_sch9b;
+DROP SCHEMA schema_sch9b;
+REVOKE CREATE ON DATABASE regression FROM regress_role_sch9;
+DROP ROLE regress_role_sch9;
+-- Should fail. OWNER without CREATE ON DATABASE can't ALTER OWNER SCHEMA
+CREATE ROLE regress_role_sch10;
+CREATE SCHEMA schema_sch10 AUTHORIZATION regress_role_sch10;
+CREATE ROLE regress_role_sch10b;
+GRANT regress_role_sch10b TO regress_role_sch10;
+SET ROLE regress_role_sch10;
+ALTER SCHEMA schema_sch10 RENAME TO schema_sch2;
+ERROR:  permission denied for database regression
+ALTER SCHEMA schema_sch10 OWNER TO regress_role_sch10b;
+ERROR:  permission denied for database regression
+RESET ROLE;
+DROP SCHEMA schema_sch10;
+REVOKE regress_role_sch10b FROM regress_role_sch10;
+DROP ROLE regress_role_sch10b;
+DROP ROLE regress_role_sch10;
+-- Should work. Try to have multiple OWNERships for a ROLE
+CREATE ROLE regress_role_sch11;
+CREATE ROLE regress_role_sch11b;
+CREATE SCHEMA schema_sch11;
+GRANT CREATE ON SCHEMA schema_sch11 TO regress_role_sch11;
+GRANT ALL ON SCHEMA schema_sch11 TO regress_role_sch11b;
+ALTER SCHEMA schema_sch11 OWNER TO regress_role_sch11;
+REVOKE CREATE ON SCHEMA schema_sch11 FROM regress_role_sch11;
+REVOKE ALL ON SCHEMA schema_sch11 FROM regress_role_sch11b;
+DROP SCHEMA schema_sch11;
+DROP ROLE regress_role_sch11b;
+DROP ROLE regress_role_sch11;
+-- Change OWNER of SCHEMA
+CREATE SCHEMA schema_sch12;
+CREATE ROLE regress_role_sch12;
+ALTER SCHEMA schema_sch12 OWNER TO regress_role_sch12;
+DROP SCHEMA schema_sch12;
+DROP ROLE regress_role_sch12;
+-- Should fail. Can't change OWNER of SCHEMA if doesn't exist/invalid name/etc.
+ALTER SCHEMA schema_sch13 RENAME TO schema_sch13;
+ERROR:  schema "schema_sch13" does not exist
+CREATE ROLE regress_role_sch13;
+ALTER SCHEMA schema_sch13 OWNER TO regress_role_sch13;
+ERROR:  schema "schema_sch13" does not exist
+DROP ROLE regress_role_sch13;
diff --git a/src/test/regress/sql/namespace.sql b/src/test/regress/sql/namespace.sql
index 879b6c3..45cf37c 100644
--- a/src/test/regress/sql/namespace.sql
+++ b/src/test/regress/sql/namespace.sql
@@ -38,3 +38,138 @@ DROP SCHEMA test_schema_1 CASCADE;
 -- verify that the objects were dropped
 SELECT COUNT(*) FROM pg_class WHERE relnamespace =
     (SELECT oid FROM pg_namespace WHERE nspname = 'test_schema_1');
+
+-- Should fail. Disallow CREATE SCHEMA by ROLE with insufficient permission
+CREATE ROLE regress_role_sch2;
+SET ROLE regress_role_sch2;
+CREATE SCHEMA schema_sch2;
+RESET ROLE;
+DROP ROLE regress_role_sch2;
+
+-- Should fail. Disallow CREATE SCHEMA if using a reserved name
+CREATE SCHEMA pg_schema_sch2b;
+
+-- Should fail. Disallow CREATE SCHEMA if already exists
+CREATE SCHEMA schema_sch2c;
+CREATE SCHEMA schema_sch2c;
+DROP SCHEMA schema_sch2c;
+
+-- Ensure CREATE SCHEMA uses current_user (and not necessarily session_user)
+CREATE ROLE regress_role_sch3;
+GRANT CREATE ON DATABASE regression TO regress_role_sch3;
+SET ROLE regress_role_sch3;
+CREATE SCHEMA schema_sch3;
+SELECT schema_owner
+FROM information_schema.schemata
+WHERE schema_name = 'schema_sch3';
+DROP SCHEMA schema_sch3;
+RESET ROLE;
+REVOKE CREATE ON DATABASE regression FROM regress_role_sch3;
+DROP ROLE regress_role_sch3;
+
+-- Should work. RENAME SCHEMA
+CREATE SCHEMA schema_sch4;
+ALTER SCHEMA schema_sch4 RENAME TO schema_sch4b;
+DROP SCHEMA schema_sch4b;
+
+-- ALTER SCHEMA ok for user created IN ROLE of one with CREATE DATABASE rights
+CREATE ROLE regress_role_sch5;
+GRANT CREATE ON DATABASE regression to regress_role_sch5;
+CREATE ROLE regress_role_sch5b IN ROLE regress_role_sch5;
+SET ROLE regress_role_sch5;
+CREATE SCHEMA schema_sch5;
+SET ROLE regress_role_sch5b;
+ALTER SCHEMA schema_sch5 RENAME TO schema_sch5b;
+ALTER SCHEMA schema_sch5b OWNER TO regress_role_sch5b;
+DROP SCHEMA schema_sch5b;
+RESET ROLE;
+REVOKE CREATE ON DATABASE regression FROM regress_role_sch5;
+SET ROLE regress_role_sch5;
+RESET ROLE;
+DROP ROLE regress_role_sch5b;
+DROP ROLE regress_role_sch5;
+
+-- Should work, REASSIGN OWNED objects to another OWNER
+CREATE ROLE regress_role_sch6;
+GRANT CREATE ON DATABASE regression to regress_role_sch6;
+CREATE ROLE regress_role_sch6b;
+SET ROLE regress_role_sch6;
+CREATE SCHEMA schema_sch6;
+RESET ROLE;
+REVOKE CREATE ON DATABASE regression FROM regress_role_sch6;
+REASSIGN OWNED BY regress_role_sch6 TO regress_role_sch6b;
+SET ROLE regress_role_sch6b;
+DROP SCHEMA schema_sch6;
+RESET ROLE;
+DROP ROLE regress_role_sch6b;
+DROP ROLE regress_role_sch6;
+
+-- Should fail. Shouldn't RENAME SCHEMA if invalid / already existing / etc.
+CREATE SCHEMA schema_sch7;
+CREATE SCHEMA schema_sch7b;
+ALTER SCHEMA schema_sch7b RENAME TO schema_sch7;
+ALTER SCHEMA schema_sch7b RENAME TO public;
+ALTER SCHEMA schema_sch7b RENAME TO pg_asdf;
+DROP SCHEMA schema_sch7;
+DROP SCHEMA schema_sch7b;
+
+-- Should fail. Shouldn't ALTER SCHEMA if not OWNER
+CREATE SCHEMA schema_sch8;
+CREATE ROLE regress_role_sch8;
+SET ROLE regress_role_sch8;
+ALTER SCHEMA schema_sch8 RENAME TO schema_sch8b;
+ALTER SCHEMA schema_sch8 OWNER TO regress_role_sch8;
+RESET ROLE;
+DROP SCHEMA schema_sch8;
+DROP ROLE regress_role_sch8;
+
+-- Should work. Non-Owner with CREATE ON DATABASE priviledge can RENAME SCHEMA
+CREATE ROLE regress_role_sch9;
+GRANT CREATE ON DATABASE regression to regress_role_sch9;
+SET ROLE regress_role_sch9;
+CREATE SCHEMA schema_sch9;
+RESET ROLE;
+ALTER SCHEMA schema_sch9 RENAME TO schema_sch9b;
+DROP SCHEMA schema_sch9b;
+REVOKE CREATE ON DATABASE regression FROM regress_role_sch9;
+DROP ROLE regress_role_sch9;
+
+-- Should fail. OWNER without CREATE ON DATABASE can't ALTER OWNER SCHEMA
+CREATE ROLE regress_role_sch10;
+CREATE SCHEMA schema_sch10 AUTHORIZATION regress_role_sch10;
+CREATE ROLE regress_role_sch10b;
+GRANT regress_role_sch10b TO regress_role_sch10;
+SET ROLE regress_role_sch10;
+ALTER SCHEMA schema_sch10 RENAME TO schema_sch2;
+ALTER SCHEMA schema_sch10 OWNER TO regress_role_sch10b;
+RESET ROLE;
+DROP SCHEMA schema_sch10;
+REVOKE regress_role_sch10b FROM regress_role_sch10;
+DROP ROLE regress_role_sch10b;
+DROP ROLE regress_role_sch10;
+
+-- Should work. Try to have multiple OWNERships for a ROLE
+CREATE ROLE regress_role_sch11;
+CREATE ROLE regress_role_sch11b;
+CREATE SCHEMA schema_sch11;
+GRANT CREATE ON SCHEMA schema_sch11 TO regress_role_sch11;
+GRANT ALL ON SCHEMA schema_sch11 TO regress_role_sch11b;
+ALTER SCHEMA schema_sch11 OWNER TO regress_role_sch11;
+REVOKE CREATE ON SCHEMA schema_sch11 FROM regress_role_sch11;
+REVOKE ALL ON SCHEMA schema_sch11 FROM regress_role_sch11b;
+DROP SCHEMA schema_sch11;
+DROP ROLE regress_role_sch11b;
+DROP ROLE regress_role_sch11;
+
+-- Change OWNER of SCHEMA
+CREATE SCHEMA schema_sch12;
+CREATE ROLE regress_role_sch12;
+ALTER SCHEMA schema_sch12 OWNER TO regress_role_sch12;
+DROP SCHEMA schema_sch12;
+DROP ROLE regress_role_sch12;
+
+-- Should fail. Can't change OWNER of SCHEMA if doesn't exist/invalid name/etc.
+ALTER SCHEMA schema_sch13 RENAME TO schema_sch13;
+CREATE ROLE regress_role_sch13;
+ALTER SCHEMA schema_sch13 OWNER TO regress_role_sch13;
+DROP ROLE regress_role_sch13;

#14

Fabien COELHO

coelho@cri.ensmp.fr

over 12 years ago

In reply to: Robins Tharakan (#13)

Re: Patch to add regression tests for SCHEMA

PFA an updated patch:
- Renamed ROLEs as per new feedback (although the previous naming was also
as per an earlier feedback)
- Merged tests into namespace

I do not see any difference with v4. I guess you sent the earlier version.

--
Fabien.

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

#15

Fabien COELHO

coelho@cri.ensmp.fr

over 12 years ago

In reply to: Fabien COELHO (#14)

Re: Patch to add regression tests for SCHEMA

PFA an updated patch:
- Renamed ROLEs as per new feedback (although the previous naming was also
as per an earlier feedback)
- Merged tests into namespace

I do not see any difference with v4. I guess you sent the earlier version.

Sorry, wrong thread, this apply to SEQUENCE tests.

--
Fabien.

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

#16

Fabien COELHO

coelho@cri.ensmp.fr

over 12 years ago

In reply to: Robins Tharakan (#13)

Re: Patch to add regression tests for SCHEMA

Hello Robins,

PFA an updated patch:
- Renamed ROLEs as per new feedback (although the previous naming was also
as per an earlier feedback)
- Merged tests into namespace

I cannot apply the patch, it seems to be truncated:

sh> git apply ../regress_schema_v5.patch
### warnings about trailing whitespace, then:
fatal: corrupt patch at line 291

Another go with "patch":

sh> patch -p1 < ../regress_schema_v5.patch
...
patch unexpectedly ends in middle of line
patch: **** malformed patch at line 290:

I have this:

sh> cksum ../regress_schema_v5.patch
985580529 11319 ../regress_schema_v5.patch

--
Fabien.

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

#17

Robins Tharakan

tharakan@gmail.com

over 12 years ago

In reply to: Fabien COELHO (#16)

Re: Patch to add regression tests for SCHEMA

Hi Fabien,

Appreciate you being able to check right away.
Seems something went wrong with these set of patches... Would check again
and resubmit them soon.

--
Robins Tharakan

On 9 July 2013 10:57, Fabien COELHO <coelho@cri.ensmp.fr> wrote:

Show quoted text

Hello Robins,

PFA an updated patch:

- Renamed ROLEs as per new feedback (although the previous naming was also
as per an earlier feedback)
- Merged tests into namespace

I cannot apply the patch, it seems to be truncated:

sh> git apply ../regress_schema_v5.patch
### warnings about trailing whitespace, then:
fatal: corrupt patch at line 291

Another go with "patch":

sh> patch -p1 < ../regress_schema_v5.patch
...
patch unexpectedly ends in middle of line
patch: **** malformed patch at line 290:

I have this:

sh> cksum ../regress_schema_v5.patch
985580529 11319 ../regress_schema_v5.patch

--
Fabien.

#18

Robins Tharakan

tharakan@gmail.com

over 12 years ago

In reply to: Fabien COELHO (#16)

Re: Patch to add regression tests for SCHEMA

On 9 July 2013 08:57, Fabien COELHO <coelho@cri.ensmp.fr> wrote:

I cannot apply the patch, it seems to be truncated:

Hi Fabien,

Please find attached the updated patch.

It seems the only difference between v5 and v6 is probably a newline at the
end (Line 291 was the last line), in fact meld doesn't even show anything.
I'll try to be more careful though.

git reset --hard HEAD
git pull
patch -p1 < ../regress_schema_v6.patch
patch -p1 -R < ../regress_schema_v6.patch
patch -p1 < ../regress_schema_v6.patch
make clean
./configure --enable-depend --enable-coverage --enable-cassert
--enable-debug
make -j3 check

Do let me know if something is still amiss.
--
Robins Tharakan

#19

Robins Tharakan

tharakan@gmail.com

over 12 years ago

In reply to: Robins Tharakan (#18)

1 attachment(s)

Re: Patch to add regression tests for SCHEMA

Hi Fabien,

Please find attached the updated patch.

It seems the only difference between v5 and v6 is probably a newline at the
end (Line 291 was the last line), in fact meld doesn't even show anything.
I'll try to be more careful though.

Do let me know if something is still amiss.
--
Robins Tharakan

Attachments:

regress_schema_v6.patchapplication/octet-stream; name=regress_schema_v6.patchDownload

diff --git a/src/test/regress/expected/namespace.out b/src/test/regress/expected/namespace.out
index 9187c81..675da18 100644
--- a/src/test/regress/expected/namespace.out
+++ b/src/test/regress/expected/namespace.out
@@ -61,3 +61,143 @@ SELECT COUNT(*) FROM pg_class WHERE relnamespace =
      0
 (1 row)
 
+-- Should fail. Disallow CREATE SCHEMA by ROLE with insufficient permission
+CREATE ROLE regress_role_sch2;
+SET ROLE regress_role_sch2;
+CREATE SCHEMA schema_sch2;
+ERROR:  permission denied for database regression
+RESET ROLE;
+DROP ROLE regress_role_sch2;
+-- Should fail. Disallow CREATE SCHEMA if using a reserved name
+CREATE SCHEMA pg_schema_sch2b;
+ERROR:  unacceptable schema name "pg_schema_sch2b"
+DETAIL:  The prefix "pg_" is reserved for system schemas.
+-- Should fail. Disallow CREATE SCHEMA if already exists
+CREATE SCHEMA schema_sch2c;
+CREATE SCHEMA schema_sch2c;
+ERROR:  schema "schema_sch2c" already exists
+DROP SCHEMA schema_sch2c;
+-- Ensure CREATE SCHEMA uses current_user (and not necessarily session_user)
+CREATE ROLE regress_role_sch3;
+GRANT CREATE ON DATABASE regression TO regress_role_sch3;
+SET ROLE regress_role_sch3;
+CREATE SCHEMA schema_sch3;
+SELECT schema_owner
+FROM information_schema.schemata
+WHERE schema_name = 'schema_sch3';
+   schema_owner    
+-------------------
+ regress_role_sch3
+(1 row)
+
+DROP SCHEMA schema_sch3;
+RESET ROLE;
+REVOKE CREATE ON DATABASE regression FROM regress_role_sch3;
+DROP ROLE regress_role_sch3;
+-- Should work. RENAME SCHEMA
+CREATE SCHEMA schema_sch4;
+ALTER SCHEMA schema_sch4 RENAME TO schema_sch4b;
+DROP SCHEMA schema_sch4b;
+-- ALTER SCHEMA ok for user created IN ROLE of one with CREATE DATABASE rights
+CREATE ROLE regress_role_sch5;
+GRANT CREATE ON DATABASE regression to regress_role_sch5;
+CREATE ROLE regress_role_sch5b IN ROLE regress_role_sch5;
+SET ROLE regress_role_sch5;
+CREATE SCHEMA schema_sch5;
+SET ROLE regress_role_sch5b;
+ALTER SCHEMA schema_sch5 RENAME TO schema_sch5b;
+ALTER SCHEMA schema_sch5b OWNER TO regress_role_sch5b;
+DROP SCHEMA schema_sch5b;
+RESET ROLE;
+REVOKE CREATE ON DATABASE regression FROM regress_role_sch5;
+SET ROLE regress_role_sch5;
+RESET ROLE;
+DROP ROLE regress_role_sch5b;
+DROP ROLE regress_role_sch5;
+-- Should work, REASSIGN OWNED objects to another OWNER
+CREATE ROLE regress_role_sch6;
+GRANT CREATE ON DATABASE regression to regress_role_sch6;
+CREATE ROLE regress_role_sch6b;
+SET ROLE regress_role_sch6;
+CREATE SCHEMA schema_sch6;
+RESET ROLE;
+REVOKE CREATE ON DATABASE regression FROM regress_role_sch6;
+REASSIGN OWNED BY regress_role_sch6 TO regress_role_sch6b;
+SET ROLE regress_role_sch6b;
+DROP SCHEMA schema_sch6;
+RESET ROLE;
+DROP ROLE regress_role_sch6b;
+DROP ROLE regress_role_sch6;
+-- Should fail. Shouldn't RENAME SCHEMA if invalid / already existing / etc.
+CREATE SCHEMA schema_sch7;
+CREATE SCHEMA schema_sch7b;
+ALTER SCHEMA schema_sch7b RENAME TO schema_sch7;
+ERROR:  schema "schema_sch7" already exists
+ALTER SCHEMA schema_sch7b RENAME TO public;
+ERROR:  schema "public" already exists
+ALTER SCHEMA schema_sch7b RENAME TO pg_asdf;
+ERROR:  unacceptable schema name "pg_asdf"
+DETAIL:  The prefix "pg_" is reserved for system schemas.
+DROP SCHEMA schema_sch7;
+DROP SCHEMA schema_sch7b;
+-- Should fail. Shouldn't ALTER SCHEMA if not OWNER
+CREATE SCHEMA schema_sch8;
+CREATE ROLE regress_role_sch8;
+SET ROLE regress_role_sch8;
+ALTER SCHEMA schema_sch8 RENAME TO schema_sch8b;
+ERROR:  must be owner of schema schema_sch8
+ALTER SCHEMA schema_sch8 OWNER TO regress_role_sch8;
+ERROR:  must be owner of schema schema_sch8
+RESET ROLE;
+DROP SCHEMA schema_sch8;
+DROP ROLE regress_role_sch8;
+-- Should work. Non-Owner with CREATE ON DATABASE priviledge can RENAME SCHEMA
+CREATE ROLE regress_role_sch9;
+GRANT CREATE ON DATABASE regression to regress_role_sch9;
+SET ROLE regress_role_sch9;
+CREATE SCHEMA schema_sch9;
+RESET ROLE;
+ALTER SCHEMA schema_sch9 RENAME TO schema_sch9b;
+DROP SCHEMA schema_sch9b;
+REVOKE CREATE ON DATABASE regression FROM regress_role_sch9;
+DROP ROLE regress_role_sch9;
+-- Should fail. OWNER without CREATE ON DATABASE can't ALTER OWNER SCHEMA
+CREATE ROLE regress_role_sch10;
+CREATE SCHEMA schema_sch10 AUTHORIZATION regress_role_sch10;
+CREATE ROLE regress_role_sch10b;
+GRANT regress_role_sch10b TO regress_role_sch10;
+SET ROLE regress_role_sch10;
+ALTER SCHEMA schema_sch10 RENAME TO schema_sch2;
+ERROR:  permission denied for database regression
+ALTER SCHEMA schema_sch10 OWNER TO regress_role_sch10b;
+ERROR:  permission denied for database regression
+RESET ROLE;
+DROP SCHEMA schema_sch10;
+REVOKE regress_role_sch10b FROM regress_role_sch10;
+DROP ROLE regress_role_sch10b;
+DROP ROLE regress_role_sch10;
+-- Should work. Try to have multiple OWNERships for a ROLE
+CREATE ROLE regress_role_sch11;
+CREATE ROLE regress_role_sch11b;
+CREATE SCHEMA schema_sch11;
+GRANT CREATE ON SCHEMA schema_sch11 TO regress_role_sch11;
+GRANT ALL ON SCHEMA schema_sch11 TO regress_role_sch11b;
+ALTER SCHEMA schema_sch11 OWNER TO regress_role_sch11;
+REVOKE CREATE ON SCHEMA schema_sch11 FROM regress_role_sch11;
+REVOKE ALL ON SCHEMA schema_sch11 FROM regress_role_sch11b;
+DROP SCHEMA schema_sch11;
+DROP ROLE regress_role_sch11b;
+DROP ROLE regress_role_sch11;
+-- Change OWNER of SCHEMA
+CREATE SCHEMA schema_sch12;
+CREATE ROLE regress_role_sch12;
+ALTER SCHEMA schema_sch12 OWNER TO regress_role_sch12;
+DROP SCHEMA schema_sch12;
+DROP ROLE regress_role_sch12;
+-- Should fail. Can't change OWNER of SCHEMA if doesn't exist/invalid name/etc.
+ALTER SCHEMA schema_sch13 RENAME TO schema_sch13;
+ERROR:  schema "schema_sch13" does not exist
+CREATE ROLE regress_role_sch13;
+ALTER SCHEMA schema_sch13 OWNER TO regress_role_sch13;
+ERROR:  schema "schema_sch13" does not exist
+DROP ROLE regress_role_sch13;
diff --git a/src/test/regress/sql/namespace.sql b/src/test/regress/sql/namespace.sql
index 879b6c3..45cf37c 100644
--- a/src/test/regress/sql/namespace.sql
+++ b/src/test/regress/sql/namespace.sql
@@ -38,3 +38,138 @@ DROP SCHEMA test_schema_1 CASCADE;
 -- verify that the objects were dropped
 SELECT COUNT(*) FROM pg_class WHERE relnamespace =
     (SELECT oid FROM pg_namespace WHERE nspname = 'test_schema_1');
+
+-- Should fail. Disallow CREATE SCHEMA by ROLE with insufficient permission
+CREATE ROLE regress_role_sch2;
+SET ROLE regress_role_sch2;
+CREATE SCHEMA schema_sch2;
+RESET ROLE;
+DROP ROLE regress_role_sch2;
+
+-- Should fail. Disallow CREATE SCHEMA if using a reserved name
+CREATE SCHEMA pg_schema_sch2b;
+
+-- Should fail. Disallow CREATE SCHEMA if already exists
+CREATE SCHEMA schema_sch2c;
+CREATE SCHEMA schema_sch2c;
+DROP SCHEMA schema_sch2c;
+
+-- Ensure CREATE SCHEMA uses current_user (and not necessarily session_user)
+CREATE ROLE regress_role_sch3;
+GRANT CREATE ON DATABASE regression TO regress_role_sch3;
+SET ROLE regress_role_sch3;
+CREATE SCHEMA schema_sch3;
+SELECT schema_owner
+FROM information_schema.schemata
+WHERE schema_name = 'schema_sch3';
+DROP SCHEMA schema_sch3;
+RESET ROLE;
+REVOKE CREATE ON DATABASE regression FROM regress_role_sch3;
+DROP ROLE regress_role_sch3;
+
+-- Should work. RENAME SCHEMA
+CREATE SCHEMA schema_sch4;
+ALTER SCHEMA schema_sch4 RENAME TO schema_sch4b;
+DROP SCHEMA schema_sch4b;
+
+-- ALTER SCHEMA ok for user created IN ROLE of one with CREATE DATABASE rights
+CREATE ROLE regress_role_sch5;
+GRANT CREATE ON DATABASE regression to regress_role_sch5;
+CREATE ROLE regress_role_sch5b IN ROLE regress_role_sch5;
+SET ROLE regress_role_sch5;
+CREATE SCHEMA schema_sch5;
+SET ROLE regress_role_sch5b;
+ALTER SCHEMA schema_sch5 RENAME TO schema_sch5b;
+ALTER SCHEMA schema_sch5b OWNER TO regress_role_sch5b;
+DROP SCHEMA schema_sch5b;
+RESET ROLE;
+REVOKE CREATE ON DATABASE regression FROM regress_role_sch5;
+SET ROLE regress_role_sch5;
+RESET ROLE;
+DROP ROLE regress_role_sch5b;
+DROP ROLE regress_role_sch5;
+
+-- Should work, REASSIGN OWNED objects to another OWNER
+CREATE ROLE regress_role_sch6;
+GRANT CREATE ON DATABASE regression to regress_role_sch6;
+CREATE ROLE regress_role_sch6b;
+SET ROLE regress_role_sch6;
+CREATE SCHEMA schema_sch6;
+RESET ROLE;
+REVOKE CREATE ON DATABASE regression FROM regress_role_sch6;
+REASSIGN OWNED BY regress_role_sch6 TO regress_role_sch6b;
+SET ROLE regress_role_sch6b;
+DROP SCHEMA schema_sch6;
+RESET ROLE;
+DROP ROLE regress_role_sch6b;
+DROP ROLE regress_role_sch6;
+
+-- Should fail. Shouldn't RENAME SCHEMA if invalid / already existing / etc.
+CREATE SCHEMA schema_sch7;
+CREATE SCHEMA schema_sch7b;
+ALTER SCHEMA schema_sch7b RENAME TO schema_sch7;
+ALTER SCHEMA schema_sch7b RENAME TO public;
+ALTER SCHEMA schema_sch7b RENAME TO pg_asdf;
+DROP SCHEMA schema_sch7;
+DROP SCHEMA schema_sch7b;
+
+-- Should fail. Shouldn't ALTER SCHEMA if not OWNER
+CREATE SCHEMA schema_sch8;
+CREATE ROLE regress_role_sch8;
+SET ROLE regress_role_sch8;
+ALTER SCHEMA schema_sch8 RENAME TO schema_sch8b;
+ALTER SCHEMA schema_sch8 OWNER TO regress_role_sch8;
+RESET ROLE;
+DROP SCHEMA schema_sch8;
+DROP ROLE regress_role_sch8;
+
+-- Should work. Non-Owner with CREATE ON DATABASE priviledge can RENAME SCHEMA
+CREATE ROLE regress_role_sch9;
+GRANT CREATE ON DATABASE regression to regress_role_sch9;
+SET ROLE regress_role_sch9;
+CREATE SCHEMA schema_sch9;
+RESET ROLE;
+ALTER SCHEMA schema_sch9 RENAME TO schema_sch9b;
+DROP SCHEMA schema_sch9b;
+REVOKE CREATE ON DATABASE regression FROM regress_role_sch9;
+DROP ROLE regress_role_sch9;
+
+-- Should fail. OWNER without CREATE ON DATABASE can't ALTER OWNER SCHEMA
+CREATE ROLE regress_role_sch10;
+CREATE SCHEMA schema_sch10 AUTHORIZATION regress_role_sch10;
+CREATE ROLE regress_role_sch10b;
+GRANT regress_role_sch10b TO regress_role_sch10;
+SET ROLE regress_role_sch10;
+ALTER SCHEMA schema_sch10 RENAME TO schema_sch2;
+ALTER SCHEMA schema_sch10 OWNER TO regress_role_sch10b;
+RESET ROLE;
+DROP SCHEMA schema_sch10;
+REVOKE regress_role_sch10b FROM regress_role_sch10;
+DROP ROLE regress_role_sch10b;
+DROP ROLE regress_role_sch10;
+
+-- Should work. Try to have multiple OWNERships for a ROLE
+CREATE ROLE regress_role_sch11;
+CREATE ROLE regress_role_sch11b;
+CREATE SCHEMA schema_sch11;
+GRANT CREATE ON SCHEMA schema_sch11 TO regress_role_sch11;
+GRANT ALL ON SCHEMA schema_sch11 TO regress_role_sch11b;
+ALTER SCHEMA schema_sch11 OWNER TO regress_role_sch11;
+REVOKE CREATE ON SCHEMA schema_sch11 FROM regress_role_sch11;
+REVOKE ALL ON SCHEMA schema_sch11 FROM regress_role_sch11b;
+DROP SCHEMA schema_sch11;
+DROP ROLE regress_role_sch11b;
+DROP ROLE regress_role_sch11;
+
+-- Change OWNER of SCHEMA
+CREATE SCHEMA schema_sch12;
+CREATE ROLE regress_role_sch12;
+ALTER SCHEMA schema_sch12 OWNER TO regress_role_sch12;
+DROP SCHEMA schema_sch12;
+DROP ROLE regress_role_sch12;
+
+-- Should fail. Can't change OWNER of SCHEMA if doesn't exist/invalid name/etc.
+ALTER SCHEMA schema_sch13 RENAME TO schema_sch13;
+CREATE ROLE regress_role_sch13;
+ALTER SCHEMA schema_sch13 OWNER TO regress_role_sch13;
+DROP ROLE regress_role_sch13;

#20

Fabien COELHO

coelho@cri.ensmp.fr

over 12 years ago

In reply to: Robins Tharakan (#19)

Re: Patch to add regression tests for SCHEMA

I've looked this version.

The only reservation I have is that when changing the owner of a schema,
the new owner is not always checked. I would suggest to query the new
owner to check that it matches (5, 11, 12), just as you do in 3.

Also, reowning is tested several times (5, 11, 12). I would suggest to
remove 12 which does not bring much new things after both 5 and 11 get
passed ?

Otherwise the patch applies (with a minor warning about spaces on line 33)
passes for me, and brings valuable new test coverage.

--
Fabien.

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