1-- This file and its contents are licensed under the Apache License 2.0.
2-- Please see the included NOTICE for copyright information and
3-- LICENSE-APACHE for a copy of the license.
4
5\c :TEST_DBNAME :ROLE_SUPERUSER
6
7CREATE TABLE conditions(
8    time TIMESTAMPTZ NOT NULL,
9    device INTEGER,
10    temperature FLOAT
11);
12
13-- Create a hypertable and show that it does not have any privileges
14SELECT * FROM create_hypertable('conditions', 'time', chunk_time_interval => '5 days'::interval);
15INSERT INTO conditions
16SELECT time, (random()*30)::int, random()*80 - 40
17FROM generate_series('2018-12-01 00:00'::timestamp, '2018-12-10 00:00'::timestamp, '1h') AS time;
18\z conditions
19\z _timescaledb_internal.*chunk
20
21-- Add privileges and show that they propagate to the chunks
22GRANT SELECT, INSERT ON conditions TO PUBLIC;
23\z conditions
24\z _timescaledb_internal.*chunk
25
26-- Create some more chunks and show that they also get the privileges.
27INSERT INTO conditions
28SELECT time, (random()*30)::int, random()*80 - 40
29FROM generate_series('2018-12-10 00:00'::timestamp, '2018-12-20 00:00'::timestamp, '1h') AS time;
30\z conditions
31\z _timescaledb_internal.*chunk
32
33-- Revoke one of the privileges and show that it propagate to the
34-- chunks.
35REVOKE INSERT ON conditions FROM PUBLIC;
36\z conditions
37\z _timescaledb_internal.*chunk
38
39-- Add some more chunks and show that it inherits the grants from the
40-- hypertable.
41INSERT INTO conditions
42SELECT time, (random()*30)::int, random()*80 - 40
43FROM generate_series('2018-12-20 00:00'::timestamp, '2018-12-30 00:00'::timestamp, '1h') AS time;
44\z conditions
45\z _timescaledb_internal.*chunk
46
47-- Change grants of one chunk explicitly and check that it is possible
48\z _timescaledb_internal._hyper_1_1_chunk
49GRANT UPDATE ON _timescaledb_internal._hyper_1_1_chunk TO PUBLIC;
50\z _timescaledb_internal._hyper_1_1_chunk
51REVOKE SELECT ON _timescaledb_internal._hyper_1_1_chunk FROM PUBLIC;
52\z _timescaledb_internal._hyper_1_1_chunk
53
54-- Check that revoking a permission first on the chunk and then on the
55-- hypertable that was added through the hypertable (INSERT and
56-- SELECT, in this case) still do not copy permissions from the
57-- hypertable (so there should not be a select permission to public on
58-- the chunk but there should be one on the hypertable).
59GRANT INSERT ON conditions TO PUBLIC;
60\z conditions
61\z _timescaledb_internal._hyper_1_2_chunk
62REVOKE SELECT ON _timescaledb_internal._hyper_1_2_chunk FROM PUBLIC;
63REVOKE INSERT ON conditions FROM PUBLIC;
64\z conditions
65\z _timescaledb_internal._hyper_1_2_chunk
66
67-- Check that granting permissions through hypertable does not remove
68-- separate grants on chunk.
69GRANT UPDATE ON _timescaledb_internal._hyper_1_3_chunk TO PUBLIC;
70\z conditions
71\z _timescaledb_internal._hyper_1_3_chunk
72GRANT INSERT ON conditions TO PUBLIC;
73REVOKE INSERT ON conditions FROM PUBLIC;
74\z conditions
75\z _timescaledb_internal._hyper_1_3_chunk
76
77-- Check that GRANT ALL IN SCHEMA adds privileges to the parent
78-- and also goes to chunks in another schema
79GRANT ALL ON ALL TABLES  IN SCHEMA public TO :ROLE_DEFAULT_PERM_USER_2;
80\z conditions
81\z _timescaledb_internal.*chunk
82
83-- Check that REVOKE ALL IN SCHEMA removes privileges of the parent
84-- and also goes to chunks in another schema
85REVOKE ALL ON ALL TABLES  IN SCHEMA public FROM :ROLE_DEFAULT_PERM_USER_2;
86\z conditions
87\z _timescaledb_internal.*chunk
88
89-- Create chunks in the same schema as the hypertable and check that
90-- they also get the same privileges as the hypertable
91CREATE TABLE measurements(
92    time TIMESTAMPTZ NOT NULL,
93    device INTEGER,
94    temperature FLOAT
95);
96
97-- Create a hypertable with chunks in the same schema
98SELECT * FROM create_hypertable('public.measurements', 'time', chunk_time_interval => '5 days'::interval, associated_schema_name => 'public');
99INSERT INTO measurements
100SELECT time, (random()*30)::int, random()*80 - 40
101FROM generate_series('2018-12-01 00:00'::timestamp, '2018-12-10 00:00'::timestamp, '1h') AS time;
102
103-- GRANT ALL and check privileges
104GRANT ALL ON ALL TABLES  IN SCHEMA public TO :ROLE_DEFAULT_PERM_USER_2;
105\z measurements
106\z conditions
107\z public.*chunk
108
109-- REVOKE ALL and check privileges
110REVOKE ALL ON ALL TABLES  IN SCHEMA public FROM :ROLE_DEFAULT_PERM_USER_2;
111\z measurements
112\z conditions
113\z public.*chunk
114