Timescaledb: Suggestion to Improve Docs about Hypertable Permission Handling

[thaxy]

Hello,

I cannot find a section in the official docs about permission handling with hypertables.
I have a custom schema (not public) and want to allow a specific user/role to be able to only insert/select to/from a specific hypertable created by a superuser.

So I granted the role USAGE on my schema and SELECT and INSERT privileges on the hypertable. That works fine for any other (non hyper)table.

When the hypertable is empty I cannot insert with my tight permission role values into my table (actually the chunks behind the hypertable). It says:

ERROR: permission denied for relation _hyper_1_1_chunk
Incrementing every time I repeat the insert query:

ERROR:  permission denied for relation _hyper_1_2_chunk
ERROR:  permission denied for relation _hyper_1_3_chunk
[...]

When I now insert a record as a superuser a chunk will be created and my previous user is then able to insert records into the hypertable. I assume that only works until the chunk size is reached. Is this a bug?
I noticed that the chunks are created within the _timescaledb_internal schema.
I have also tried to grant the role ALL PRIVILEGES on that schema - still failed. Another issue is that I don't know whether the role needs other schemas like _timescaledb_cache for performance reasons.

Update (maybe this helps):

                             List of schemas
         Name          |   Owner   |   Access privileges    | Description 
-----------------------+-----------+------------------------+-------------
 _timescaledb_cache    | timescale | timescale=UC/timescale+| 
                       |           | tight_user=UC/timescale    | 
 _timescaledb_catalog  | timescale | timescale=UC/timescale+| 
                       |           | =U/timescale          +| 
                       |           | tight_user=UC/timescale    | 
 _timescaledb_internal | timescale | timescale=UC/timescale+| 
                       |           | =U/timescale          +| 
                       |           | tight_user=UC/timescale    | 
 mals                  | timescale | timescale=UC/timescale+| 
                       |           | tight_user=UC/timescale    | 

____
                                                   Access privileges
 Schema |                 Name                 |   Type   |      Access privileges      | Column privileges | Policies 
--------+--------------------------------------+----------+-----------------------------+-------------------+----------
mals   | my_hypertable                         | table    | timescale=arwdDxt/timescale+|                   | 
       |                                       |          | tight_user=ar/timescale         |                  | 

[erimatnor]

@thaxy This does sound like a bug. Until we can investigate and fix this issue, it might help to know that you can actually specify the schema where you'd like chunks to be stored when creating the hypertable. Just pass associated_schema_name => 'chunk_schema' to create_hypertable(). This is not yet in the docs, but should work and allow you to use a schema that has the permissions you want.

[erimatnor]

@thaxy b.t.w., which version of TimescaleDB are you using?

[thaxy]

@erimatnor I am using timescale/timescaledb:0.9.1-pg10

[thaxy]

@erimatnor This is my database init script, running as superuser, shortened for brevity:

    CREATE USER tight_user WITH PASSWORD 'xxx';

    -- remove default schema
    DROP SCHEMA public;
    -- create new schema
    CREATE SCHEMA mals;
    -- set default schema for database
    ALTER DATABASE $POSTGRES_DB SET search_path TO mals;
    -- set schema for current session
    SET search_path TO mals;

    CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
    GRANT CONNECT ON DATABASE $POSTGRES_DB TO tight_user;
    GRANT USAGE ON SCHEMA mals TO tight_user;
    -- unsure here
    -- GRANT ALL PRIVILEGES ON SCHEMA _timescaledb_internal TO tight_user;
    -- GRANT ALL PRIVILEGES ON SCHEMA _timescaledb_catalog TO tight_user;
    -- GRANT ALL PRIVILEGES ON SCHEMA _timescaledb_cache TO tight_user;

    -- CREATE TABLE mals.my_hypertable(....
    SELECT create_hypertable('mals.my_hypertable', 'timestamp');
    GRANT SELECT, INSERT ON mals.my_hypertable TO tight_user;

[thaxy]

@erimatnor While being able to create a table in my schema the INSERT statement still gives the error ERROR: permission denied for relation _hyper_1_1_chunk even when the associated_schema_name is specified. After executing the INSERT with a superuser the chunk is created in the newly specified schema.

[msqr]

I just got hit by this issue as well. It only manifested when a new chunk needed to be created. I have inserts going into a hypertable by a role that is not the owner of the hypertable. Inserting into existing chunks works fine. Once TSDB tried to create a new chunk, however, the INSERT fails with this permission error.

I was able to work around by connecting to the database as the role that owns the database (not a superuser, just the role that owns this particular database) and inserting a single row into the table. That allowed TSDB to create the new chunk. Then inserts by the "normal" role started working again, as it started inserting rows into the newly created chunk.

I'm wondering: does the role doing the inserts need to have the CREATE privilege on the database, given it might need to create a new chunk at any time? For example, would this be required in this situation:

GRANT CREATE ON DATABASE mydatabase TO myinsertrole

I guess I hoped that would not be necessary, as this role doesn't really have any need for creating tables (other than chunk tables on demand).

[msqr]

After some testing, granting CREATE to the role did not seem to help. I also tried granting CREATE on the _timescaledb_internal schema but that did not help either. So right now it seems the only option is to use the role that owns the database?

[thaxy]

@msqr That's exactly what I experienced and tried to describe. For now, I am using a superuser account rather than a service account.

[erimatnor]

@thaxy I found an issue similar to what you describe when there is a trigger on the hypertable. Without triggers, everything works fine for me. Is it the case that you have at least one trigger on your hypertable?

[thaxy]

@erimatnor Uhmm, yes that's correct. Sorry for not mentioning that. I think I forgot it too. I have one trigger on my hypertable. The trigger inserts into another table. I tried to give my user GRANT ALL ON mals.other_table_where_trigger_inserts_to TO tight_user; but still running into ERROR: permission denied for relation _hyper_1_16_chunk when a chunk is empty/ non-existent.

[msqr]

@erimatnor I too have a trigger on the hypertable that exhibits this behavior, that inserts into another table.

[erimatnor]

This should be fixed by https://github.com/timescale/timescaledb/pull/516

[solugebefola]

@thaxy if the upgrade to 0.9.2 has fixed your problem, can you close this issue?

[thaxy]

@solugebefola yes, I will check it out in the next 1-2 days.

[thaxy]

Works for me. Thanks for your help.