Hiding PostGIS Schema in QGIS?

Question

This is my situation:

I have a table A in schema A which includes personal data. I want to give specific persons the permission to see some data from that table in QGIS, excluding that personal data. Therefore I have a view B of table A in Schema B which doesn't include the columns of the personal data. The problem is that I want the person to edit two columns in that view. So I created a trigger for this view and gave permission to the person to update these two columns in it. However, I also need to give him permission to the table A. My problem is now that the person is able to access table A in Schema A as he needs the permissions to update the two columns. Because that person doesn't know anything about the structure of the database, this might be fine as he would not know that there is a table behind that view. However, when connecting to the database in QGIS he can also see schema A and is able to load the table A.

Perhaps there is a way to limit the acces to table A in the backend. But for now it would be enough when the schema A could be hidden in QGIS. Is that possible? Or does anyone has a better idea for a solution?

Answer 1

I think there's no need to work with views or triggers since postgres has the option to do column level priviliges. It would be something in the line of: grant select ( id, name, geom ) on tablename to username;

Here is a simple blogpost about it: https://www.depesz.com/2009/01/31/waiting-for-84-column-level-privileges/

Answer 2

I can think of two possible solutions for this problem:

1. Create a new table in schema B without the personal info columns and feed this table back to the main table with an update statement. You could add some sort of identifier to match the rows.

2. Hide the columns inside QGIS with a standard style. So every time your user loads the table, he is presented with the same style. In this style you can hide certain columns under the properties and field tab you can choose the edit widget to be "hidden" (see screenshots).

After setting the style, you save it to your database as a default( propertioes and then style on the lower left side. However be aware, that the user could change the styling and thus unhide the columns and edit them. So this is not 100% save.