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
|
-- General setup
\set SHOW_CONTEXT never
-- Check for local server reset
SELECT "PoWA".powa_reset(0);
-- check catalog FK
DO
$_$
DECLARE
v_dbid oid;
v_nb integer;
v_catname text;
v_prefix text;
BEGIN
SELECT oid INTO v_dbid FROM "PoWA".powa_catalog_databases
WHERE srvid = 1 AND datname = current_database();
DELETE FROM "PoWA".powa_catalog_databases
WHERE srvid = 1 AND datname = current_database();
FOR v_catname IN SELECT catname FROM "PoWA".powa_catalogs
LOOP
-- get the necessary object name
SELECT 'powa_catalog_' || replace(v_catname, 'pg_', '') INTO v_prefix;
-- There shouldn't be any row left for that databa in any catalog
EXECUTE format('SELECT count(*) FROM "PoWA".%I WHERE dbid = %s',
v_prefix, v_dbid) INTO v_nb;
IF v_nb != 0 THEN
RAISE WARNING 'table "PoWA".% for catalog % has % rows',
v_prefix, v_catname, v_nb;
END IF;
-- but there should be record in the src_tmp tables
EXECUTE format('SELECT count(*) FROM "PoWA".%I', v_prefix) INTO v_nb;
IF v_nb = 0 THEN
RAISE WARNING 'table "PoWA".% for catalog % has % rows',
v_prefix || '_src_tmp', v_catname, v_nb;
END IF;
END LOOP;
END;
$_$ LANGUAGE plpgsql;
SELECT "PoWA".powa_reset(1);
-- There shouldn't be any row left for that server in any catalog
DO
$_$
DECLARE
v_nb integer;
v_catname text;
v_prefix text;
BEGIN
FOR v_catname IN SELECT catname FROM "PoWA".powa_catalogs
LOOP
-- get the necessary object name
SELECT 'powa_catalog_' || replace(v_catname, 'pg_', '') INTO v_prefix;
EXECUTE format('SELECT count(*) FROM "PoWA".%I', v_prefix) INTO v_nb;
IF v_nb != 0 THEN
RAISE WARNING 'table "PoWA".% for catalog % has % rows',
v_prefix, v_catname, v_nb;
END IF;
EXECUTE format('SELECT count(*) FROM "PoWA".%I', v_prefix) INTO v_nb;
IF v_nb != 0 THEN
RAISE WARNING 'table "PoWA".% for catalog % has % rows',
v_prefix || '_src_tmp', v_catname, v_nb;
END IF;
END LOOP;
END;
$_$ LANGUAGE plpgsql;
-- Check remote server removal
DELETE FROM "PoWA".powa_servers WHERE id = 1;
-- dropping powa will leave the role initially created, and we should be able
-- to reinstall powa again
DROP EXTENSION powa;
CREATE EXTENSION powa WITH SCHEMA "PoWA";
-- we shouldn't assigned the existing role to our pseudo predefined role, even
-- if they exist with the default name
SELECT * FROM "PoWA".powa_roles WHERE rolname IS NOT NULL;
-- and also shouldn't put back any ACL on the previous roles
SELECT has_table_privilege('powa_admin', '"PoWA".powa_servers', 'SELECT');
-- We shouldn't be able to grant or revoke privileges in that situation
SELECT "PoWA".powa_revoke();
SELECT "PoWA".powa_grant();
-- we should be able to reuse the initially created roles
SELECT "PoWA".setup_powa_roles(true);
SELECT has_table_privilege('powa_admin', '"PoWA".powa_servers', 'SELECT');
-- cleanup ACL and remove the powa pseudo predefined roles
SELECT "PoWA".powa_revoke();
SELECT has_table_privilege('powa_admin', '"PoWA".powa_servers', 'SELECT');
DROP ROLE powa_admin;
DROP ROLE powa_read_all_data;
DROP ROLE powa_read_all_metrics;
DROP ROLE powa_write_all_data;
DROP ROLE powa_snapshot;
DROP ROLE powa_signal_backend;
|
