Cannot ingest data using ILP and RBAC

Question

I am using QuestDB Enterprise 2.1.1 and setting up permissions. When I use the built-in admin user, I can ingest data programmatically using ILP, for example with this snippet from the docs

conf = f'http::addr=localhost:9000;username=admin;password=quest;auto_flush_rows=100;auto_flush_interval=1000;'
        with Sender.from_conf(conf) as sender:           
            sender.row(
                'trades',
                symbols={
                    'symbol': 'ETH-USD',
                    'side': 'sell'},
                columns={
                    'price': 2615.54,
                    'amount': 0.00044,
                   },
                at=datetime.datetime(
                        2022, 3, 8, 18, 53, 57, 609765,
                        tzinfo=datetime.timezone.utc))

I have created a user:

create user ilp_ingest WITH PASSWORD ilp123;
grant ILP to ilp_ingest;
grant PGWIRE to ilp_ingest;

GRANT CREATE TABLE  to ilp_ingest;
GRANT INSERT  ON ilp_test to ilp_ingest;
grant select on all tables to ilp_ingest;

But when I try ingesting data with the ilp_ingest user I get access denied. This happens both if the table is created beforehand or if I start sending data to enable table auto-creation.

What am I missing?

Answer 1

In QuestDB you can ingest data with the ILP protocol in two ways, with the TCP transport or with the HTTP transport. The TCP transport is generally not recommended and it is available mostly for historical reasons. The snippet is using the recommended HTTP transport.

The unauthorized problem happens because QuestDB needs

GRANT HTTP to user;

You actually don't need to grant the ILP grant unless you are planning to use the TCP transport.

Also, rather than using user and password as the credentials, it is recommended to generate an HTTP token, as in

ALTER USER ilp_ingest CREATE TOKEN TYPE REST WITH TTL ‘10d’ REFRESH;

Using the token is lighter on the server side, which can make a different in throughput when ingesting high volumes of data.

Then we can just replace user and password on the connection string with token as in

conf = f'http::addr=localhost:9000;token=XY132213wXXXXzzzzzzzzz;auto_flush_rows=100;auto_flush_interval=1000;'