Django model reference to a postgres foreign table (clickhouse_fdw)

Question

I have a Django project. I've connected a clickhouse table with a postgres table using clickhouse_fdw. Following clickhouse_fdw instructions, in postgres I did:

CREATE EXTENSION clickhousedb_fdw;
CREATE SERVER clickhouse_svr FOREIGN DATA WRAPPER clickhousedb_fdw OPTIONS(host 'host', dbname 'dbname', driver '/var/lib/postgresql/libclickhouseodbc.so');
CREATE USER MAPPING FOR CURRENT_USER SERVER clickhouse_svr;
CREATE FOREIGN TABLE clickhouse_table ("date_from" Date, "date_to" Date ... ) server clickhouse_svr options(table_name 'table');

This it's working using psql commands, but now I want to access from django (I can access all the others tables without any problems). I've added a new model for clickhouse table and ignored migrations and managment for it in django.

Then in django shell I've tried:

ClickHouseTable.objects.all()

ClickHouseTable.objects.raw('SELECT * FROM clickhouse_table LIMIT 1')[0]

from django.db import connection
with connection.cursor() as cursor:
    cursor.execute('SELECT * FROM clickhouse_table LIMIT 1')
    row = cursor.fetchone()

and with every query I get:

ProgrammingError: permission denied for foreign table clickhouse_table

Also from dbshell:

database => SELECT * FROM clickhouse_table LIMIT 1;
ERROR:  permission denied for foreign table clickhouse_table

Any suggestions?

score 0 · Answer 1 · answered Jul 12 '19 at 19:42

0

You Django app needs a full access to the DB. Also, Django provides you ORM. You might consider to use it.

answered Jul 12 '19 at 19:42

Rastko Sasic

36
3

Yes! Thats what I first did. I've also tried using django orm, something like ClickHouseTable.objects.all() ends with the same error. – Josefina Estevez Jul 15 '19 at 12:19
I have never used Django with Postgres. But this error comes from Postgres. It seems you didn't grant permissions to your Django app. Try to follow tutorials like this one: https://medium.com/agatha-codes/painless-postgresql-django-d4f03364989 – Rastko Sasic Jul 15 '19 at 14:02
But I can access to all tables using Django. The issue is with this foreign data wrapper. I also did GRANT USAGE ON FOREIGN SERVER myserver TO myuser; – Josefina Estevez Jul 15 '19 at 14:35
Have you tried this https://stackoverflow.com/questions/22483555/give-all-the-permissions-to-a-user-on-a-db – Rastko Sasic Jul 15 '19 at 18:18
Cool, I am pretty sure that your app for some reason was't granted all permissions... – Rastko Sasic Jul 15 '19 at 19:22

score 0 · Answer 2 · answered Jul 15 '19 at 19:18

0

I've started from scratch and now it's working. No idea what was going on :/

answered Jul 15 '19 at 19:18

Josefina Estevez

139
1
9

Django model reference to a postgres foreign table (clickhouse_fdw)

2 Answers2