Hi there,
@iames reported about permission issues at Problem with PowerBI Desktop and permissions, which apparently have been resolved with CrateDB 4.6.1. Thank you!
Now, there seems to be a different issue with timestamps which can be discussed within this topic. Thanks again for reporting this, @iames!
With kind regards,
Andreas.
Today I have updated the server to 4.6.1 version (from 4.5.3) and tested again PowerBI connection. Now it connects correctly using PostgreSQL datasource using DirectQuery with non privileged user, but the problem now are the timestamps, the all show as âErrorâ, autogenerated ones and inserted ones.
This problem happens with all timestamp columns on all tables, for example I have a table definition like this:
CREATE TABLE IF NOT EXISTS "south_mill"."processed_data" (
"var_id" BIGINT,
"var_date" TIMESTAMP WITHOUT TIME ZONE,
"var_value" DOUBLE PRECISION,
"var_creation_date" TIMESTAMP WITHOUT TIME ZONE GENERATED ALWAYS AS _cast(current_timestamp, 'timestamp without time zone'),
"var_year" INTEGER GENERATED ALWAYS AS EXTRACT(YEAR FROM "var_date"),
PRIMARY KEY ("var_id", "var_date", "var_year")
)
CLUSTERED INTO 4 SHARDS
PARTITIONED BY ("var_year")
Column var_date is inserted and column var_creation_date is automatically created on insertion. On PowerBI the result is
On a more simpler table (is not partitioned or part of the key) the error is different
Any ideas of what is happening? When I initially posted this topic I tried a nightly 4.6.0 build of Crate and I donât remember this problem.
We will look into this.
Could you maybe share the queries run by PowerBI
e.g. by running
SELECT * FROM sys.jobs_log ORDER BY ended DESC
I have tried to capture all PowerBI queries while adding the Crate datasource. They are quite long, I have not found a way to attach a file.
select COLUMN_NAME, ORDINAL_POSITION, IS_NULLABLE, case when (data_type like '%unsigned%') then DATA_TYPE || ' unsigned' else DATA_TYPE end as DATA_TYPE
from INFORMATION_SCHEMA.columns
where TABLE_SCHEMA = 'south_mill' and TABLE_NAME = 'processed_data'
order by TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION
select
pkcol.COLUMN_NAME as PK_COLUMN_NAME,
fkcol.TABLE_SCHEMA AS FK_TABLE_SCHEMA,
fkcol.TABLE_NAME AS FK_TABLE_NAME,
fkcol.COLUMN_NAME as FK_COLUMN_NAME,
fkcol.ORDINAL_POSITION as ORDINAL,
fkcon.CONSTRAINT_SCHEMA || '_' || fkcol.TABLE_NAME || '_' || 'var_names' || '_' || fkcon.CONSTRAINT_NAME as FK_NAME
from
(select distinct constraint_catalog, constraint_schema, unique_constraint_schema, constraint_name, unique_constraint_name
from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS) fkcon
inner join
INFORMATION_SCHEMA.KEY_COLUMN_USAGE fkcol
on fkcon.CONSTRAINT_SCHEMA = fkcol.CONSTRAINT_SCHEMA
and fkcon.CONSTRAINT_NAME = fkcol.CONSTRAINT_NAME
inner join
INFORMATION_SCHEMA.KEY_COLUMN_USAGE pkcol
on fkcon.UNIQUE_CONSTRAINT_SCHEMA = pkcol.CONSTRAINT_SCHEMA
and fkcon.UNIQUE_CONSTRAINT_NAME = pkcol.CONSTRAINT_NAME
where pkcol.TABLE_SCHEMA = 'south_mill' and pkcol.TABLE_NAME = 'var_names'
and pkcol.ORDINAL_POSITION = fkcol.ORDINAL_POSITION
order by FK_NAME, fkcol.ORDINAL_POSITION
select
pkcol.TABLE_SCHEMA AS PK_TABLE_SCHEMA,
pkcol.TABLE_NAME AS PK_TABLE_NAME,
pkcol.COLUMN_NAME as PK_COLUMN_NAME,
fkcol.COLUMN_NAME as FK_COLUMN_NAME,
fkcol.ORDINAL_POSITION as ORDINAL,
fkcon.CONSTRAINT_SCHEMA || '_' || 'var_names' || '_' || pkcol.TABLE_NAME || '_' || fkcon.CONSTRAINT_NAME as FK_NAME
from
(select distinct constraint_catalog, constraint_schema, unique_constraint_schema, constraint_name, unique_constraint_name
from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS) fkcon
inner join
INFORMATION_SCHEMA.KEY_COLUMN_USAGE fkcol
on fkcon.CONSTRAINT_SCHEMA = fkcol.CONSTRAINT_SCHEMA
and fkcon.CONSTRAINT_NAME = fkcol.CONSTRAINT_NAME
inner join
INFORMATION_SCHEMA.KEY_COLUMN_USAGE pkcol
on fkcon.UNIQUE_CONSTRAINT_SCHEMA = pkcol.CONSTRAINT_SCHEMA
and fkcon.UNIQUE_CONSTRAINT_NAME = pkcol.CONSTRAINT_NAME
where fkcol.TABLE_SCHEMA = 'south_mill' and fkcol.TABLE_NAME = 'var_names'
and pkcol.ORDINAL_POSITION = fkcol.ORDINAL_POSITION
order by FK_NAME, fkcol.ORDINAL_POSITION
select i.CONSTRAINT_SCHEMA || '_' || i.CONSTRAINT_NAME as INDEX_NAME, ii.COLUMN_NAME, ii.ORDINAL_POSITION, case when i.CONSTRAINT_TYPE = 'PRIMARY KEY' then 'Y' else 'N' end as PRIMARY_KEY
from INFORMATION_SCHEMA.table_constraints i inner join INFORMATION_SCHEMA.key_column_usage ii on i.CONSTRAINT_SCHEMA = ii.CONSTRAINT_SCHEMA and i.CONSTRAINT_NAME = ii.CONSTRAINT_NAME and i.TABLE_SCHEMA = ii.TABLE_SCHEMA and i.TABLE_NAME = ii.TABLE_NAME
where i.TABLE_SCHEMA = 'south_mill' and i.TABLE_NAME = 'var_names'
and i.CONSTRAINT_TYPE in ('PRIMARY KEY', 'UNIQUE')
order by i.CONSTRAINT_SCHEMA || '_' || i.CONSTRAINT_NAME, ii.TABLE_SCHEMA, ii.TABLE_NAME, ii.ORDINAL_POSITION
select
pkcol.COLUMN_NAME as PK_COLUMN_NAME,
fkcol.TABLE_SCHEMA AS FK_TABLE_SCHEMA,
fkcol.TABLE_NAME AS FK_TABLE_NAME,
fkcol.COLUMN_NAME as FK_COLUMN_NAME,
fkcol.ORDINAL_POSITION as ORDINAL,
fkcon.CONSTRAINT_SCHEMA || '_' || fkcol.TABLE_NAME || '_' || 'processed_data' || '_' || fkcon.CONSTRAINT_NAME as FK_NAME
from
(select distinct constraint_catalog, constraint_schema, unique_constraint_schema, constraint_name, unique_constraint_name
from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS) fkcon
inner join
INFORMATION_SCHEMA.KEY_COLUMN_USAGE fkcol
on fkcon.CONSTRAINT_SCHEMA = fkcol.CONSTRAINT_SCHEMA
and fkcon.CONSTRAINT_NAME = fkcol.CONSTRAINT_NAME
inner join
INFORMATION_SCHEMA.KEY_COLUMN_USAGE pkcol
on fkcon.UNIQUE_CONSTRAINT_SCHEMA = pkcol.CONSTRAINT_SCHEMA
and fkcon.UNIQUE_CONSTRAINT_NAME = pkcol.CONSTRAINT_NAME
where pkcol.TABLE_SCHEMA = 'south_mill' and pkcol.TABLE_NAME = 'processed_data'
and pkcol.ORDINAL_POSITION = fkcol.ORDINAL_POSITION
order by FK_NAME, fkcol.ORDINAL_POSITION
select
pkcol.TABLE_SCHEMA AS PK_TABLE_SCHEMA,
pkcol.TABLE_NAME AS PK_TABLE_NAME,
pkcol.COLUMN_NAME as PK_COLUMN_NAME,
fkcol.COLUMN_NAME as FK_COLUMN_NAME,
fkcol.ORDINAL_POSITION as ORDINAL,
fkcon.CONSTRAINT_SCHEMA || '_' || 'processed_data' || '_' || pkcol.TABLE_NAME || '_' || fkcon.CONSTRAINT_NAME as FK_NAME
from
(select distinct constraint_catalog, constraint_schema, unique_constraint_schema, constraint_name, unique_constraint_name
from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS) fkcon
inner join
INFORMATION_SCHEMA.KEY_COLUMN_USAGE fkcol
on fkcon.CONSTRAINT_SCHEMA = fkcol.CONSTRAINT_SCHEMA
and fkcon.CONSTRAINT_NAME = fkcol.CONSTRAINT_NAME
inner join
INFORMATION_SCHEMA.KEY_COLUMN_USAGE pkcol
on fkcon.UNIQUE_CONSTRAINT_SCHEMA = pkcol.CONSTRAINT_SCHEMA
and fkcon.UNIQUE_CONSTRAINT_NAME = pkcol.CONSTRAINT_NAME
where fkcol.TABLE_SCHEMA = 'south_mill' and fkcol.TABLE_NAME = 'processed_data'
and pkcol.ORDINAL_POSITION = fkcol.ORDINAL_POSITION
order by FK_NAME, fkcol.ORDINAL_POSITION
select i.CONSTRAINT_SCHEMA || '_' || i.CONSTRAINT_NAME as INDEX_NAME, ii.COLUMN_NAME, ii.ORDINAL_POSITION, case when i.CONSTRAINT_TYPE = 'PRIMARY KEY' then 'Y' else 'N' end as PRIMARY_KEY
from INFORMATION_SCHEMA.table_constraints i inner join INFORMATION_SCHEMA.key_column_usage ii on i.CONSTRAINT_SCHEMA = ii.CONSTRAINT_SCHEMA and i.CONSTRAINT_NAME = ii.CONSTRAINT_NAME and i.TABLE_SCHEMA = ii.TABLE_SCHEMA and i.TABLE_NAME = ii.TABLE_NAME
where i.TABLE_SCHEMA = 'south_mill' and i.TABLE_NAME = 'processed_data'
and i.CONSTRAINT_TYPE in ('PRIMARY KEY', 'UNIQUE')
order by i.CONSTRAINT_SCHEMA || '_' || i.CONSTRAINT_NAME, ii.TABLE_SCHEMA, ii.TABLE_NAME, ii.ORDINAL_POSITION
select
pkcol.COLUMN_NAME as PK_COLUMN_NAME,
fkcol.TABLE_SCHEMA AS FK_TABLE_SCHEMA,
fkcol.TABLE_NAME AS FK_TABLE_NAME,
fkcol.COLUMN_NAME as FK_COLUMN_NAME,
fkcol.ORDINAL_POSITION as ORDINAL,
fkcon.CONSTRAINT_SCHEMA || '_' || fkcol.TABLE_NAME || '_' || 'processed_data' || '_' || fkcon.CONSTRAINT_NAME as FK_NAME
from
(select distinct constraint_catalog, constraint_schema, unique_constraint_schema, constraint_name, unique_constraint_name
from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS) fkcon
inner join
INFORMATION_SCHEMA.KEY_COLUMN_USAGE fkcol
on fkcon.CONSTRAINT_SCHEMA = fkcol.CONSTRAINT_SCHEMA
and fkcon.CONSTRAINT_NAME = fkcol.CONSTRAINT_NAME
inner join
INFORMATION_SCHEMA.KEY_COLUMN_USAGE pkcol
on fkcon.UNIQUE_CONSTRAINT_SCHEMA = pkcol.CONSTRAINT_SCHEMA
and fkcon.UNIQUE_CONSTRAINT_NAME = pkcol.CONSTRAINT_NAME
where pkcol.TABLE_SCHEMA = 'south_mill' and pkcol.TABLE_NAME = 'processed_data'
and pkcol.ORDINAL_POSITION = fkcol.ORDINAL_POSITION
order by FK_NAME, fkcol.ORDINAL_POSITION
select
pkcol.TABLE_SCHEMA AS PK_TABLE_SCHEMA,
pkcol.TABLE_NAME AS PK_TABLE_NAME,
pkcol.COLUMN_NAME as PK_COLUMN_NAME,
fkcol.COLUMN_NAME as FK_COLUMN_NAME,
fkcol.ORDINAL_POSITION as ORDINAL,
fkcon.CONSTRAINT_SCHEMA || '_' || 'processed_data' || '_' || pkcol.TABLE_NAME || '_' || fkcon.CONSTRAINT_NAME as FK_NAME
from
(select distinct constraint_catalog, constraint_schema, unique_constraint_schema, constraint_name, unique_constraint_name
from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS) fkcon
inner join
INFORMATION_SCHEMA.KEY_COLUMN_USAGE fkcol
on fkcon.CONSTRAINT_SCHEMA = fkcol.CONSTRAINT_SCHEMA
and fkcon.CONSTRAINT_NAME = fkcol.CONSTRAINT_NAME
inner join
INFORMATION_SCHEMA.KEY_COLUMN_USAGE pkcol
on fkcon.UNIQUE_CONSTRAINT_SCHEMA = pkcol.CONSTRAINT_SCHEMA
and fkcon.UNIQUE_CONSTRAINT_NAME = pkcol.CONSTRAINT_NAME
where fkcol.TABLE_SCHEMA = 'south_mill' and fkcol.TABLE_NAME = 'processed_data'
and pkcol.ORDINAL_POSITION = fkcol.ORDINAL_POSITION
order by FK_NAME, fkcol.ORDINAL_POSITION
select i.CONSTRAINT_SCHEMA || '_' || i.CONSTRAINT_NAME as INDEX_NAME, ii.COLUMN_NAME, ii.ORDINAL_POSITION, case when i.CONSTRAINT_TYPE = 'PRIMARY KEY' then 'Y' else 'N' end as PRIMARY_KEY
from INFORMATION_SCHEMA.table_constraints i inner join INFORMATION_SCHEMA.key_column_usage ii on i.CONSTRAINT_SCHEMA = ii.CONSTRAINT_SCHEMA and i.CONSTRAINT_NAME = ii.CONSTRAINT_NAME and i.TABLE_SCHEMA = ii.TABLE_SCHEMA and i.TABLE_NAME = ii.TABLE_NAME
where i.TABLE_SCHEMA = 'south_mill' and i.TABLE_NAME = 'processed_data'
and i.CONSTRAINT_TYPE in ('PRIMARY KEY', 'UNIQUE')
order by i.CONSTRAINT_SCHEMA || '_' || i.CONSTRAINT_NAME, ii.TABLE_SCHEMA, ii.TABLE_NAME, ii.ORDINAL_POSITION
Hi @iames,
I just authorized the file extensions .log and .txt to be able to be uploaded to posts. Just drag and drop a corresponding file into the text area and Discourse will automatically upload it.
With kind regards,
Andreas.
Hi @iames,
the problem appears to be the timestamp without time zone data type that is not working properly with Power BI. Columns with the data type timestamp with time zone however do work.
We will look further into Npgsql for more insights and get back to you here.
In the meantime, a quick workaround could be to cast affected columns to timestamp with time zone, e.g. as a view:
CREATE VIEW south_mill.processed_data_powerbi AS
SELECT var_id, var_date::timestamp with time zone AS var_date, var_value, var_creation_date::timestamp with time zone AS var_creation_date, var_year::timestamp with time zone AS var_year
FROM south_mill.processed_data;
Best
Niklas
Hi @iames,
Power BI ships with a two-year-old Npgsql version (4.0.10) as per its documentation.
The timestamp without time zone issue is reproducible on Npgsql < 5.0.0, independent from Power BI. From version 5.0.0 on, the issue doesnât occur anymore. Unfortunately, there seems to be no possibility to manually update the bundled Npgsql version due to different .NET version compatibilities.
Until Microsoft ships Power BI with a more recent Npgsql version, Iâm afraid one of the workarounds need to be used:
timestamp with time zone as per the example above using a view or writing a manual query directly in Power BI.Best
Niklas
I have tried the view solution, but unfortunately it does not work. In PowerBI y get the following error:
This is what I have done:
First create the the views using the following SQL sentences:
CREATE VIEW bemp_demo.processed_data_powerbi AS
SELECT var_id, var_date::timestamp with time zone AS var_date, var_value, var_creation_date::timestamp with time zone AS var_creation_date, var_year
FROM bemp_demo.processed_data;
CREATE VIEW bemp_demo.model_config_powerbi AS
SELECT meter_id as id, meter_name as name, description, target_id, var_id_array, last_data_ts::timestamp with time zone AS last_data_ts, model_status
FROM "bemp_demo"."meter_config";
CREATE VIEW bemp_demo.prediction_data_powerbi AS
SELECT meter_id as model_id, var_date::timestamp with time zone AS var_date, var_value, var_creation_date::timestamp with time zone AS var_creation_date, var_year
FROM "bemp_demo"."prediction_data";
CREATE VIEW bemp_demo.raw_data_powerbi AS
SELECT var_id, var_date::timestamp with time zone AS var_date, var_value, var_value_text, var_creation_date::timestamp with time zone AS var_creation_date, var_year
FROM "bemp_demo"."raw_data";
CREATE VIEW bemp_demo.var_names_powerbi AS
SELECT var_id as id, var_name, var_description, var_creation_date::timestamp with time zone AS var_creation_date, isnumeric, unit, var_type, var_unit
FROM "bemp_demo"."var_names";
Then I have added GRANT DQL to user on all new views:
GRANT DQL ON TABLE bemp_demo.processed_data_powerbi to bemp_demo;
GRANT DQL ON TABLE bemp_demo.model_config_powerbi to bemp_demo;
GRANT DQL ON TABLE bemp_demo.prediction_data_powerbi to bemp_demo;
GRANT DQL ON TABLE bemp_demo.raw_data_powerbi to bemp_demo;
GRANT DQL ON TABLE bemp_demo.var_names_powerbi to bemp_demo;
I attach a txt file with all queries from PowerBI, but the one seem to fail is:
select "$Table"."var_id" as "var_id",
"$Table"."var_date" as "var_date",
"$Table"."var_value" as "var_value",
"$Table"."var_creation_date" as "var_creation_date",
"$Table"."var_year" as "var_year"
from "bemp_demo"."processed_data_powerbi" "$Table"
limit 1000
The error shown in Crate is: Schema âbemp_demoâ unknown. If I run the query in a console as user crate it seems to work (I get results), but using bemp_demo user I get: SchemaUnknownException[Schema âbemp_demoâ unknown]
UPDATE: After writing this all I have tried to GRANT DQL on schema to user bemp_demo and now it seems to work. Is thre any way to not grant DQL on complete schema?
powerbi_crate_queries_2.txt (3.9 KB)
Hi @iames,
can you please try granting the DQL permission with ON VIEW instead of ON TABLE?
GRANT DQL ON VIEW bemp_demo.processed_data_powerbi to bemp_demo;
The documentation doesnât mention that VIEW can be used, Iâll get that fixed.
If it still doesnât work then, what are the exact permissions that are currently in place for that user? (SELECT * FROM sys.privileges WHERE grantee = 'beep_demo')
Best
Niklas
Thank you @hammerhead , for the moment I have created all views on a different schema and granted permissions on it. I will try using GRANT DQL ON VIEW.
The truth is that for our needs using views is a better solutions than directly giving access to the table, thank you for the idea and the help.
Best regards,
Be aware, that views have an owner
In order to be able to query data from a view, a user needs to have
DQLprivileges on a view. DQL privileges can be granted on a cluster level, on the schema in which the view is contained, or the view itself. Privileges on relations accessed by the view are not necessary.However, it is required, at all times, that the owner (the user who created the view), has
DQLprivileges on all relations occurring within the viewâs query definition.
also see https://crate.io/docs/crate/reference/en/4.6/general/ddl/views.html
So if I have created the views using the crate user the queries are run using the crate user? What security implications does it have? The user bemp_demo has DQL permissions on views (all schema in this case). Can bemp_demo user somehow write data to the final tables or access data crate user has?
So if I have created the views using the crate user the queries are run using the crate user?
Kinda, the user, that created the view has to have the rights to execute the query in the view.
What security implications does it have?
It allows to restrict access to specific (sub)-sets of tables. What implications are you referring to?
The user bemp_demo has DQL permissions on views (all schema in this case). Can bemp_demo user somehow write data to the final tables or access data crate user has?
No, not at all. The result set is always restricted to what the view is set up to be.
I only want to add I have tried GRANT DQL ON VIEW as mentioned by @hammerhead and it works perfectly.