How to prevent the use of "import" connection mode for specific tables of a (Postgresql) dataset ?

[MNMstatistics]

Hello you all, 

I'm struggling to find a fully secure method to connect Power BI Destkop to a Postgresql dataset containing sensitive (health) data. The dataset is stored on a remote HIPAA-compliant datacenter and I'm using the ODBC plugin to reach it. I chose to use a composite connection mode to connect to the dataset : I connect to the tables that contain identifying and/or sensitive data using Direct Query mode. For performance issues, I use the "Import" mode to connect to other tables. This way, I prevent sensitive data from being stored on my computer.

My issue is that it remains technically possible to connect to all tables using the "import" mode. This is not satisfying because if any sensitive data can be stored on my PC, it means that to some extent the database is exposed. 

In order to get a fully secure set-up, I'd like to set a technical barrier preventing to use the "import" mode for the tables that contain sensitive data.

I guess there is a way to do this using access permissions/role managements on Postgresql, but I don't know how to do it.

Any clues ? 

Thanks in advance for your kind help!

[mwegener]

Hi @MNMstatistics ,

I assume that there is no possibility here.

Power BI sends a SQL query to the database regardless of whether the result is saved in import mode or only displayed as a direct query result.

Therefore, postgresql has no information about what the data is being requested for.

[MNMstatistics]

Cheers for your answer mwegener! 

Too bad ... I assumed there was a difference between the queries sent through "import" and "direct query" modes I could use to define table-specific restrictions to the data connection mode used.

For now I'm letting the post opened hoping another idea pops up!

By the way, is there a way to read the SQL queries Power BI sends to the postgres database?

Good day and thanks heaps!

[mwegener · Accepted solution]

Hi @MNMstatistics ,

you can log the queries in Postgres...

http://www.microhowto.info/howto/log_all_queries_to_a_postgresql_server.html

... or with the Query Diagnostics in Power BI

https://docs.microsoft.com/en-us/power-query/querydiagnostics

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.