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.

Improve this question
8
  • Do you use GRANT SQL command to set user rights (privileges)? Did you GRANT INSERT on your table?
    – Zoltan
    Commented Jun 22, 2017 at 16:02
  • @Zoltan Yes, my query looks like GRANT INSERT (col2, col3, col4) ON table TO users
    – GuiOm Clair
    Commented Jun 22, 2017 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
    Commented Jun 24, 2017 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
    Commented Jun 26, 2017 at 6:19
  • 3
    Or you create a view of your table excluding the SERIAL column and only give access to that view.
    – JoeBe
    Commented Oct 3, 2017 at 0:36

1 Answer 1

Reset to default
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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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