12

I am trying to set privileges in my database for users so they can only UPDATE, INSERT, DELETE and, of course, SELECT on geographic tables.

Since I am using some SERIAL types for my IDs, I would prefer to not even give the users the possibility to manually edit this field. So I am defining column-wise privileges. It works fine on the UPDATE part, but the button in QGIS allowing the creation of a new object stays greyed. The only way it seems to be working is when I do not specify any field in the privileges definition. Even when I choose all the fields it does not work (even though I would have thought that not specifying any column and specifying all of them would be the same).

It seems to be something that I don't understand, or there is a limitation in the privilege definition for proper interaction between the DB and QGIS. Does anyone have any piece of information or advice which could help me understand what is happening, and/or (even better) help me achieve my goal?

I can always deal with that by setting the field as non-editable in the style definition, but since anyone can set it as they please, I would prefer a more secure alternative.

Running QGIS 2.14, PostGIS 2.3 for PostgreSQL 9.5.

  • Do you use GRANT SQL command to set user rights (privileges)? Did you GRANT INSERT on your table? – Zoltan Jun 22 '17 at 16:02
  • @Zoltan Yes, my query looks like GRANT INSERT (col2, col3, col4) ON table TO users – GuiOm Clair Jun 22 '17 at 17:07
  • 5
    The user who should have INSERT rights must have full rights to the primary key column. Otherwise she will not be able to do the insert. I don't think there's any way around that. You can only limit the visibility of that column in QGIS, as you already mentioned. – Micha Jun 24 '17 at 14:14
  • 1
    @Micha Ok then, indeed it makes total sense said like that... I guess I also have another workaround by a column-specific trigger ON INSERT DO NOTHING and ON UPDATE DO NOTHING for the primary key, which would prevent any manual edit of the primary key. Thanks. – GuiOm Clair Jun 26 '17 at 6:19
  • 3
    Or you create a view of your table excluding the SERIAL column and only give access to that view. – JoeBe Oct 3 '17 at 0:36
0

The user who should have INSERT rights must have full rights to the primary key column. Otherwise she will not be able to do the insert. I don't think there's any way around that. You can only limit the visibility of that column in QGIS, as you already mentioned.

Regarding your comment: column-specific trigger ON INSERT DO NOTHING and ON UPDATE DO NOTHING for the primary key I'm not sure that would work. When inserting a new row, a new primary key must be created, obviously. You don't want to circumvent that.

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.