diff --git a/src/test/regress/expected/database.out b/src/test/regress/expected/database.out new file mode 100644 index 0000000..f8f30b7 --- /dev/null +++ b/src/test/regress/expected/database.out @@ -0,0 +1,203 @@ +-- +-- tests functions in dbcommands.c script +-- +-- Should work. Create Database +CREATE DATABASE db_db1; +DROP DATABASE db_db1; +-- Should fail. Can't DROP DATABASE that doesn't exist / is reserved +DROP DATABASE db_db2; +ERROR: database "db_db2" does not exist +DROP DATABASE IF EXISTS db_db2; +NOTICE: database "db_db2" does not exist, skipping +DROP DATABASE template1; +ERROR: cannot drop a template database +DROP DATABASE regression; +ERROR: cannot drop the currently open database +-- Should fail. Can't DROP DATABASE inside a transaction +BEGIN TRANSACTION; +CREATE DATABASE db_db3; +ERROR: CREATE DATABASE cannot run inside a transaction block +DROP DATABASE db_db3; +ERROR: current transaction is aborted, commands ignored until end of transaction block +ROLLBACK; +-- Should fail. DROP DATABASE requires CREATEDB permissions +CREATE ROLE rol_db4; +CREATE DATABASE db_db4; +SET ROLE rol_db4; +DROP DATABASE db_db4; +ERROR: must be owner of database db_db4 +RESET ROLE; +DROP DATABASE db_db4; +DROP ROLE rol_db4; +-- Should work. ALTER DATABASE RENAME TO +CREATE DATABASE db_db5; +ALTER DATABASE db_db5 RENAME TO db_db5b; +DROP DATABASE db_db5b; +-- Should fail. ALTER DATABASE RENAME TO on non-existent DB +CREATE DATABASE db_db6; +ALTER DATABASE asdf RENAME TO db_db6a; +ERROR: database "asdf" does not exist +ALTER DATABASE db_db6 RENAME TO db_db6; +ERROR: database "db_db6" already exists +ALTER DATABASE db_db6 RENAME TO db_db6a; +DROP DATABASE db_db6a; +-- Should fail. ALTER DATABASE RENAME TO without permission +CREATE ROLE rol_db7; +CREATE DATABASE db_db7; +SET ROLE rol_db7; +ALTER DATABASE db_db7 RENAME TO db_db7a; +ERROR: must be owner of database db_db7 +RESET ROLE; +DROP DATABASE db_db7; +DROP ROLE rol_db7; +-- Should fail. ALTER DATABASE RENAME TO with OWNER rights without CREATEDB +CREATE ROLE rol_db8; +CREATE DATABASE db_db8 WITH OWNER rol_db8; +SET ROLE rol_db8; +ALTER DATABASE db_db8 RENAME TO db_db8a; +ERROR: permission denied to rename database +ALTER DATABASE db_db8 RENAME TO db_db8a; +ERROR: permission denied to rename database +RESET ROLE; +DROP DATABASE db_db8; +DROP ROLE rol_db8; +-- Should work. ALTER DATABASE RENAME TO with OWNER and CREATEDB privilege +CREATE ROLE rol_db9 CREATEDB; +CREATE DATABASE db_db9 WITH OWNER rol_db9; +SET ROLE rol_db9; +ALTER DATABASE db_db9 RENAME TO regression; +ERROR: database "regression" already exists +ALTER DATABASE db_db9 RENAME TO db_db9a; +RESET ROLE; +DROP DATABASE db_db9a; +DROP ROLE rol_db9; +-- Should fail. Can't rename current database +ALTER DATABASE regression RENAME TO regression_a; +ERROR: current database cannot be renamed +-- Should work. ALTER DATABASE SET with some valid / invalid values +CREATE DATABASE db_db10; +ALTER DATABASE db_db10 SET ASDF; +ERROR: syntax error at or near ";" +LINE 1: ALTER DATABASE db_db10 SET ASDF; + ^ +ALTER DATABASE db_db10 WITH CONNECTION LIMIT = -100; +ERROR: invalid connection limit: -100 +ALTER DATABASE db_db10 WITH CONNECTION LIMIT = -1; +ALTER DATABASE db_db10 WITH CONNECTION LIMIT = 0; +ALTER DATABASE db_db10 WITH CONNECTION LIMIT = 100; +ALTER DATABASE db_db10 SET SEED = 0.5; +DROP DATABASE db_db10; +-- Should fail. Can't set Tablespace within transaction +CREATE DATABASE db_db11; +BEGIN TRANSACTION; +ALTER DATABASE db_db11 SET TABLESPACE asdf; +ERROR: ALTER DATABASE SET TABLESPACE cannot run inside a transaction block +ROLLBACK; +DROP DATABASE db_db11; +-- Should fail. ALTER DATABASE with invalid connection limit +CREATE DATABASE db_db12; +BEGIN TRANSACTION; +ALTER DATABASE db_db12 WITH CONNECTION LIMIT = 8ASDF; +ERROR: syntax error at or near "ASDF" +LINE 1: ALTER DATABASE db_db12 WITH CONNECTION LIMIT = 8ASDF; + ^ +ROLLBACK; +DROP DATABASE db_db12; +-- Should work. ALTER DATABASE SET with OWNER rights without CREATEDB +CREATE ROLE rol_db13; +CREATE DATABASE db_db13 WITH OWNER rol_db13; +SET ROLE rol_db13; +ALTER DATABASE db_db13 SET SEED=0.5; +RESET ROLE; +DROP DATABASE db_db13; +DROP ROLE rol_db13; +-- Should work. ALTER DATABASE OWNER TO with OWNER rights +CREATE ROLE rol_db14; +CREATE ROLE rol_db15; +CREATE DATABASE db_db14 WITH OWNER rol_db14; +SET ROLE rol_db15; +ALTER DATABASE db_db14 OWNER TO rol_db15; -- OWNERship required +ERROR: must be owner of database db_db14 +RESET ROLE; +SET ROLE rol_db14; +ALTER DATABASE db_db14 OWNER TO rol_db15; -- CREATEDB required for grantor +ERROR: must be member of role "rol_db15" +RESET ROLE; +ALTER DATABASE asdf OWNER TO rol_db15; -- bogus database +ERROR: database "asdf" does not exist +ALTER DATABASE db_db14 OWNER TO asdf; -- bogus to owner +ERROR: role "asdf" does not exist +ALTER DATABASE db_db14 OWNER TO rol_db14; -- reassign to self +ALTER DATABASE db_db14 OWNER TO rol_db15; +DROP DATABASE db_db14; +DROP ROLE rol_db15; +DROP ROLE rol_db14; +-- Should fail. ALTER DATABASE SET TABLESPACE with invalid name +ALTER DATABASE db_db16 SET TABLESPACE asdf; +ERROR: database "db_db16" does not exist +-- Should fail. ALTER DATABASE SET TABLESPACE without OWNER privilege +CREATE ROLE rol_db16; +CREATE DATABASE db_db16; +SET ROLE rol_db16; +ALTER DATABASE db_db16 SET TABLESPACE asdf; +ERROR: must be owner of database db_db16 +RESET ROLE; +DROP DATABASE db_db16; +DROP ROLE rol_db16; +-- Should fail. ALTER DATABASE SET TABLESPACE without OWNER privilege +ALTER DATABASE regression SET TABLESPACE asdf; +ERROR: cannot change the tablespace of the currently open database +-- Should fail. ALTER DATABASE SET TABLESPACE with OWNER / CREATEDB privilege +CREATE ROLE rol_db17 CREATEDB; +CREATE DATABASE db_db17 OWNER rol_db17; +SET ROLE rol_db17; +ALTER DATABASE db_db17 SET TABLESPACE DEFAULT; +ERROR: syntax error at or near "DEFAULT" +LINE 1: ALTER DATABASE db_db17 SET TABLESPACE DEFAULT; + ^ +RESET ROLE; +DROP DATABASE db_db17; +DROP ROLE rol_db17; +-- Should work. CREATE DATABASE with valid values +CREATE DATABASE db_db18a CONNECTION LIMIT 10; +DROP DATABASE db_db18a; +-- Should fail. CREATE DATABASE with invalid values +CREATE DATABASE db_db18 ENCODING 'invalid_encoding'; +ERROR: invalid_encoding is not a valid encoding name +CREATE DATABASE db_db18 ENCODING 123456789; +ERROR: 123456789 is not a valid encoding code +CREATE DATABASE db_db18 CONNECTION LIMIT -10; +ERROR: invalid connection limit: -10 +CREATE DATABASE db_db18 TEMPLATE invalid_template; +ERROR: template database "invalid_template" does not exist +CREATE DATABASE regression; +ERROR: database "regression" already exists +-- Should fail. CREATE DATABASE using non-template as template without SUPERUSER +CREATE ROLE rol_db19 CREATEDB; +CREATE DATABASE db_db19; +SET ROLE rol_db19; +CREATE DATABASE db_db20 template db_db19; +ERROR: permission denied to copy database "db_db19" +RESET ROLE; +DROP DATABASE db_db19; +DROP ROLE rol_db19; +-- Should fail. Self trying CREATE / ALTER / DROP DATABASE without rights +CREATE ROLE rol_db21; +CREATE DATABASE db_db21; +SET ROLE rol_db21; +CREATE DATABASE db_db21a; +ERROR: permission denied to create database +ALTER DATABASE db_db21 OWNER TO rol_db21; +ERROR: must be owner of database db_db21 +DROP DATABASE db_db21; +ERROR: must be owner of database db_db21 +RESET ROLE; +DROP ROLE rol_db21; +DROP DATABASE db_db21; +-- Should fail. CREATE / ALTER DATABASE should not have pg_global as TABLESPACE +CREATE DATABASE db_db22 TABLESPACE pg_global; +ERROR: pg_global cannot be used as default tablespace +CREATE DATABASE db_db22a; +ALTER DATABASE db_db22a SET TABLESPACE pg_global; +ERROR: pg_global cannot be used as default tablespace +DROP DATABASE db_db22a; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 2af28b1..243f377 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -88,7 +88,7 @@ test: privileges security_label collate matview # ---------- # Another group of parallel tests # ---------- -test: alter_generic misc psql +test: alter_generic misc psql database # rules cannot run concurrently with any test that creates a view test: rules diff --git a/src/test/regress/sql/database.sql b/src/test/regress/sql/database.sql new file mode 100644 index 0000000..e4d6481 --- /dev/null +++ b/src/test/regress/sql/database.sql @@ -0,0 +1,187 @@ +-- +-- tests functions in dbcommands.c script +-- + +-- Should work. Create Database +CREATE DATABASE db_db1; +DROP DATABASE db_db1; + +-- Should fail. Can't DROP DATABASE that doesn't exist / is reserved +DROP DATABASE db_db2; +DROP DATABASE IF EXISTS db_db2; +DROP DATABASE template1; +DROP DATABASE regression; + +-- Should fail. Can't DROP DATABASE inside a transaction +BEGIN TRANSACTION; +CREATE DATABASE db_db3; +DROP DATABASE db_db3; +ROLLBACK; + +-- Should fail. DROP DATABASE requires CREATEDB permissions +CREATE ROLE rol_db4; +CREATE DATABASE db_db4; +SET ROLE rol_db4; +DROP DATABASE db_db4; +RESET ROLE; +DROP DATABASE db_db4; +DROP ROLE rol_db4; + +-- Should work. ALTER DATABASE RENAME TO +CREATE DATABASE db_db5; +ALTER DATABASE db_db5 RENAME TO db_db5b; +DROP DATABASE db_db5b; + +-- Should fail. ALTER DATABASE RENAME TO on non-existent DB +CREATE DATABASE db_db6; +ALTER DATABASE asdf RENAME TO db_db6a; +ALTER DATABASE db_db6 RENAME TO db_db6; +ALTER DATABASE db_db6 RENAME TO db_db6a; +DROP DATABASE db_db6a; + +-- Should fail. ALTER DATABASE RENAME TO without permission +CREATE ROLE rol_db7; +CREATE DATABASE db_db7; +SET ROLE rol_db7; +ALTER DATABASE db_db7 RENAME TO db_db7a; +RESET ROLE; +DROP DATABASE db_db7; +DROP ROLE rol_db7; + +-- Should fail. ALTER DATABASE RENAME TO with OWNER rights without CREATEDB +CREATE ROLE rol_db8; +CREATE DATABASE db_db8 WITH OWNER rol_db8; +SET ROLE rol_db8; +ALTER DATABASE db_db8 RENAME TO db_db8a; +ALTER DATABASE db_db8 RENAME TO db_db8a; +RESET ROLE; +DROP DATABASE db_db8; +DROP ROLE rol_db8; + +-- Should work. ALTER DATABASE RENAME TO with OWNER and CREATEDB privilege +CREATE ROLE rol_db9 CREATEDB; +CREATE DATABASE db_db9 WITH OWNER rol_db9; +SET ROLE rol_db9; +ALTER DATABASE db_db9 RENAME TO regression; +ALTER DATABASE db_db9 RENAME TO db_db9a; +RESET ROLE; +DROP DATABASE db_db9a; +DROP ROLE rol_db9; + +-- Should fail. Can't rename current database +ALTER DATABASE regression RENAME TO regression_a; + +-- Should work. ALTER DATABASE SET with some valid / invalid values +CREATE DATABASE db_db10; +ALTER DATABASE db_db10 SET ASDF; +ALTER DATABASE db_db10 WITH CONNECTION LIMIT = -100; +ALTER DATABASE db_db10 WITH CONNECTION LIMIT = -1; +ALTER DATABASE db_db10 WITH CONNECTION LIMIT = 0; +ALTER DATABASE db_db10 WITH CONNECTION LIMIT = 100; +ALTER DATABASE db_db10 SET SEED = 0.5; +DROP DATABASE db_db10; + +-- Should fail. Can't set Tablespace within transaction +CREATE DATABASE db_db11; +BEGIN TRANSACTION; +ALTER DATABASE db_db11 SET TABLESPACE asdf; +ROLLBACK; +DROP DATABASE db_db11; + +-- Should fail. ALTER DATABASE with invalid connection limit +CREATE DATABASE db_db12; +BEGIN TRANSACTION; +ALTER DATABASE db_db12 WITH CONNECTION LIMIT = 8ASDF; +ROLLBACK; +DROP DATABASE db_db12; + +-- Should work. ALTER DATABASE SET with OWNER rights without CREATEDB +CREATE ROLE rol_db13; +CREATE DATABASE db_db13 WITH OWNER rol_db13; +SET ROLE rol_db13; +ALTER DATABASE db_db13 SET SEED=0.5; +RESET ROLE; +DROP DATABASE db_db13; +DROP ROLE rol_db13; + +-- Should work. ALTER DATABASE OWNER TO with OWNER rights +CREATE ROLE rol_db14; +CREATE ROLE rol_db15; +CREATE DATABASE db_db14 WITH OWNER rol_db14; + +SET ROLE rol_db15; +ALTER DATABASE db_db14 OWNER TO rol_db15; -- OWNERship required +RESET ROLE; + +SET ROLE rol_db14; +ALTER DATABASE db_db14 OWNER TO rol_db15; -- CREATEDB required for grantor +RESET ROLE; + +ALTER DATABASE asdf OWNER TO rol_db15; -- bogus database +ALTER DATABASE db_db14 OWNER TO asdf; -- bogus to owner +ALTER DATABASE db_db14 OWNER TO rol_db14; -- reassign to self +ALTER DATABASE db_db14 OWNER TO rol_db15; +DROP DATABASE db_db14; +DROP ROLE rol_db15; +DROP ROLE rol_db14; + +-- Should fail. ALTER DATABASE SET TABLESPACE with invalid name +ALTER DATABASE db_db16 SET TABLESPACE asdf; + +-- Should fail. ALTER DATABASE SET TABLESPACE without OWNER privilege +CREATE ROLE rol_db16; +CREATE DATABASE db_db16; +SET ROLE rol_db16; +ALTER DATABASE db_db16 SET TABLESPACE asdf; +RESET ROLE; +DROP DATABASE db_db16; +DROP ROLE rol_db16; + +-- Should fail. ALTER DATABASE SET TABLESPACE without OWNER privilege +ALTER DATABASE regression SET TABLESPACE asdf; + +-- Should fail. ALTER DATABASE SET TABLESPACE with OWNER / CREATEDB privilege +CREATE ROLE rol_db17 CREATEDB; +CREATE DATABASE db_db17 OWNER rol_db17; +SET ROLE rol_db17; +ALTER DATABASE db_db17 SET TABLESPACE DEFAULT; +RESET ROLE; +DROP DATABASE db_db17; +DROP ROLE rol_db17; + +-- Should work. CREATE DATABASE with valid values +CREATE DATABASE db_db18a CONNECTION LIMIT 10; +DROP DATABASE db_db18a; + +-- Should fail. CREATE DATABASE with invalid values +CREATE DATABASE db_db18 ENCODING 'invalid_encoding'; +CREATE DATABASE db_db18 ENCODING 123456789; +CREATE DATABASE db_db18 CONNECTION LIMIT -10; +CREATE DATABASE db_db18 TEMPLATE invalid_template; +CREATE DATABASE regression; + +-- Should fail. CREATE DATABASE using non-template as template without SUPERUSER +CREATE ROLE rol_db19 CREATEDB; +CREATE DATABASE db_db19; +SET ROLE rol_db19; +CREATE DATABASE db_db20 template db_db19; +RESET ROLE; +DROP DATABASE db_db19; +DROP ROLE rol_db19; + +-- Should fail. Self trying CREATE / ALTER / DROP DATABASE without rights +CREATE ROLE rol_db21; +CREATE DATABASE db_db21; +SET ROLE rol_db21; +CREATE DATABASE db_db21a; +ALTER DATABASE db_db21 OWNER TO rol_db21; +DROP DATABASE db_db21; +RESET ROLE; +DROP ROLE rol_db21; +DROP DATABASE db_db21; + +-- Should fail. CREATE / ALTER DATABASE should not have pg_global as TABLESPACE +CREATE DATABASE db_db22 TABLESPACE pg_global; +CREATE DATABASE db_db22a; +ALTER DATABASE db_db22a SET TABLESPACE pg_global; +DROP DATABASE db_db22a;