2

So I am trying to edit data saved on a local instance of PostGIS within QGIS. However,whenever I try to save my edits I get the error message:

enter image description here

I am capable of modifying the data how I want in QGIS, it just will not let me save the edits. I have included the permission settings I have in PostGIS and associated the SQL code. Let me know what I am doing incorrectly. I have had the same issue with all of the other mock users that I created. I am also currently using PGAdmin4.

enter image description here enter image description here enter image description here enter image description here

3 Answers 3

3

As I understand you tried to not just edit but create new object (insert new row). So, you have not such privileges on table primary key sequence. In your example it's "Test_Line_Layer_id_seq". Whith PgAdmin4 open properties of this sequence and set privileges you need: enter image description here

In this case USAGE privilege is enough

2
  • Maybe it is because of the version I am running but usage does not seem to be an option I can select. I added a screen shot of this to the original post.
    – Ian Moffit
    Jan 12, 2023 at 16:11
  • Your screenshot shows properties of the TABLE "Test_Line_Layer". But you need properties of the SEQUENCE "Test_Line_Layer_seq". In PgAdmin object tree you find it in the Sequencies node.
    – RainForest
    Jan 13, 2023 at 4:44
0

So interestingly I figured out the problem and it has to do with the default value for the ID field of the new line features. When I create a new line feature the ID field is auto populated as such:

enter image description here

But when I change the ID value to a single number like 3 it does work:

enter image description here

While I did manage to solve the problem I would like to know why this happens if possible. Thank you so much everyone for all your help!

1
  • Yes! You tried to do what sequencies have to do! Sequence automaticaly increments internal variable every time you add new row to the table and gives this value to the id field. You set id value manualy, This way you already broke sequence algorithm. Now just truncate the table or change internal sequence variable (ALTER SEQUENCE "Test_Line_Layer_seq" RESTART WITH 4).
    – RainForest
    Jan 13, 2023 at 5:03
0

If you don't care about fine tuning the right, this should cover everything you need:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA schema_name TO your_pg_role; -- on tables
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA schema_name TO your_pg_role; -- on sequences
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA schema_name TO your_pg_role; -- on functions

Or shortly:

GRANT ALL ON ALL TABLES IN SCHEMA schema_name TO your_pg_role; -- on tables
GRANT ALL ON ALL SEQUENCES IN SCHEMA schema_name TO your_pg_role; -- on sequences
GRANT ALL ON ALL FUNCTIONS IN SCHEMA schema_name TO your_pg_role; -- on functions

Feel free to adapt of course, as you can give specific rights such as for SELECT:

GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO your_pg_role;

More details can be found in the documentation: https://www.postgresql.org/docs/current/sql-grant.html

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.