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
|
SELECT case
when setting::int >= 130000 then 'PG 13+'
when setting::int >= 100000 then 'PG 10..12'
end as "regression output for PG version"
FROM pg_settings where name = 'server_version_num';
regression output for PG version
----------------------------------
PG 13+
(1 row)
SET ROLE mere_mortal;
SELECT extname FROM pg_extension ORDER BY 1;
extname
---------
citext
plpgsql
(2 rows)
-- pre-existing extension
CREATE EXTENSION plpgsql;
ERROR: extension "plpgsql" already exists
COMMENT ON EXTENSION hstore IS 'plpgsql comment';
ERROR: extension "hstore" does not exist
SELECT extname FROM pg_extension ORDER BY 1;
extname
---------
citext
plpgsql
(2 rows)
-- non-whitelisted extension
CREATE EXTENSION hstore;
ERROR: permission denied to create extension "hstore"
HINT: Must have CREATE privilege on current database to create this extension.
COMMENT ON EXTENSION hstore IS 'hstore comment';
ERROR: extension "hstore" does not exist
SELECT extname FROM pg_extension ORDER BY 1;
extname
---------
citext
plpgsql
(2 rows)
-- whitelisted extension, but dependency is missing
CREATE EXTENSION earthdistance;
ERROR: required extension "cube" is not installed
HINT: Use CREATE EXTENSION ... CASCADE to install required extensions too.
SELECT extname FROM pg_extension ORDER BY 1;
extname
---------
citext
plpgsql
(2 rows)
-- drop non-whitelisted extension
DROP EXTENSION plpgsql;
ERROR: must be owner of extension plpgsql
SELECT extname FROM pg_extension ORDER BY 1;
extname
---------
citext
plpgsql
(2 rows)
|
