-
Notifications
You must be signed in to change notification settings - Fork 1.1k
Expand file tree
/
Copy pathdrop_owned-16.out
More file actions
168 lines (149 loc) · 12.3 KB
/
Copy pathdrop_owned-16.out
File metadata and controls
168 lines (149 loc) · 12.3 KB
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
-- This file and its contents are licensed under the Apache License 2.0.
-- Please see the included NOTICE for copyright information and
-- LICENSE-APACHE for a copy of the license.
\c :TEST_DBNAME :ROLE_SUPERUSER
CREATE SCHEMA hypertable_schema;
GRANT ALL ON SCHEMA hypertable_schema TO :ROLE_DEFAULT_PERM_USER;
SET ROLE :ROLE_DEFAULT_PERM_USER;
CREATE TABLE hypertable_schema.default_perm_user (time timestamptz, temp float, location int);
SELECT create_hypertable('hypertable_schema.default_perm_user', 'time', 'location', 2);
create_hypertable
-------------------------------------------
(1,hypertable_schema,default_perm_user,t)
INSERT INTO hypertable_schema.default_perm_user VALUES ('2001-01-01 01:01:01', 23.3, 1);
RESET ROLE;
CREATE TABLE hypertable_schema.superuser (time timestamptz, temp float, location int);
SELECT create_hypertable('hypertable_schema.superuser', 'time', 'location', 2);
create_hypertable
-----------------------------------
(2,hypertable_schema,superuser,t)
INSERT INTO hypertable_schema.superuser VALUES ('2001-01-01 01:01:01', 23.3, 1);
SELECT * FROM _timescaledb_catalog.hypertable ORDER BY id;
id | schema_name | table_name | associated_schema_name | associated_table_prefix | num_dimensions | chunk_sizing_func_schema | chunk_sizing_func_name | chunk_target_size | compression_state | compressed_hypertable_id | status
----+-------------------+-------------------+------------------------+-------------------------+----------------+--------------------------+--------------------------+-------------------+-------------------+--------------------------+--------
1 | hypertable_schema | default_perm_user | _timescaledb_internal | _hyper_1 | 2 | _timescaledb_functions | calculate_chunk_interval | 0 | 0 | | 0
2 | hypertable_schema | superuser | _timescaledb_internal | _hyper_2 | 2 | _timescaledb_functions | calculate_chunk_interval | 0 | 0 | | 0
SELECT id, hypertable_id, schema_name, table_name, status, osm_chunk FROM _timescaledb_catalog.chunk;
id | hypertable_id | schema_name | table_name | status | osm_chunk
----+---------------+-----------------------+------------------+--------+-----------
1 | 1 | _timescaledb_internal | _hyper_1_1_chunk | 0 | f
2 | 2 | _timescaledb_internal | _hyper_2_2_chunk | 0 | f
DROP OWNED BY :ROLE_DEFAULT_PERM_USER;
SELECT * FROM _timescaledb_catalog.hypertable ORDER BY id;
id | schema_name | table_name | associated_schema_name | associated_table_prefix | num_dimensions | chunk_sizing_func_schema | chunk_sizing_func_name | chunk_target_size | compression_state | compressed_hypertable_id | status
----+-------------------+------------+------------------------+-------------------------+----------------+--------------------------+--------------------------+-------------------+-------------------+--------------------------+--------
2 | hypertable_schema | superuser | _timescaledb_internal | _hyper_2 | 2 | _timescaledb_functions | calculate_chunk_interval | 0 | 0 | | 0
SELECT id, hypertable_id, schema_name, table_name, status, osm_chunk FROM _timescaledb_catalog.chunk;
id | hypertable_id | schema_name | table_name | status | osm_chunk
----+---------------+-----------------------+------------------+--------+-----------
2 | 2 | _timescaledb_internal | _hyper_2_2_chunk | 0 | f
DROP TABLE hypertable_schema.superuser;
--everything should be cleaned up
SELECT * FROM _timescaledb_catalog.hypertable GROUP BY id;
id | schema_name | table_name | associated_schema_name | associated_table_prefix | num_dimensions | chunk_sizing_func_schema | chunk_sizing_func_name | chunk_target_size | compression_state | compressed_hypertable_id | status
----+-------------+------------+------------------------+-------------------------+----------------+--------------------------+------------------------+-------------------+-------------------+--------------------------+--------
SELECT id, hypertable_id, schema_name, table_name, status, osm_chunk FROM _timescaledb_catalog.chunk;
id | hypertable_id | schema_name | table_name | status | osm_chunk
----+---------------+-------------+------------+--------+-----------
SELECT * FROM _timescaledb_catalog.dimension;
id | hypertable_id | column_name | column_type | aligned | num_slices | partitioning_func_schema | partitioning_func | interval_length | compress_interval_length | integer_now_func_schema | integer_now_func
----+---------------+-------------+-------------+---------+------------+--------------------------+-------------------+-----------------+--------------------------+-------------------------+------------------
SELECT * FROM _timescaledb_catalog.dimension_slice;
id | chunk_id | dimension_id | range_start | range_end
----+----------+--------------+-------------+-----------
SELECT chunk_id, id AS dimension_slice_id, format('constraint_%s', id)::name AS constraint_name FROM _timescaledb_catalog.dimension_slice ORDER BY chunk_id, dimension_slice_id;
chunk_id | dimension_slice_id | constraint_name
----------+--------------------+-----------------
-- test drop owned in database without extension installed
\c :TEST_DBNAME :ROLE_SUPERUSER
CREATE database test_drop_owned;
\c test_drop_owned
DROP OWNED BY :ROLE_SUPERUSER;
\c :TEST_DBNAME :ROLE_SUPERUSER
DROP DATABASE test_drop_owned WITH (FORCE);
-- Test that dependencies on roles are added to chunks when creating
-- new chunks. If that is not done, DROP OWNED BY will not revoke the
-- privilege on the chunk.
CREATE TABLE sensor_data(time timestamptz not null, cpu double precision null);
SELECT * FROM create_hypertable('sensor_data','time');
hypertable_id | schema_name | table_name | created
---------------+-------------+-------------+---------
3 | public | sensor_data | t
INSERT INTO sensor_data
SELECT time,
random() AS cpu
FROM generate_series('2020-01-01'::timestamptz, '2020-01-24'::timestamptz, INTERVAL '10 minute') AS g1(time);
\dp sensor_data
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+-------------+-------+-------------------+-------------------+----------
public | sensor_data | table | | |
\dp _timescaledb_internal._hyper_3*
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
-----------------------+------------------+-------+-------------------+-------------------+----------
_timescaledb_internal | _hyper_3_3_chunk | table | | |
_timescaledb_internal | _hyper_3_4_chunk | table | | |
_timescaledb_internal | _hyper_3_5_chunk | table | | |
_timescaledb_internal | _hyper_3_6_chunk | table | | |
_timescaledb_internal | _hyper_3_7_chunk | table | | |
GRANT SELECT ON sensor_data TO :ROLE_DEFAULT_PERM_USER;
\dp sensor_data
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+-------------+-------+--------------------------------+-------------------+----------
public | sensor_data | table | super_user=arwdDxt/super_user +| |
| | | default_perm_user=r/super_user | |
\dp _timescaledb_internal._hyper_3*
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
-----------------------+------------------+-------+--------------------------------+-------------------+----------
_timescaledb_internal | _hyper_3_3_chunk | table | super_user=arwdDxt/super_user +| |
| | | default_perm_user=r/super_user | |
_timescaledb_internal | _hyper_3_4_chunk | table | super_user=arwdDxt/super_user +| |
| | | default_perm_user=r/super_user | |
_timescaledb_internal | _hyper_3_5_chunk | table | super_user=arwdDxt/super_user +| |
| | | default_perm_user=r/super_user | |
_timescaledb_internal | _hyper_3_6_chunk | table | super_user=arwdDxt/super_user +| |
| | | default_perm_user=r/super_user | |
_timescaledb_internal | _hyper_3_7_chunk | table | super_user=arwdDxt/super_user +| |
| | | default_perm_user=r/super_user | |
-- Insert more chunks after adding the user to the hypertable. These
-- will now get the privileges of the hypertable.
INSERT INTO sensor_data
SELECT time,
random() AS cpu
FROM generate_series('2020-01-20'::timestamptz, '2020-02-05'::timestamptz, INTERVAL '10 minute') AS g1(time);
\dp _timescaledb_internal._hyper_3*
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
-----------------------+------------------+-------+--------------------------------+-------------------+----------
_timescaledb_internal | _hyper_3_3_chunk | table | super_user=arwdDxt/super_user +| |
| | | default_perm_user=r/super_user | |
_timescaledb_internal | _hyper_3_4_chunk | table | super_user=arwdDxt/super_user +| |
| | | default_perm_user=r/super_user | |
_timescaledb_internal | _hyper_3_5_chunk | table | super_user=arwdDxt/super_user +| |
| | | default_perm_user=r/super_user | |
_timescaledb_internal | _hyper_3_6_chunk | table | super_user=arwdDxt/super_user +| |
| | | default_perm_user=r/super_user | |
_timescaledb_internal | _hyper_3_7_chunk | table | super_user=arwdDxt/super_user +| |
| | | default_perm_user=r/super_user | |
_timescaledb_internal | _hyper_3_8_chunk | table | super_user=arwdDxt/super_user +| |
| | | default_perm_user=r/super_user | |
-- This should revoke the privileges on both the hypertable and the chunks.
DROP OWNED BY :ROLE_DEFAULT_PERM_USER;
\dp sensor_data
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+-------------+-------+-------------------------------+-------------------+----------
public | sensor_data | table | super_user=arwdDxt/super_user | |
\dp _timescaledb_internal._hyper_3*
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
-----------------------+------------------+-------+-------------------------------+-------------------+----------
_timescaledb_internal | _hyper_3_3_chunk | table | super_user=arwdDxt/super_user | |
_timescaledb_internal | _hyper_3_4_chunk | table | super_user=arwdDxt/super_user | |
_timescaledb_internal | _hyper_3_5_chunk | table | super_user=arwdDxt/super_user | |
_timescaledb_internal | _hyper_3_6_chunk | table | super_user=arwdDxt/super_user | |
_timescaledb_internal | _hyper_3_7_chunk | table | super_user=arwdDxt/super_user | |
_timescaledb_internal | _hyper_3_8_chunk | table | super_user=arwdDxt/super_user | |