Repository navigation
Expand file tree
/
Copy pathqueries.sql
More file actions
139 lines (106 loc) · 3.19 KB
/
Copy pathqueries.sql
File metadata and controls
139 lines (106 loc) · 3.19 KB
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
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
-- Tutorial 2: Query Before Mutation
-- Validated end to end against Google Cloud Storage.
--
-- Pattern:
-- query live state
-- identify delta
-- policy gate
-- mutate
-- verify convergence
-- ============================================================
-- SETUP
-- ============================================================
REGISTRY PULL google v26.07.00432;
-- ============================================================
-- STEP 1: QUERY LIVE STATE
-- ============================================================
-- Replace your-project-id with the Google Cloud project used for the demo.
SELECT
name,
location,
encryption
FROM google.storage.buckets
WHERE project = 'your-project-id';
-- Buckets where encryption is NULL use Google-managed encryption.
-- For this tutorial, the desired policy is CMEK.
SELECT
name,
location,
encryption
FROM google.storage.buckets
WHERE project = 'your-project-id'
AND encryption IS NULL;
-- ============================================================
-- STEP 2: POLICY GATE
-- ============================================================
-- Gate 1:
-- Only operate on buckets in the expected location.
SELECT
name,
location,
encryption
FROM google.storage.buckets
WHERE project = 'your-project-id'
AND location = 'US'
AND encryption IS NULL;
-- Gate 2:
-- Count the number of resources that would be mutated.
--
-- The automation should stop if this number is higher than
-- the configured safety threshold.
SELECT COUNT(*)
FROM google.storage.buckets
WHERE project = 'your-project-id'
AND location = 'US'
AND encryption IS NULL;
-- ============================================================
-- STEP 3: MUTATE TO CONVERGE
-- ============================================================
-- Apply a customer-managed Cloud KMS key to one non-compliant bucket.
--
-- Replace:
-- demo-app-bucket1
-- your-project-id
-- your-ring
-- your-key
--
-- with values from your environment.
UPDATE google.storage.buckets
SET data__encryption =
'{"defaultKmsKeyName":"projects/your-project-id/locations/us/keyRings/your-ring/cryptoKeys/your-key"}'
WHERE bucket = 'demo-app-bucket1';
-- Expected result:
-- The operation was despatched successfully
-- ============================================================
-- STEP 4: VERIFY CONVERGENCE
-- ============================================================
SELECT
name,
encryption
FROM google.storage.buckets
WHERE bucket = 'demo-app-bucket1';
-- Expected state:
--
-- encryption should now contain a defaultKmsKeyName similar to:
--
-- {
-- "defaultKmsKeyName":
-- "projects/your-project-id/locations/us/keyRings/your-ring/cryptoKeys/your-key"
-- }
-- ============================================================
-- STEP 5: IDEMPOTENCE CHECK
-- ============================================================
SELECT
name,
location,
encryption
FROM google.storage.buckets
WHERE project = 'your-project-id'
AND location = 'US'
AND encryption IS NULL;
-- Once the target bucket has CMEK configured, it should no longer
-- appear in this result set.
--
-- If no non-compliant target remains, no UPDATE should run.
-- Every execution starts from live state and mutates only when
-- the current state differs from policy.