-
Notifications
You must be signed in to change notification settings - Fork 111
/
filtering.go
292 lines (254 loc) · 9.38 KB
/
filtering.go
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
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
package pgdb
import (
"fmt"
"strings"
"time"
"github.com/lib/pq"
"github.com/pkg/errors"
"github.com/sirupsen/logrus"
"github.com/chef/automate/api/external/lib/errorutils"
"github.com/chef/automate/api/interservice/compliance/common"
authzConstants "github.com/chef/automate/components/authz-service/constants"
"github.com/chef/automate/components/compliance-service/utils"
"github.com/chef/automate/lib/pgutils"
"github.com/chef/automate/lib/stringutils"
)
func mergeFilters(mergeableFilters []*common.Filter) ([]common.Filter, error) {
filterMap := make(map[string]common.Filter)
for _, mf := range mergeableFilters {
var values []string
key := mf.Key
if f, found := filterMap[key]; found {
if f.Exclude != mf.Exclude {
return nil, errors.New("Filters are not allowed to be inclusive and exclusive on the same field.")
}
values = append(f.Values, mf.Values...)
} else {
values = mf.Values
}
filterMap[key] = common.Filter{Key: key, Values: values, Exclude: mf.Exclude}
}
filters := make([]common.Filter, 0, len(filterMap))
for _, v := range filterMap {
filters = append(filters, v)
}
return filters, nil
}
func tagFiltersToSQLConditions(tagFilters []common.Filter) ([]string, error) {
sqlTagConditions := make([]string, len(tagFilters))
for i, filter := range tagFilters {
tagKeyFilter := strings.TrimPrefix(filter.Key, "tags:")
newSqlTagCondition, err := patternMatchTags(tagKeyFilter, filter.Values, TagsTableAbbrev)
if err != nil {
return sqlTagConditions, errors.Wrap(err, "tagFiltersToSQLConditions error")
}
if filter.Exclude {
newSqlTagCondition = fmt.Sprintf("NOT %s", newSqlTagCondition)
}
sqlTagConditions[i] = newSqlTagCondition
}
return sqlTagConditions, nil
}
// Takes a filter map (should be validated for content) and table abbreviation
// and returns WHERE and HAVING SQL conditions
func buildWhereHavingFilter(mergeableFilters []*common.Filter, tableAbbrev string, filterField map[string]string) (whereFilter string, havingFilter string, err error) {
if len(mergeableFilters) == 0 {
return "", "", nil
}
filters, err := mergeFilters(mergeableFilters)
if err != nil {
return "", "", errors.Wrap(err, "buildWhereHavingFilter error")
}
var conditions []string // nolint: prealloc
var tagFilters []common.Filter
for _, filter := range filters {
var newCondition string
var err error
if strings.HasPrefix(filter.Key, "tags:") {
tagFilters = append(tagFilters, filter)
continue
} else {
switch filterField[filter.Key] {
case "":
return "", "", &errorutils.InvalidError{Msg: fmt.Sprintf("Unsupported filter field: %s", filter.Key)}
case "source_region", "name":
newCondition, err = wherePatternMatch(filterField[filter.Key], filter.Values, tableAbbrev)
case "statechange_timestamp", "last_contact", "last_run ->> 'EndTime'", "last_scan ->> 'EndTime'":
newCondition, err = whereFieldBetween(filterField[filter.Key], filter.Values, tableAbbrev)
case "manager_id":
newCondition, err = whereNodeManagerNodeExists(filterField[filter.Key], filter.Values, tableAbbrev)
case "project":
newCondition, err = whereProjectsMatch(filterField[filter.Key], filter.Values, tableAbbrev)
default:
newCondition, err = whereFieldIn(filterField[filter.Key], filter.Values, tableAbbrev)
}
}
if newCondition == "" {
continue
}
if err != nil {
return "", "", errors.Wrap(err, "buildWhereHavingFilter error")
}
if filter.Exclude {
newCondition = fmt.Sprintf("NOT (%s)", newCondition)
}
conditions = append(conditions, newCondition)
}
if len(tagFilters) > 0 {
tagConditions, err := tagFiltersToSQLConditions(tagFilters)
if err != nil {
return "", "", errors.Wrap(err, "buildWhereHavingFilter error building tags")
}
havingFilter = fmt.Sprintf("HAVING (%s)", strings.Join(tagConditions, ") AND ("))
}
if len(conditions) > 0 {
whereFilter = fmt.Sprintf("WHERE (%s)", strings.Join(conditions, " AND "))
}
logrus.Debugf("buildWhereHavingFilter, whereFilter=%s, havingFilter=%s", whereFilter, havingFilter)
return whereFilter, havingFilter, nil
}
// Builds an IN where condition like: j.parent_id IN ('e57605ed-bb8a-49b8-606c-af0e2b31b139')
func whereFieldIn(field string, arr []string, tableAbbrev string) (condition string, err error) {
if !acceptedJSONFilterFields(field) {
if !pgutils.IsSqlSafe(field) {
return "", &errorutils.InvalidError{Msg: fmt.Sprintf("Unsupported character found in field: %s", field)}
}
}
condition += fmt.Sprintf("%s.%s IN (", tableAbbrev, field)
if len(arr) > 0 {
for index, item := range arr {
condition += fmt.Sprintf("'%s'", pgutils.EscapeLiteralForPG(item))
if index < len(arr)-1 {
condition += ","
}
}
} else {
condition += "''"
}
return condition + ")", nil
}
func acceptedJSONFilterFields(field string) bool {
switch field {
case "last_run ->> 'EndTime'":
return true
case "last_scan ->> 'EndTime'":
return true
case "last_run ->> 'Status'":
return true
case "last_scan ->> 'Status'":
return true
case "last_run ->> 'PenultimateStatus'":
return true
case "last_scan ->> 'PenultimateStatus'":
return true
default:
return false
}
}
func whereFieldBetween(field string, arr []string, tableAbbrev string) (condition string, err error) {
if !acceptedJSONFilterFields(field) {
if !pgutils.IsSqlSafe(field) {
return "", &errorutils.InvalidError{Msg: fmt.Sprintf("Unsupported character found in field: %s", field)}
}
}
if len(arr) != 2 {
return "", &errorutils.InvalidError{Msg: fmt.Sprintf("Two params requires for whereFieldBetween %d", len(arr))}
}
for _, elem := range arr {
_, err := time.Parse(time.RFC3339, elem)
if err != nil {
return "", &errorutils.InvalidError{Msg: fmt.Sprintf("Invalid timestamp: %s", elem)}
}
}
condition = fmt.Sprintf("%s.%s BETWEEN SYMMETRIC '%s' AND '%s'", tableAbbrev, field, arr[0], arr[1])
return condition, nil
}
// because of the n:m with the tags table and the ability to pass any number of tag queries
// that are ANDed or ORed, we use string_agg to denormalize the tags into a single string that
// can take multiple AND and OR conditions
func patternMatchTags(field string, arr []string, tableAbbrev string) (condition string, err error) {
if !pgutils.IsSqlSafe(field) {
return "", &errorutils.InvalidError{Msg: fmt.Sprintf("Unsupported character found in field: %s", field)}
}
// if no value is provided for the tag, we query for any value
if len(arr) == 0 {
arr = append(arr, "")
}
valueLikes := make([]string, len(arr))
for index, item := range arr {
// concat needed as string_agg returns NULL when no tags exist and LIKE on NULL is not working
valueLikes[index] = fmt.Sprintf("concat('',string_agg(',' || %s.key || ':' || %s.value, '')) LIKE '%%,%s:%s%%'", tableAbbrev, tableAbbrev, field, item)
}
return strings.Join(valueLikes, " OR "), nil
}
func Unique(a []string) []string {
seen := map[string]bool{}
var b []string
for _, v := range a {
if _, ok := seen[v]; !ok {
seen[v] = true
b = append(b, v)
}
}
return b
}
func wherePatternMatch(field string, arr []string, tableAbbrev string) (condition string, err error) {
if !pgutils.IsSqlSafe(field) {
return "", &errorutils.InvalidError{Msg: fmt.Sprintf("Unsupported character found in field: %s", field)}
}
condition += fmt.Sprintf("COALESCE(%s.%s, '') LIKE ", tableAbbrev, field)
if len(arr) == 0 {
condition += "''"
return condition, nil
}
arr = Unique(arr)
for index, item := range arr {
item = strings.TrimSuffix(item, "*")
condition += fmt.Sprintf("'%s%%'", pgutils.EscapeLiteralForPGPatternMatch(item))
if index < len(arr)-1 {
condition += fmt.Sprintf(" OR COALESCE(%s.%s, '') LIKE ", tableAbbrev, field)
}
}
return condition, nil
}
func whereNodeManagerNodeExists(field string, arr []string, tableAbbrev string) (condition string, err error) {
if !pgutils.IsSqlSafe(field) {
return "", &errorutils.InvalidError{Msg: fmt.Sprintf("Unsupported character found in field: %s", field)}
}
if len(arr) == 0 {
condition += fmt.Sprintf("not exists (select 1 from node_managers_nodes nmn where %s.id = nmn.node_id)", tableAbbrev)
return condition, nil
}
for _, item := range arr {
if !utils.IsSafeUUID(item) {
return "", &errorutils.InvalidError{Msg: fmt.Sprintf("Unsupported character found in: %s", item)}
}
}
condition += fmt.Sprintf("exists (select 1 from node_managers_nodes nmn where %s.id = nmn.node_id and nmn.%s = ANY('{%s}'::text[]))", tableAbbrev, field, strings.Join(arr, ","))
return condition, nil
}
func whereProjectsMatch(_ string, arr []string, tableAbbrev string) (string, error) {
var condition string
if len(arr) == 0 {
return "", nil
}
refinedValues := stringutils.SliceFilter(arr, func(projectId string) bool {
return projectId != authzConstants.UnassignedProjectID
})
if len(refinedValues) > 0 {
value, err := pq.Array(refinedValues).Value()
if err != nil {
return "", err
}
condition = fmt.Sprintf("exists (select 1 from projects p join nodes_projects np on p.id = np.project_id where np.node_id = %s.id and p.project_id = ANY('%s'::text[]))", tableAbbrev, value)
}
if stringutils.SliceContains(arr, authzConstants.UnassignedProjectID) {
unassignedCondition := fmt.Sprintf("not exists (select 1 from projects p join nodes_projects np on p.id = np.project_id where np.node_id = %s.id)", tableAbbrev)
if condition == "" {
condition = unassignedCondition
} else {
condition = condition + " OR " + unassignedCondition
}
}
return condition, nil
}