2

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?

4
  • is the person allowed to see the personal data or is your aim to hide this information completly?
    – LaughU
    Aug 6 '17 at 19:11
  • I want to hide it completely to that person
    – JoeBe
    Aug 6 '17 at 19:54
  • Just use updatable views and assign proper privileges: depesz.com/2012/12/11/…
    – mmd
    Aug 6 '17 at 20:54
  • My view would not meet all criteria to create an updatable view
    – JoeBe
    Aug 7 '17 at 0:01
2

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/

1
  • I thought this grant select is not possible, because therefore views exist to limit the viewable content to the user. Perhaps I understood that wrongly
    – JoeBe
    Aug 6 '17 at 18:47
1

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).

enter image description here

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.

before and after the hidding

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.