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
|
--
-- SELECT_INTO
--
SELECT *
INTO TABLE tmp1
FROM onek
WHERE onek.unique1 < 2;
DROP TABLE tmp1;
SELECT *
INTO TABLE tmp1
FROM onek2
WHERE onek2.unique1 < 2;
DROP TABLE tmp1;
--
-- SELECT INTO and INSERT permission, if owner is not allowed to insert.
--
CREATE SCHEMA selinto_schema;
CREATE USER selinto_user;
ALTER DEFAULT PRIVILEGES FOR ROLE selinto_user
REVOKE INSERT ON TABLES FROM selinto_user;
GRANT ALL ON SCHEMA selinto_schema TO public;
SET SESSION AUTHORIZATION selinto_user;
SELECT * INTO TABLE selinto_schema.tmp1
FROM pg_class WHERE relname like '%a%'; -- Error
ERROR: permission denied for relation tmp1
SELECT oid AS clsoid, relname, relnatts + 10 AS x
INTO selinto_schema.tmp2
FROM pg_class WHERE relname like '%b%'; -- Error
ERROR: permission denied for relation tmp2
CREATE TABLE selinto_schema.tmp3 (a,b,c)
AS SELECT oid,relname,relacl FROM pg_class
WHERE relname like '%c%'; -- Error
ERROR: permission denied for relation tmp3
RESET SESSION AUTHORIZATION;
ALTER DEFAULT PRIVILEGES FOR ROLE selinto_user
GRANT INSERT ON TABLES TO selinto_user;
SET SESSION AUTHORIZATION selinto_user;
SELECT * INTO TABLE selinto_schema.tmp1
FROM pg_class WHERE relname like '%a%'; -- OK
SELECT oid AS clsoid, relname, relnatts + 10 AS x
INTO selinto_schema.tmp2
FROM pg_class WHERE relname like '%b%'; -- OK
CREATE TABLE selinto_schema.tmp3 (a,b,c)
AS SELECT oid,relname,relacl FROM pg_class
WHERE relname like '%c%'; -- OK
RESET SESSION AUTHORIZATION;
DROP SCHEMA selinto_schema CASCADE;
NOTICE: drop cascades to 3 other objects
DETAIL: drop cascades to table selinto_schema.tmp1
drop cascades to table selinto_schema.tmp2
drop cascades to table selinto_schema.tmp3
DROP USER selinto_user;
--
-- CREATE TABLE AS/SELECT INTO as last command in a SQL function
-- have been known to cause problems
--
CREATE FUNCTION make_table() RETURNS VOID
AS $$
CREATE TABLE created_table AS SELECT * FROM int8_tbl;
$$ LANGUAGE SQL;
SELECT make_table();
make_table
------------
(1 row)
SELECT * FROM created_table;
q1 | q2
------------------+-------------------
123 | 456
123 | 4567890123456789
4567890123456789 | 123
4567890123456789 | 4567890123456789
4567890123456789 | -4567890123456789
(5 rows)
DROP TABLE created_table;
--
-- Disallowed uses of SELECT ... INTO. All should fail
--
DECLARE foo CURSOR FOR SELECT 1 INTO b;
ERROR: SELECT ... INTO is not allowed here
LINE 1: DECLARE foo CURSOR FOR SELECT 1 INTO b;
^
COPY (SELECT 1 INTO frak UNION SELECT 2) TO 'blob';
ERROR: COPY (SELECT INTO) is not supported
SELECT * FROM (SELECT 1 INTO f) bar;
ERROR: SELECT ... INTO is not allowed here
LINE 1: SELECT * FROM (SELECT 1 INTO f) bar;
^
CREATE VIEW foo AS SELECT 1 INTO b;
ERROR: views must not contain SELECT INTO
INSERT INTO b SELECT 1 INTO f;
ERROR: SELECT ... INTO is not allowed here
LINE 1: INSERT INTO b SELECT 1 INTO f;
^
|
