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