File size: 5,708 Bytes
f689c7d
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
import unittest

import check
from test_check import BASE, history


class SqlPolicyTest(unittest.TestCase):
    def plan(self, sql):
        return check.plan({**BASE, "V43__schema_probe.sql": sql}, BASE, history())

    def test_supported_qualified_targets(self):
        for sql in [
            "CREATE TABLE public.example(id bigint PRIMARY KEY);",
            "CREATE INDEX idx_example ON public.example(id);",
            "CREATE UNIQUE INDEX idx_example ON public.example(lower(name)) WHERE name IS NOT NULL;",
            "ALTER TABLE public.example ADD COLUMN title text;",
            "CREATE TYPE public.state AS ENUM ('NEW','DONE');",
            "ALTER TYPE public.state ADD VALUE 'ARCHIVED';",
            "CREATE SEQUENCE public.example_seq;",
            "ALTER SEQUENCE public.example_seq OWNED BY public.example.id;",
            "CREATE VIEW public.example_view AS SELECT id FROM public.example;",
            "CREATE TABLE public.example_copy AS SELECT * FROM public.example;",
            "COMMENT ON COLUMN public.example.title IS 'public. is not a target';",
            "DROP INDEX public.idx_example;",
            "UPDATE public.example SET title=upper(title);",
            "WITH sample AS (SELECT id FROM public.example) SELECT * FROM sample;",
            'CREATE TABLE "public"."Mixed Case"(id bigint);',
            "CREATE TABLE public.example(id bigint GENERATED ALWAYS AS IDENTITY (SEQUENCE NAME public.example_seq));",
            "SET LOCAL lock_timeout='5s'; CREATE TABLE public.example(id bigint);",
        ]:
            with self.subTest(sql=sql):
                self.assertEqual(self.plan(sql)["new_local_versions"], [43])

    def test_unqualified_and_wrong_schema_targets_are_rejected(self):
        for sql in [
            "CREATE TABLE example(id bigint);",
            "CREATE TABLE taskpilot_migrations.example(id bigint);",
            "CREATE INDEX public_in_name ON example(id);",
            "ALTER TABLE example ADD COLUMN title text;",
            "CREATE TYPE state AS ENUM ('NEW');",
            "CREATE SEQUENCE example_seq;",
            "CREATE VIEW example_view AS SELECT id FROM public.example;",
            "CREATE TABLE example_copy AS SELECT * FROM public.example;",
            "DROP TABLE example;",
            "ALTER SEQUENCE public.example_seq OWNED BY example.id;",
            "CREATE TABLE public.child(parent_id bigint REFERENCES parent(id));",
            "CREATE TABLE public.example(id bigint GENERATED ALWAYS AS IDENTITY (SEQUENCE NAME taskpilot_migrations.hidden_seq));",
            "WITH sample AS (SELECT 1) DELETE FROM sample;",
            'CREATE TABLE "public.example"(id bigint);',
            "-- CREATE TABLE public.safe(id int);\nCREATE TABLE unsafe(id int);",
            "CREATE TABLE unsafe(title text DEFAULT 'public.example');",
        ]:
            with self.subTest(sql=sql), self.assertRaisesRegex(check.CheckError, "MIGRATION_SQL_POLICY"):
                self.plan(sql)

    def test_unanalysable_or_session_redirecting_sql_fails_closed(self):
        for sql in [
            "SET search_path=public; CREATE TABLE example(id bigint);",
            "SELECT set_config('search_path','public',false);",
            "DO $$ BEGIN EXECUTE 'CREATE TABLE hidden(id bigint)'; END $$;",
            "CREATE FUNCTION public.hidden() RETURNS void LANGUAGE plpgsql AS $$ BEGIN EXECUTE 'CREATE TABLE hidden(id int)'; END $$;",
            "CALL public.hidden();",
            "SELECT public.hidden();",
            "CREATE EXTENSION hstore;",
            "ALTER TABLE public.example SET SCHEMA taskpilot_migrations;",
            "BEGIN; CREATE TABLE public.example(id bigint); COMMIT;",
            "COPY public.example FROM PROGRAM 'anything';",
            "CREATE TABL public.broken(id int);",
        ]:
            with self.subTest(sql=sql), self.assertRaisesRegex(check.CheckError, "MIGRATION_SQL_POLICY"):
                self.plan(sql)

    def test_accepted_history_is_not_rewritten_or_retroactively_linted(self):
        self.assertEqual(check.plan(BASE, BASE, history())["local_version"], 42)

    def test_cte_names_do_not_escape_their_query_scope(self):
        for sql in [
            "CREATE VIEW public.example_view AS WITH outer_cte AS (WITH example AS (SELECT 1) SELECT 1) SELECT * FROM example;",
            "WITH outer_cte AS (WITH example AS (SELECT 1) SELECT 1) SELECT * FROM example;",
            "WITH first_cte AS (SELECT * FROM later_cte), later_cte AS (SELECT 1) SELECT * FROM first_cte;",
        ]:
            with self.subTest(sql=sql), self.assertRaisesRegex(check.CheckError, "MIGRATION_SQL_POLICY"):
                self.plan(sql)
        for sql in [
            "WITH first_cte AS (SELECT id FROM public.example), second_cte AS (SELECT * FROM first_cte) SELECT * FROM second_cte;",
            "WITH RECURSIVE counter(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM counter WHERE n<3) SELECT * FROM counter;",
            "SELECT * FROM (WITH sample AS (SELECT id FROM public.example) SELECT * FROM sample) nested_query;",
        ]:
            with self.subTest(sql=sql):
                self.assertEqual(self.plan(sql)["new_local_versions"], [43])

    def test_nested_unsupported_statements_are_not_an_execution_bypass(self):
        for sql in [
            "CREATE TABLE public.example_copy AS EXECUTE previous_query;",
            "WITH changed AS (MERGE INTO public.example t USING public.other s ON t.id=s.id WHEN MATCHED THEN DELETE RETURNING t.id) SELECT * FROM changed;",
        ]:
            with self.subTest(sql=sql), self.assertRaisesRegex(check.CheckError, "MIGRATION_SQL_POLICY"):
                self.plan(sql)


if __name__ == "__main__":
    unittest.main()