/
repo.sp
273 lines (264 loc) · 9.54 KB
/
repo.sp
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
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
query "public_repo_has_security_md_file" {
sql = <<-EOQ
select
-- Required Columns
r.url as resource,
case
when p.security is null then 'alarm'
else 'ok'
end as status,
r.name_with_owner || case when(p.security is not null) then ' with ' else ' without ' end || 'SECURITY.md file.' as reason,
-- Additional Dimensions
r.name_with_owner
from
github_my_repository as r
left join github_community_profile as p on r.name_with_owner = p.repository_full_name
where
visibility = 'PUBLIC' and r.is_fork = false;
EOQ
}
query "repo_delete_branch_on_merge_enabled" {
sql = <<-EOQ
select
-- Required Columns
url as resource,
case
when delete_branch_on_merge then 'ok'
else 'alarm'
end as status,
name_with_owner || ' delete branch on merge is ' || case when(delete_branch_on_merge)::bool then 'enabled' else 'disabled' end || '.' as reason,
-- Additional Dimensions
name_with_owner
from
github_my_repository;
EOQ
}
query "repo_deletion_limited_to_trusted_users" {
sql = <<-EOQ
with repo_admins as (
select distinct
name_with_owner,
array_agg(user_login) as admins
from
github_my_repository r
join
github_repository_collaborator c
on
r.name_with_owner = c.repository_full_name
and
c.permission = 'ADMIN'
group by
name_with_owner
)
select
-- Required Columns
r.url as resource,
case
when jsonb_array_length(to_jsonb(admins) - $1::text[]) > 0 then 'alarm'
else 'ok'
end as status,
case
when jsonb_array_length(to_jsonb(admins) - $1::text[]) > 2
then concat( 'Repository deletion permission allowed to untrusted users ', to_jsonb(admins) - $1::text[] #>> '{0}', ', ', to_jsonb(admins) - $1::text[] #>> '{1}', ' and ', (jsonb_array_length(to_jsonb(admins) - $1::text[]) - 2)::text, ' more.')
when jsonb_array_length(to_jsonb(admins) - $1::text[]) = 2
then concat('Repository deletion permission allowed to untrusted users ', to_jsonb(admins) - $1::text[] #>> '{0}', ' and ', to_jsonb(admins) - $1::text[] #>> '{1}', '.')
when jsonb_array_length(to_jsonb(admins) - $1::text[]) = 1
then concat('Repository deletion permission allowed to untrusted user ', to_jsonb(admins) - $1::text[] #>> '{0}', '.')
else 'Repository deletion permission limited to trusted users.'
end as reason,
-- Additional Dimensions
r.name_with_owner
from
github_my_repository as r
left join repo_admins as a on r.name_with_owner = a.name_with_owner;
EOQ
}
query "repo_inactive_more_than_90_days" {
sql = <<-EOQ
select
-- Required Columns
url as resource,
case
when EXTRACT(DAY FROM CURRENT_TIMESTAMP - updated_at) <= 90 then 'ok'
else 'alarm'
end as status,
case
when EXTRACT(DAY FROM CURRENT_TIMESTAMP - updated_at) <= 90 then name_with_owner || ' is active.'
else name_with_owner || ' is inactive since ' || EXTRACT(DAY FROM CURRENT_TIMESTAMP - updated_at) || ' days.'
end as reason,
-- Additional Dimensions
name_with_owner
from
github_my_repository
where
not is_fork
order by
name_with_owner;
EOQ
}
query "repo_issue_deletion_limited_to_trusted_users" {
sql = <<-EOQ
with repo_admins as (
select distinct
name_with_owner,
array_agg(user_login) as admins
from
github_my_repository r
join
github_repository_collaborator c
on
r.name_with_owner = c.repository_full_name
and
c.permission = 'ADMIN'
group by
name_with_owner
)
select
-- Required Columns
r.url as resource,
case
when jsonb_array_length(to_jsonb(admins) - $1::text[]) > 0 then 'alarm'
else 'ok'
end as status,
case
when jsonb_array_length(to_jsonb(admins) - $1::text[]) > 2
then concat( 'Repository issue deletion permission allowed to untrusted users ', to_jsonb(admins) - $1::text[] #>> '{0}', ', ', to_jsonb(admins) - $1::text[] #>> '{1}', ' and ', (jsonb_array_length(to_jsonb(admins) - $1::text[]) - 2)::text, ' more.')
when jsonb_array_length(to_jsonb(admins) - $1::text[]) = 2
then concat('Repository issue deletion permission allowed to untrusted users ', to_jsonb(admins) - $1::text[] #>> '{0}', ' and ', to_jsonb(admins) - $1::text[] #>> '{1}', '.')
when jsonb_array_length(to_jsonb(admins) - $1::text[]) = 1
then concat('Repository issue deletion permission allowed to untrusted user ', to_jsonb(admins) - $1::text[] #>> '{0}', '.')
else 'Repository issue deletion permission limited to trusted users.'
end as reason,
-- Additional Dimensions
r.name_with_owner
from
github_my_repository as r
left join repo_admins as a on r.name_with_owner = a.name_with_owner;
EOQ
}
query "repo_linear_history_enabled" {
sql = <<-EOQ
select
-- Required Columns
url as resource,
case
when ((default_branch_ref -> 'branch_protection_rule') is not null and (default_branch_ref -> 'branch_protection_rule' ->> 'requires_linear_history')::bool = true) or (rebase_merge_allowed = true or squash_merge_allowed = true) then 'ok'
else 'alarm'
end as status,
name_with_owner || ' default branch ' || (default_branch_ref ->> 'name') || case when((default_branch_ref -> 'branch_protection_rule') is not null and (default_branch_ref -> 'branch_protection_rule' ->> 'requires_linear_history')::bool = true) or (rebase_merge_allowed = true or squash_merge_allowed = true) then ' has linear history enabled.' else ' has linear history disabled.' end as reason,
-- Additional Dimensions
name_with_owner
from
github_my_repository;
EOQ
}
query "repo_no_open_comments" {
sql = <<-EOQ
select
-- Required Columns
url as resource,
case
when (default_branch_ref -> 'branch_protection_rule') is null then 'info'
when (default_branch_ref -> 'branch_protection_rule' ->> 'requires_conversation_resolution')::bool = true then 'ok'
else 'alarm'
end as status,
name_with_owner || ' default branch ' || (default_branch_ref ->> 'name') ||
case
when (default_branch_ref -> 'branch_protection_rule') is null then ' branch protection rule unknown.'
when (default_branch_ref -> 'branch_protection_rule' ->> 'requires_conversation_resolution')::bool = true then ' requires conversation resolution before merge.'
else ' does not require conversation resolution before merge.'
end as reason,
-- Additional Dimensions
name_with_owner
from
github_my_repository;
EOQ
}
query "repo_open_branches_are_upto_date_before_merge" {
sql = <<-EOQ
select
-- Required Columns
url as resource,
case
when (default_branch_ref -> 'branch_protection_rule') is null then 'info'
when (default_branch_ref -> 'branch_protection_rule' ->> 'requires_strict_status_checks')::bool = true then 'ok'
else 'alarm'
end as status,
name_with_owner || ' default branch ' || (default_branch_ref ->> 'name') ||
case
when (default_branch_ref -> 'branch_protection_rule') is null then ' branch protection rule unknown.'
when (default_branch_ref -> 'branch_protection_rule' ->> 'requires_strict_status_checks')::bool = true then ' requires open branches to be upto date before merge.'
else ' does not require open branches to be upto date before merge.'
end as reason,
-- Additional Dimensions
name_with_owner
from
github_my_repository;
EOQ
}
query "repo_should_have_two_admins" {
sql = <<-EOQ
with repo_admins as (
select
name_with_owner,
url,
count(user_login) as admins
from github_my_repository r
join github_repository_collaborator c
on r.name_with_owner = c.repository_full_name
and c.permission = 'ADMIN'
group by name_with_owner, url
)
select
url as resource,
case
when admins >= 2 then 'ok'
else 'alarm'
end as status,
name_with_owner || case when admins >= 2 then ' has ' || admins::text || ' administrators.' else ' has only ' || admins::text || ' administrators.' end as reason,
name_with_owner
from repo_admins;
EOQ
}
query "repo_webhook_package_registery_security_settings_enabled" {
sql = <<-EOQ
with hooks_info as (
select
login as name,
url,
h as hook
from
github_my_organization,
jsonb_array_elements(hooks) h
union
select
name,
url,
h as hook
from
github_my_repository,
jsonb_array_elements(hooks) h
)
select
-- Required Columns
url as resource,
case
when (hook ->> 'active' = 'true'
and (hook -> 'config' ->> 'insecure_ssl' = '1'
or hook -> 'config' ->> 'secret' is null
or hook -> 'config' ->> 'url' not like '%https:%')) then 'alarm'
else 'ok'
end as status,
case
when (hook ->> 'active' = 'true'
and (hook -> 'config' ->> 'insecure_ssl' = '1'
or hook -> 'config' ->> 'secret' is null
or hook -> 'config' ->> 'url' not like '%https:%')) then (hook ->> 'id') || ' is an insecure hook.'
else (hook ->> 'id') || ' is a secure hook.'
end as reason,
-- Additional Dimensions
name
from
hooks_info;
EOQ
}