From cf589c9c1f5f7f89018684b763d74d7b02a2504b Mon Sep 17 00:00:00 2001 From: Simon Riggs Date: Wed, 11 Dec 2013 20:45:15 +0000 Subject: [PATCH] Regression tests for SCHEMA commands Hari Babu Kommi reviewed by David Rowley --- src/test/regress/expected/namespace.out | 26 ++++++++++++++-------- src/test/regress/expected/privileges.out | 28 ++++++++++++++++++++++++ src/test/regress/sql/namespace.sql | 14 +++++++----- src/test/regress/sql/privileges.sql | 27 +++++++++++++++++++++++ 4 files changed, 81 insertions(+), 14 deletions(-) diff --git a/src/test/regress/expected/namespace.out b/src/test/regress/expected/namespace.out index 9187c8126a..b0cdd65af3 100644 --- a/src/test/regress/expected/namespace.out +++ b/src/test/regress/expected/namespace.out @@ -36,12 +36,20 @@ SELECT * FROM test_schema_1.abc_view; 4 | (3 rows) +ALTER SCHEMA test_schema_1 RENAME TO test_schema_renamed; +SELECT COUNT(*) FROM pg_class WHERE relnamespace = + (SELECT oid FROM pg_namespace WHERE nspname = 'test_schema_1'); + count +------- + 0 +(1 row) + -- test IF NOT EXISTS cases -CREATE SCHEMA test_schema_1; -- fail, already exists -ERROR: schema "test_schema_1" already exists -CREATE SCHEMA IF NOT EXISTS test_schema_1; -- ok with notice -NOTICE: schema "test_schema_1" already exists, skipping -CREATE SCHEMA IF NOT EXISTS test_schema_1 -- fail, disallowed +CREATE SCHEMA test_schema_renamed; -- fail, already exists +ERROR: schema "test_schema_renamed" already exists +CREATE SCHEMA IF NOT EXISTS test_schema_renamed; -- ok with notice +NOTICE: schema "test_schema_renamed" already exists, skipping +CREATE SCHEMA IF NOT EXISTS test_schema_renamed -- fail, disallowed CREATE TABLE abc ( a serial, b int UNIQUE @@ -49,13 +57,13 @@ CREATE SCHEMA IF NOT EXISTS test_schema_1 -- fail, disallowed ERROR: CREATE SCHEMA IF NOT EXISTS cannot include schema elements LINE 2: CREATE TABLE abc ( ^ -DROP SCHEMA test_schema_1 CASCADE; +DROP SCHEMA test_schema_renamed CASCADE; NOTICE: drop cascades to 2 other objects -DETAIL: drop cascades to table test_schema_1.abc -drop cascades to view test_schema_1.abc_view +DETAIL: drop cascades to table test_schema_renamed.abc +drop cascades to view test_schema_renamed.abc_view -- verify that the objects were dropped SELECT COUNT(*) FROM pg_class WHERE relnamespace = - (SELECT oid FROM pg_namespace WHERE nspname = 'test_schema_1'); + (SELECT oid FROM pg_namespace WHERE nspname = 'test_schema_renamed'); count ------- 0 diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out index 3da03fc9ae..fa574d744e 100644 --- a/src/test/regress/expected/privileges.out +++ b/src/test/regress/expected/privileges.out @@ -1350,6 +1350,34 @@ SELECT has_function_privilege('regressuser1', 'testns.testfunc(int)', 'EXECUTE') SET client_min_messages TO 'warning'; DROP SCHEMA testns CASCADE; RESET client_min_messages; +-- Change owner of the schema & and rename of new schema owner +\c - +CREATE ROLE schemauser1 superuser login; +CREATE ROLE schemauser2 superuser login; +SET SESSION ROLE schemauser1; +CREATE SCHEMA testns; +SELECT nspname, rolname FROM pg_namespace, pg_roles WHERE pg_namespace.nspname = 'testns' AND pg_namespace.nspowner = pg_roles.oid; + nspname | rolname +---------+------------- + testns | schemauser1 +(1 row) + +ALTER SCHEMA testns OWNER TO schemauser2; +ALTER ROLE schemauser2 RENAME TO schemauser_renamed; +SELECT nspname, rolname FROM pg_namespace, pg_roles WHERE pg_namespace.nspname = 'testns' AND pg_namespace.nspowner = pg_roles.oid; + nspname | rolname +---------+-------------------- + testns | schemauser_renamed +(1 row) + +set session role schemauser_renamed; +SET client_min_messages TO 'warning'; +DROP SCHEMA testns CASCADE; +RESET client_min_messages; +-- clean up +\c - +DROP ROLE schemauser1; +DROP ROLE schemauser_renamed; -- test that dependent privileges are revoked (or not) properly \c - set session role regressuser1; diff --git a/src/test/regress/sql/namespace.sql b/src/test/regress/sql/namespace.sql index 879b6c35b0..51cb091cc5 100644 --- a/src/test/regress/sql/namespace.sql +++ b/src/test/regress/sql/namespace.sql @@ -24,17 +24,21 @@ INSERT INTO test_schema_1.abc DEFAULT VALUES; SELECT * FROM test_schema_1.abc; SELECT * FROM test_schema_1.abc_view; +ALTER SCHEMA test_schema_1 RENAME TO test_schema_renamed; +SELECT COUNT(*) FROM pg_class WHERE relnamespace = + (SELECT oid FROM pg_namespace WHERE nspname = 'test_schema_1'); + -- test IF NOT EXISTS cases -CREATE SCHEMA test_schema_1; -- fail, already exists -CREATE SCHEMA IF NOT EXISTS test_schema_1; -- ok with notice -CREATE SCHEMA IF NOT EXISTS test_schema_1 -- fail, disallowed +CREATE SCHEMA test_schema_renamed; -- fail, already exists +CREATE SCHEMA IF NOT EXISTS test_schema_renamed; -- ok with notice +CREATE SCHEMA IF NOT EXISTS test_schema_renamed -- fail, disallowed CREATE TABLE abc ( a serial, b int UNIQUE ); -DROP SCHEMA test_schema_1 CASCADE; +DROP SCHEMA test_schema_renamed CASCADE; -- verify that the objects were dropped SELECT COUNT(*) FROM pg_class WHERE relnamespace = - (SELECT oid FROM pg_namespace WHERE nspname = 'test_schema_1'); + (SELECT oid FROM pg_namespace WHERE nspname = 'test_schema_renamed'); diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql index cb993ae2b0..38f8695475 100644 --- a/src/test/regress/sql/privileges.sql +++ b/src/test/regress/sql/privileges.sql @@ -813,6 +813,33 @@ DROP SCHEMA testns CASCADE; RESET client_min_messages; +-- Change owner of the schema & and rename of new schema owner +\c - + +CREATE ROLE schemauser1 superuser login; +CREATE ROLE schemauser2 superuser login; + +SET SESSION ROLE schemauser1; +CREATE SCHEMA testns; + +SELECT nspname, rolname FROM pg_namespace, pg_roles WHERE pg_namespace.nspname = 'testns' AND pg_namespace.nspowner = pg_roles.oid; + +ALTER SCHEMA testns OWNER TO schemauser2; +ALTER ROLE schemauser2 RENAME TO schemauser_renamed; +SELECT nspname, rolname FROM pg_namespace, pg_roles WHERE pg_namespace.nspname = 'testns' AND pg_namespace.nspowner = pg_roles.oid; + +set session role schemauser_renamed; +SET client_min_messages TO 'warning'; +DROP SCHEMA testns CASCADE; +RESET client_min_messages; + +-- clean up +\c - + +DROP ROLE schemauser1; +DROP ROLE schemauser_renamed; + + -- test that dependent privileges are revoked (or not) properly \c - -- 2.40.0