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
|
CREATE FUNCTION @extschema@.apply_privileges(p_parent_schema text, p_parent_tablename text, p_child_schema text, p_child_tablename text, p_job_id bigint DEFAULT NULL) RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
ex_context text;
ex_detail text;
ex_hint text;
ex_message text;
v_all text[] := ARRAY['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER'];
v_child_grant record;
v_child_owner text;
v_grantees text[];
v_job_id bigint;
v_jobmon boolean;
v_jobmon_schema text;
v_match boolean;
v_parent_grant record;
v_parent_owner text;
v_revoke text;
v_row_revoke record;
v_sql text;
v_step_id bigint;
BEGIN
/*
* Apply privileges and ownership that exist on a given parent to the given child table
*/
SELECT jobmon INTO v_jobmon FROM @extschema@.part_config WHERE parent_table = p_parent_schema ||'.'|| p_parent_tablename;
IF v_jobmon IS NULL THEN
RAISE EXCEPTION 'Given table is not managed by this extention: %.%', p_parent_schema, p_parent_tablename;
END IF;
SELECT pg_get_userbyid(c.relowner) INTO v_parent_owner
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
WHERE n.nspname = p_parent_schema::name
AND c.relname = p_parent_tablename::name;
SELECT pg_get_userbyid(c.relowner) INTO v_child_owner
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
WHERE n.nspname = p_child_schema::name
AND c.relname = p_child_tablename::name;
IF v_parent_owner IS NULL THEN
RAISE EXCEPTION 'Given parent table does not exist: %.%', p_parent_schema, p_parent_tablename;
END IF;
IF v_child_owner IS NULL THEN
RAISE EXCEPTION 'Given child table does not exist: %.%', p_child_schema, p_child_tablename;
END IF;
IF v_jobmon THEN
SELECT nspname INTO v_jobmon_schema FROM pg_namespace n, pg_extension e WHERE e.extname = 'pg_jobmon' AND e.extnamespace = n.oid;
END IF;
IF v_jobmon_schema IS NOT NULL THEN
IF p_job_id IS NULL THEN
EXECUTE format('SELECT %I.add_job(%L)', v_jobmon_schema, format('PARTMAN APPLYING PRIVILEGES TO CHILD TABLE: %s.%s', p_child_schema, p_child_tablename)) INTO v_job_id;
ELSE
v_job_id := p_job_id;
END IF;
EXECUTE format('SELECT %I.add_step(%L, %L)', v_jobmon_schema, v_job_id, format('Setting new child table privileges for %s.%s', p_child_schema, p_child_tablename)) INTO v_step_id;
END IF;
IF v_jobmon_schema IS NOT NULL THEN
EXECUTE format('SELECT %I.update_step(%L, %L, %L)'
, v_jobmon_schema
, v_step_id
, 'PENDING'
, format('Applying privileges on child partition: %s.%s'
, p_child_schema
, p_child_tablename)
);
END IF;
FOR v_parent_grant IN
SELECT array_agg(DISTINCT privilege_type::text ORDER BY privilege_type::text) AS types
, grantee
FROM @extschema@.table_privs
WHERE table_schema = p_parent_schema::name AND table_name = p_parent_tablename::name
GROUP BY grantee
LOOP
-- Compare parent & child grants. Don't re-apply if it already exists
v_match := false;
v_sql := NULL;
FOR v_child_grant IN
SELECT array_agg(DISTINCT privilege_type::text ORDER BY privilege_type::text) AS types
, grantee
FROM @extschema@.table_privs
WHERE table_schema = p_child_schema::name AND table_name = p_child_tablename::name
GROUP BY grantee
LOOP
IF v_parent_grant.types = v_child_grant.types AND v_parent_grant.grantee = v_child_grant.grantee THEN
v_match := true;
END IF;
END LOOP;
IF v_match = false THEN
IF v_parent_grant.grantee = 'PUBLIC' THEN
v_sql := 'GRANT %s ON %I.%I TO %s';
ELSE
v_sql := 'GRANT %s ON %I.%I TO %I';
END IF;
EXECUTE format(v_sql
, array_to_string(v_parent_grant.types, ',')
, p_child_schema
, p_child_tablename
, v_parent_grant.grantee);
v_sql := NULL;
SELECT string_agg(r, ',') INTO v_revoke FROM (SELECT unnest(v_all) AS r EXCEPT SELECT unnest(v_parent_grant.types)) x;
IF v_revoke IS NOT NULL THEN
IF v_parent_grant.grantee = 'PUBLIC' THEN
v_sql := 'REVOKE %s ON %I.%I FROM %s CASCADE';
ELSE
v_sql := 'REVOKE %s ON %I.%I FROM %I CASCADE';
END IF;
EXECUTE format(v_sql
, v_revoke
, p_child_schema
, p_child_tablename
, v_parent_grant.grantee);
v_sql := NULL;
END IF;
END IF;
v_grantees := array_append(v_grantees, v_parent_grant.grantee::text);
END LOOP;
-- Revoke all privileges from roles that have none on the parent
IF v_grantees IS NOT NULL THEN
FOR v_row_revoke IN
SELECT role FROM (
SELECT DISTINCT grantee::text AS role FROM @extschema@.table_privs WHERE table_schema = p_child_schema::name AND table_name = p_child_tablename::name
EXCEPT
SELECT unnest(v_grantees)) x
LOOP
IF v_row_revoke.role IS NOT NULL THEN
IF v_row_revoke.role = 'PUBLIC' THEN
v_sql := 'REVOKE ALL ON %I.%I FROM %s';
ELSE
v_sql := 'REVOKE ALL ON %I.%I FROM %I';
END IF;
EXECUTE format(v_sql
, p_child_schema
, p_child_tablename
, v_row_revoke.role);
END IF;
END LOOP;
END IF;
IF v_parent_owner <> v_child_owner THEN
EXECUTE format('ALTER TABLE %I.%I OWNER TO %I'
, p_child_schema
, p_child_tablename
, v_parent_owner);
END IF;
IF v_jobmon_schema IS NOT NULL THEN
EXECUTE format('SELECT %I.update_step(%L, %L, %L)', v_jobmon_schema, v_step_id, 'OK', 'Done');
IF p_job_id IS NULL THEN
EXECUTE format('SELECT %I.close_job(%L)', v_jobmon_schema, v_job_id);
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
GET STACKED DIAGNOSTICS ex_message = MESSAGE_TEXT,
ex_context = PG_EXCEPTION_CONTEXT,
ex_detail = PG_EXCEPTION_DETAIL,
ex_hint = PG_EXCEPTION_HINT;
IF v_jobmon_schema IS NOT NULL THEN
IF v_job_id IS NULL THEN
EXECUTE format('SELECT %I.add_job(''PARTMAN RE-APPLYING PRIVILEGES TO ALL CHILD TABLES OF: %s'')', v_jobmon_schema, p_parent_tablename) INTO v_job_id;
EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before job logging started'')', v_jobmon_schema, v_job_id, p_parent_tablename) INTO v_step_id;
ELSIF v_step_id IS NULL THEN
EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before first step logged'')', v_jobmon_schema, v_job_id) INTO v_step_id;
END IF;
EXECUTE format('SELECT %I.update_step(%s, ''CRITICAL'', %L)', v_jobmon_schema, v_step_id, 'ERROR: '||coalesce(SQLERRM,'unknown'));
EXECUTE format('SELECT %I.fail_job(%s)', v_jobmon_schema, v_job_id);
END IF;
RAISE EXCEPTION '%
CONTEXT: %
DETAIL: %
HINT: %', ex_message, ex_context, ex_detail, ex_hint;
END
$$;
|
