--
-- Tests for things affected by allow_system_table_mods
--
-- We run the same set of commands once with allow_system_table_mods
-- off and then again with on.
--
-- The "on" tests should where possible be wrapped in BEGIN/ROLLBACK
-- blocks so as to not leave a mess around.
CREATE USER regress_user_ast;
SET allow_system_table_mods = off;
-- create new table in pg_catalog
CREATE TABLE pg_catalog.test (a int);
ERROR:  permission denied to create "pg_catalog.test"
DETAIL:  System catalog modifications are currently disallowed.
-- anyarray column
CREATE TABLE t1x (a int, b anyarray);
ERROR:  column "b" has pseudo-type anyarray
-- index on system catalog
ALTER TABLE pg_namespace ADD CONSTRAINT foo UNIQUE USING INDEX pg_namespace_nspname_index;
ERROR:  permission denied: "pg_namespace" is a system catalog
-- write to system catalog table as superuser
-- (allowed even without allow_system_table_mods)
INSERT INTO pg_description (objoid, classoid, objsubid, description) VALUES (0, 0, 0, 'foo');
-- write to system catalog table as normal user
GRANT INSERT ON pg_description TO regress_user_ast;
SET ROLE regress_user_ast;
INSERT INTO pg_description (objoid, classoid, objsubid, description) VALUES (0, 0, 1, 'foo');
ERROR:  permission denied for table pg_description
RESET ROLE;
-- policy on system catalog
CREATE POLICY foo ON pg_description FOR SELECT USING (description NOT LIKE 'secret%');
ERROR:  permission denied: "pg_description" is a system catalog
-- reserved schema name
CREATE SCHEMA pg_foo;
ERROR:  unacceptable schema name "pg_foo"
DETAIL:  The prefix "pg_" is reserved for system schemas.
-- drop system table
DROP TABLE pg_description;
ERROR:  permission denied: "pg_description" is a system catalog
-- truncate of system table
TRUNCATE pg_description;
ERROR:  permission denied: "pg_description" is a system catalog
-- rename column of system table
ALTER TABLE pg_description RENAME COLUMN description TO comment;
ERROR:  permission denied: "pg_description" is a system catalog
-- ATSimplePermissions()
ALTER TABLE pg_description ALTER COLUMN description SET NOT NULL;
ERROR:  permission denied: "pg_description" is a system catalog
-- SET STATISTICS
ALTER TABLE pg_description ALTER COLUMN description SET STATISTICS -1;
ERROR:  permission denied: "pg_description" is a system catalog
-- foreign key referencing catalog
CREATE TABLE foo (a oid, b oid, c int, FOREIGN KEY (a, b, c) REFERENCES pg_description);
ERROR:  permission denied: "pg_description" is a system catalog
-- RangeVarCallbackOwnsRelation()
CREATE INDEX pg_description_test_index ON pg_description (description);
ERROR:  permission denied: "pg_description" is a system catalog
-- RangeVarCallbackForAlterRelation()
ALTER TABLE pg_description RENAME TO pg_comment;
ERROR:  permission denied: "pg_description" is a system catalog
ALTER TABLE pg_description SET SCHEMA public;
ERROR:  permission denied: "pg_description" is a system catalog
-- reserved tablespace name
CREATE TABLESPACE pg_foo LOCATION '/no/such/location';
ERROR:  unacceptable tablespace name "pg_foo"
DETAIL:  The prefix "pg_" is reserved for system tablespaces.
-- triggers
CREATE FUNCTION tf1() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN NULL;
END $$;
CREATE TRIGGER t1 BEFORE INSERT ON pg_description EXECUTE FUNCTION tf1();
ERROR:  permission denied: "pg_description" is a system catalog
ALTER TRIGGER t1 ON pg_description RENAME TO t2;
ERROR:  permission denied: "pg_description" is a system catalog
--DROP TRIGGER t2 ON pg_description;
-- rules
CREATE RULE r1 AS ON INSERT TO pg_description DO INSTEAD NOTHING;
ERROR:  permission denied: "pg_description" is a system catalog
ALTER RULE r1 ON pg_description RENAME TO r2;
ERROR:  permission denied: "pg_description" is a system catalog
-- now make one to test dropping:
SET allow_system_table_mods TO on;
CREATE RULE r2 AS ON INSERT TO pg_description DO INSTEAD NOTHING;
RESET allow_system_table_mods;
DROP RULE r2 ON pg_description;
ERROR:  permission denied: "pg_description" is a system catalog
-- cleanup:
SET allow_system_table_mods TO on;
DROP RULE r2 ON pg_description;
RESET allow_system_table_mods;
-- Reloptions on TOAST tables
ALTER TABLE pg_toast.pg_toast_2615 SET (fillfactor = '90');
ERROR:  permission denied: "pg_toast_2615" is a system catalog
SET allow_system_table_mods = on;
-- create new table in pg_catalog
BEGIN;
CREATE TABLE pg_catalog.test (a int);
ROLLBACK;
-- anyarray column
BEGIN;
CREATE TABLE t1 (a int, b anyarray);
ROLLBACK;
-- index on system catalog
BEGIN;
ALTER TABLE pg_namespace ADD CONSTRAINT foo UNIQUE USING INDEX pg_namespace_nspname_index;
NOTICE:  ALTER TABLE / ADD CONSTRAINT USING INDEX will rename index "pg_namespace_nspname_index" to "foo"
ROLLBACK;
-- write to system catalog table as superuser
BEGIN;
INSERT INTO pg_description (objoid, classoid, objsubid, description) VALUES (0, 0, 2, 'foo');
ROLLBACK;
-- write to system catalog table as normal user
-- (not allowed)
SET ROLE regress_user_ast;
INSERT INTO pg_description (objoid, classoid, objsubid, description) VALUES (0, 0, 3, 'foo');
ERROR:  permission denied for table pg_description
RESET ROLE;
-- policy on system catalog
BEGIN;
CREATE POLICY foo ON pg_description FOR SELECT USING (description NOT LIKE 'secret%');
ROLLBACK;
-- reserved schema name
BEGIN;
CREATE SCHEMA pg_foo;
ROLLBACK;
-- drop system table
-- (This will fail anyway because it's pinned.)
BEGIN;
DROP TABLE pg_description;
ERROR:  cannot drop table pg_description because it is required by the database system
ROLLBACK;
-- truncate of system table
BEGIN;
TRUNCATE pg_description;
ROLLBACK;
-- rename column of system table
BEGIN;
ALTER TABLE pg_description RENAME COLUMN description TO comment;
ROLLBACK;
-- ATSimplePermissions()
BEGIN;
ALTER TABLE pg_description ALTER COLUMN description SET NOT NULL;
ROLLBACK;
-- SET STATISTICS
BEGIN;
ALTER TABLE pg_description ALTER COLUMN description SET STATISTICS -1;
ROLLBACK;
-- foreign key referencing catalog
BEGIN;
CREATE TABLE foo (a oid, b oid, c int, FOREIGN KEY (a, b, c) REFERENCES pg_description);
ROLLBACK;
-- RangeVarCallbackOwnsRelation()
BEGIN;
CREATE INDEX pg_description_test_index ON pg_description (description);
ROLLBACK;
-- RangeVarCallbackForAlterRelation()
BEGIN;
ALTER TABLE pg_description RENAME TO pg_comment;
ROLLBACK;
BEGIN;
ALTER TABLE pg_description SET SCHEMA public;
ROLLBACK;
-- reserved tablespace name
SET client_min_messages = error;  -- disable ENFORCE_REGRESSION_TEST_NAME_RESTRICTIONS warning
CREATE TABLESPACE pg_foo LOCATION '/no/such/location';
ERROR:  directory "/no/such/location" does not exist
RESET client_min_messages;
-- triggers
CREATE TRIGGER t1 BEFORE INSERT ON pg_description EXECUTE FUNCTION tf1();
ALTER TRIGGER t1 ON pg_description RENAME TO t2;
DROP TRIGGER t2 ON pg_description;
-- rules
CREATE RULE r1 AS ON INSERT TO pg_description DO INSTEAD NOTHING;
ALTER RULE r1 ON pg_description RENAME TO r2;
DROP RULE r2 ON pg_description;
-- Reloptions on TOAST tables
ALTER TABLE pg_toast.pg_toast_2615 SET (fillfactor = '90');
ERROR:  ALTER action SET cannot be performed on relation "pg_toast_2615"
DETAIL:  This operation is not supported for TOAST tables.
-- cleanup
REVOKE ALL ON pg_description FROM regress_user_ast;
DROP USER regress_user_ast;
DROP FUNCTION tf1;