diff options
Diffstat (limited to 'src/test/regress/expected')
| -rw-r--r-- | src/test/regress/expected/foreign_key.out | 51 | ||||
| -rw-r--r-- | src/test/regress/expected/rowsecurity.out | 259 |
2 files changed, 310 insertions, 0 deletions
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out index dc541d61adf..7f9e0ebb82d 100644 --- a/src/test/regress/expected/foreign_key.out +++ b/src/test/regress/expected/foreign_key.out @@ -3406,3 +3406,54 @@ SET client_min_messages TO warning; DROP SCHEMA fkpart12 CASCADE; RESET client_min_messages; RESET search_path; +-- Exercise the column mapping code with foreign keys. In this test we'll +-- create a partitioned table which has a partition with a dropped column and +-- check to ensure that an UPDATE cascades the changes correctly to the +-- partitioned table. +CREATE SCHEMA fkpart13; +SET search_path TO fkpart13; +CREATE TABLE fkpart13_t1 (a int PRIMARY KEY); +CREATE TABLE fkpart13_t2 ( + part_id int PRIMARY KEY, + column_to_drop int, + FOREIGN KEY (part_id) REFERENCES fkpart13_t1 ON UPDATE CASCADE ON DELETE CASCADE +) PARTITION BY LIST (part_id); +CREATE TABLE fkpart13_t2_p1 PARTITION OF fkpart13_t2 FOR VALUES IN (1); +-- drop the column +ALTER TABLE fkpart13_t2 DROP COLUMN column_to_drop; +-- create a new partition without the dropped column +CREATE TABLE fkpart13_t2_p2 PARTITION OF fkpart13_t2 FOR VALUES IN (2); +CREATE TABLE fkpart13_t3 ( + a int NOT NULL, + FOREIGN KEY (a) + REFERENCES fkpart13_t2 + ON UPDATE CASCADE ON DELETE CASCADE +); +INSERT INTO fkpart13_t1 (a) VALUES (1); +INSERT INTO fkpart13_t2 (part_id) VALUES (1); +INSERT INTO fkpart13_t3 (a) VALUES (1); +-- Test a cascading update works correctly with with the dropped column +UPDATE fkpart13_t1 SET a = 2 WHERE a = 1; +SELECT tableoid::regclass,* FROM fkpart13_t2; + tableoid | part_id +----------------+--------- + fkpart13_t2_p2 | 2 +(1 row) + +SELECT tableoid::regclass,* FROM fkpart13_t3; + tableoid | a +-------------+--- + fkpart13_t3 | 2 +(1 row) + +-- Exercise code in ExecGetTriggerResultRel() as there's been previous issues +-- with ResultRelInfos being returned with the incorrect ri_RootResultRelInfo +WITH cte AS ( + UPDATE fkpart13_t2_p1 SET part_id = part_id +) UPDATE fkpart13_t1 SET a = 2 WHERE a = 1; +DROP SCHEMA fkpart13 CASCADE; +NOTICE: drop cascades to 3 other objects +DETAIL: drop cascades to table fkpart13_t1 +drop cascades to table fkpart13_t2 +drop cascades to table fkpart13_t3 +RESET search_path; diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out index 42b78a24603..c958ef4d70a 100644 --- a/src/test/regress/expected/rowsecurity.out +++ b/src/test/regress/expected/rowsecurity.out @@ -31,6 +31,265 @@ CREATE OR REPLACE FUNCTION f_leak(text) RETURNS bool COST 0.0000001 LANGUAGE plpgsql AS 'BEGIN RAISE NOTICE ''f_leak => %'', $1; RETURN true; END'; GRANT EXECUTE ON FUNCTION f_leak(text) TO public; +-- +-- Test policies applied by command type +-- +SET SESSION AUTHORIZATION regress_rls_alice; +-- setup source table (for MERGE operations) +CREATE TABLE rls_test_src (a int PRIMARY KEY, b text); +ALTER TABLE rls_test_src ENABLE ROW LEVEL SECURITY; +GRANT SELECT, UPDATE ON rls_test_src TO public; +INSERT INTO rls_test_src VALUES (1, 'src a'); +-- setup target table with a column set by a BEFORE ROW trigger +-- (policies should always see values set by the trigger) +CREATE TABLE rls_test_tgt (a int PRIMARY KEY, b text, c text); +ALTER TABLE rls_test_tgt ENABLE ROW LEVEL SECURITY; +GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON rls_test_tgt TO public; +CREATE FUNCTION rls_test_tgt_set_c() RETURNS trigger AS + $$ BEGIN new.c = upper(new.b); RETURN new; END; $$ + LANGUAGE plpgsql; +CREATE TRIGGER rls_test_tgt_set_c BEFORE INSERT OR UPDATE ON rls_test_tgt + FOR EACH ROW EXECUTE FUNCTION rls_test_tgt_set_c(); +-- setup a complete set of policies that emit NOTICE messages when applied +CREATE FUNCTION rls_test_policy_fn(text, record) RETURNS bool AS + $$ BEGIN RAISE NOTICE '%.%', $1, $2; RETURN true; END; $$ + LANGUAGE plpgsql; +CREATE POLICY sel_pol ON rls_test_src FOR SELECT + USING (rls_test_policy_fn('SELECT USING on rls_test_src', rls_test_src)); +CREATE POLICY upd_pol ON rls_test_src FOR UPDATE + USING (rls_test_policy_fn('UPDATE USING on rls_test_src', rls_test_src)) + WITH CHECK (rls_test_policy_fn('UPDATE CHECK on rls_test_src', rls_test_src)); +CREATE POLICY sel_pol ON rls_test_tgt FOR SELECT + USING (rls_test_policy_fn('SELECT USING on rls_test_tgt', rls_test_tgt)); +CREATE POLICY ins_pol ON rls_test_tgt FOR INSERT + WITH CHECK (rls_test_policy_fn('INSERT CHECK on rls_test_tgt', rls_test_tgt)); +CREATE POLICY upd_pol ON rls_test_tgt FOR UPDATE + USING (rls_test_policy_fn('UPDATE USING on rls_test_tgt', rls_test_tgt)) + WITH CHECK (rls_test_policy_fn('UPDATE CHECK on rls_test_tgt', rls_test_tgt)); +CREATE POLICY del_pol ON rls_test_tgt FOR DELETE + USING (rls_test_policy_fn('DELETE USING on rls_test_tgt', rls_test_tgt)); +-- test policies applied to regress_rls_bob +SET SESSION AUTHORIZATION regress_rls_bob; +-- SELECT, COPY ... TO, and TABLE should only apply SELECT USING policy clause +SELECT * FROM rls_test_src; +NOTICE: SELECT USING on rls_test_src.(1,"src a") + a | b +---+------- + 1 | src a +(1 row) + +COPY rls_test_src TO stdout; +NOTICE: SELECT USING on rls_test_src.(1,"src a") +1 src a +TABLE rls_test_src; +NOTICE: SELECT USING on rls_test_src.(1,"src a") + a | b +---+------- + 1 | src a +(1 row) + +-- SELECT ... FOR UPDATE/SHARE should also apply UPDATE USING policy clause +SELECT * FROM rls_test_src FOR UPDATE; +NOTICE: UPDATE USING on rls_test_src.(1,"src a") +NOTICE: SELECT USING on rls_test_src.(1,"src a") + a | b +---+------- + 1 | src a +(1 row) + +SELECT * FROM rls_test_src FOR NO KEY UPDATE; +NOTICE: UPDATE USING on rls_test_src.(1,"src a") +NOTICE: SELECT USING on rls_test_src.(1,"src a") + a | b +---+------- + 1 | src a +(1 row) + +SELECT * FROM rls_test_src FOR SHARE; +NOTICE: UPDATE USING on rls_test_src.(1,"src a") +NOTICE: SELECT USING on rls_test_src.(1,"src a") + a | b +---+------- + 1 | src a +(1 row) + +SELECT * FROM rls_test_src FOR KEY SHARE; +NOTICE: UPDATE USING on rls_test_src.(1,"src a") +NOTICE: SELECT USING on rls_test_src.(1,"src a") + a | b +---+------- + 1 | src a +(1 row) + +-- plain INSERT should apply INSERT CHECK policy clause +INSERT INTO rls_test_tgt VALUES (1, 'tgt a'); +NOTICE: INSERT CHECK on rls_test_tgt.(1,"tgt a","TGT A") +-- INSERT ... RETURNING should also apply SELECT USING policy clause +TRUNCATE rls_test_tgt; +INSERT INTO rls_test_tgt VALUES (1, 'tgt a') RETURNING *; +NOTICE: INSERT CHECK on rls_test_tgt.(1,"tgt a","TGT A") +NOTICE: SELECT USING on rls_test_tgt.(1,"tgt a","TGT A") + a | b | c +---+-------+------- + 1 | tgt a | TGT A +(1 row) + +-- UPDATE without WHERE or RETURNING should only apply UPDATE policy clauses +UPDATE rls_test_tgt SET b = 'tgt b'; +NOTICE: UPDATE USING on rls_test_tgt.(1,"tgt a","TGT A") +NOTICE: UPDATE CHECK on rls_test_tgt.(1,"tgt b","TGT B") +-- UPDATE with WHERE or RETURNING should also apply SELECT USING policy clause +-- (to both old and new values) +UPDATE rls_test_tgt SET b = 'tgt c' WHERE a = 1; +NOTICE: UPDATE USING on rls_test_tgt.(1,"tgt b","TGT B") +NOTICE: SELECT USING on rls_test_tgt.(1,"tgt b","TGT B") +NOTICE: UPDATE CHECK on rls_test_tgt.(1,"tgt c","TGT C") +NOTICE: SELECT USING on rls_test_tgt.(1,"tgt c","TGT C") +UPDATE rls_test_tgt SET b = 'tgt d' RETURNING *; +NOTICE: UPDATE USING on rls_test_tgt.(1,"tgt c","TGT C") +NOTICE: SELECT USING on rls_test_tgt.(1,"tgt c","TGT C") +NOTICE: UPDATE CHECK on rls_test_tgt.(1,"tgt d","TGT D") +NOTICE: SELECT USING on rls_test_tgt.(1,"tgt d","TGT D") + a | b | c +---+-------+------- + 1 | tgt d | TGT D +(1 row) + +-- DELETE without WHERE or RETURNING should only apply DELETE USING policy clause +BEGIN; DELETE FROM rls_test_tgt; ROLLBACK; +NOTICE: DELETE USING on rls_test_tgt.(1,"tgt d","TGT D") +-- DELETE with WHERE or RETURNING should also apply SELECT USING policy clause +BEGIN; DELETE FROM rls_test_tgt WHERE a = 1; ROLLBACK; +NOTICE: DELETE USING on rls_test_tgt.(1,"tgt d","TGT D") +NOTICE: SELECT USING on rls_test_tgt.(1,"tgt d","TGT D") +DELETE FROM rls_test_tgt RETURNING *; +NOTICE: DELETE USING on rls_test_tgt.(1,"tgt d","TGT D") +NOTICE: SELECT USING on rls_test_tgt.(1,"tgt d","TGT D") + a | b | c +---+-------+------- + 1 | tgt d | TGT D +(1 row) + +-- INSERT ... ON CONFLICT DO NOTHING should apply INSERT CHECK and SELECT USING +-- policy clauses (to new value, whether it conflicts or not) +INSERT INTO rls_test_tgt VALUES (1, 'tgt a') ON CONFLICT (a) DO NOTHING; +NOTICE: INSERT CHECK on rls_test_tgt.(1,"tgt a","TGT A") +NOTICE: SELECT USING on rls_test_tgt.(1,"tgt a","TGT A") +INSERT INTO rls_test_tgt VALUES (1, 'tgt b') ON CONFLICT (a) DO NOTHING; +NOTICE: INSERT CHECK on rls_test_tgt.(1,"tgt b","TGT B") +NOTICE: SELECT USING on rls_test_tgt.(1,"tgt b","TGT B") +-- INSERT ... ON CONFLICT DO NOTHING without an arbiter clause only applies +-- INSERT CHECK policy clause +INSERT INTO rls_test_tgt VALUES (1, 'tgt b') ON CONFLICT DO NOTHING; +NOTICE: INSERT CHECK on rls_test_tgt.(1,"tgt b","TGT B") +-- INSERT ... ON CONFLICT DO UPDATE should apply INSERT CHECK and SELECT USING +-- policy clauses to values proposed for insert. In the event of a conflict it +-- should also apply UPDATE and SELECT policies to old and new values, like +-- UPDATE ... WHERE. +BEGIN; +INSERT INTO rls_test_tgt VALUES (2, 'tgt a') ON CONFLICT (a) DO UPDATE SET b = 'tgt b'; +NOTICE: INSERT CHECK on rls_test_tgt.(2,"tgt a","TGT A") +NOTICE: SELECT USING on rls_test_tgt.(2,"tgt a","TGT A") +INSERT INTO rls_test_tgt VALUES (2, 'tgt c') ON CONFLICT (a) DO UPDATE SET b = 'tgt d'; +NOTICE: INSERT CHECK on rls_test_tgt.(2,"tgt c","TGT C") +NOTICE: SELECT USING on rls_test_tgt.(2,"tgt c","TGT C") +NOTICE: UPDATE USING on rls_test_tgt.(2,"tgt a","TGT A") +NOTICE: SELECT USING on rls_test_tgt.(2,"tgt a","TGT A") +NOTICE: UPDATE CHECK on rls_test_tgt.(2,"tgt d","TGT D") +NOTICE: SELECT USING on rls_test_tgt.(2,"tgt d","TGT D") +INSERT INTO rls_test_tgt VALUES (3, 'tgt a') ON CONFLICT (a) DO UPDATE SET b = 'tgt b' RETURNING *; +NOTICE: INSERT CHECK on rls_test_tgt.(3,"tgt a","TGT A") +NOTICE: SELECT USING on rls_test_tgt.(3,"tgt a","TGT A") + a | b | c +---+-------+------- + 3 | tgt a | TGT A +(1 row) + +INSERT INTO rls_test_tgt VALUES (3, 'tgt c') ON CONFLICT (a) DO UPDATE SET b = 'tgt d' RETURNING *; +NOTICE: INSERT CHECK on rls_test_tgt.(3,"tgt c","TGT C") +NOTICE: SELECT USING on rls_test_tgt.(3,"tgt c","TGT C") +NOTICE: UPDATE USING on rls_test_tgt.(3,"tgt a","TGT A") +NOTICE: SELECT USING on rls_test_tgt.(3,"tgt a","TGT A") +NOTICE: UPDATE CHECK on rls_test_tgt.(3,"tgt d","TGT D") +NOTICE: SELECT USING on rls_test_tgt.(3,"tgt d","TGT D") + a | b | c +---+-------+------- + 3 | tgt d | TGT D +(1 row) + +ROLLBACK; +-- MERGE should always apply SELECT USING policy clauses to both source and +-- target rows +MERGE INTO rls_test_tgt t USING rls_test_src s ON t.a = s.a + WHEN NOT MATCHED THEN DO NOTHING; +NOTICE: SELECT USING on rls_test_tgt.(1,"tgt a","TGT A") +NOTICE: SELECT USING on rls_test_src.(1,"src a") +-- MERGE ... INSERT should behave like INSERT on target table +-- (SELECT policy applied to target, if RETURNING is specified) +TRUNCATE rls_test_tgt; +MERGE INTO rls_test_tgt t USING rls_test_src s ON t.a = s.a + WHEN NOT MATCHED THEN INSERT VALUES (1, 'tgt a'); +NOTICE: SELECT USING on rls_test_src.(1,"src a") +NOTICE: INSERT CHECK on rls_test_tgt.(1,"tgt a","TGT A") +TRUNCATE rls_test_tgt; +MERGE INTO rls_test_tgt t USING rls_test_src s ON t.a = s.a + WHEN NOT MATCHED THEN INSERT VALUES (1, 'tgt a') + RETURNING *; +NOTICE: SELECT USING on rls_test_src.(1,"src a") +NOTICE: INSERT CHECK on rls_test_tgt.(1,"tgt a","TGT A") +NOTICE: SELECT USING on rls_test_tgt.(1,"tgt a","TGT A") + a | b | a | b | c +---+-------+---+-------+------- + 1 | src a | 1 | tgt a | TGT A +(1 row) + +-- MERGE ... UPDATE should behave like UPDATE ... WHERE on target table +-- (join clause is like WHERE, so SELECT policies are always applied) +MERGE INTO rls_test_tgt t USING rls_test_src s ON t.a = s.a + WHEN MATCHED THEN UPDATE SET b = 'tgt b'; +NOTICE: SELECT USING on rls_test_tgt.(1,"tgt a","TGT A") +NOTICE: SELECT USING on rls_test_src.(1,"src a") +NOTICE: UPDATE USING on rls_test_tgt.(1,"tgt a","TGT A") +NOTICE: UPDATE CHECK on rls_test_tgt.(1,"tgt b","TGT B") +NOTICE: SELECT USING on rls_test_tgt.(1,"tgt b","TGT B") +MERGE INTO rls_test_tgt t USING rls_test_src s ON t.a = s.a + WHEN MATCHED THEN UPDATE SET b = 'tgt c' + RETURNING *; +NOTICE: SELECT USING on rls_test_tgt.(1,"tgt b","TGT B") +NOTICE: SELECT USING on rls_test_src.(1,"src a") +NOTICE: UPDATE USING on rls_test_tgt.(1,"tgt b","TGT B") +NOTICE: UPDATE CHECK on rls_test_tgt.(1,"tgt c","TGT C") +NOTICE: SELECT USING on rls_test_tgt.(1,"tgt c","TGT C") + a | b | a | b | c +---+-------+---+-------+------- + 1 | src a | 1 | tgt c | TGT C +(1 row) + +-- MERGE ... DELETE should behave like DELETE ... WHERE on target table +-- (join clause is like WHERE, so SELECT policies are always applied) +BEGIN; +MERGE INTO rls_test_tgt t USING rls_test_src s ON t.a = s.a + WHEN MATCHED THEN DELETE; +NOTICE: SELECT USING on rls_test_tgt.(1,"tgt c","TGT C") +NOTICE: SELECT USING on rls_test_src.(1,"src a") +NOTICE: DELETE USING on rls_test_tgt.(1,"tgt c","TGT C") +ROLLBACK; +MERGE INTO rls_test_tgt t USING rls_test_src s ON t.a = s.a + WHEN MATCHED THEN DELETE + RETURNING *; +NOTICE: SELECT USING on rls_test_tgt.(1,"tgt c","TGT C") +NOTICE: SELECT USING on rls_test_src.(1,"src a") +NOTICE: DELETE USING on rls_test_tgt.(1,"tgt c","TGT C") + a | b | a | b | c +---+-------+---+-------+------- + 1 | src a | 1 | tgt c | TGT C +(1 row) + +-- Tidy up +RESET SESSION AUTHORIZATION; +DROP TABLE rls_test_src, rls_test_tgt; +DROP FUNCTION rls_test_tgt_set_c; +DROP FUNCTION rls_test_policy_fn; -- BASIC Row-Level Security Scenario SET SESSION AUTHORIZATION regress_rls_alice; CREATE TABLE uaccount ( |