Bug report #19449

Strange behaviour when saving styles in Postgres

Added by Yaroslav Vasyunin over 2 years ago. Updated over 2 years ago.

Status:Closed
Priority:Normal
Assignee:-
Category:Symbology
Affected QGIS version:3.2 Regression?:No
Operating System:Mac OS and Windows Easy fix?:No
Pull Request or Patch supplied:No Resolution:invalid
Crashes QGIS or corrupts data:No Copied to github as #:27277

Description

My QGIS projects loads vector layers from PostgreSQL. I have three users in my database:

  • postgres - admin of the whole database cluster (default user)
  • user1 - owner of one particular database "test".
  • user2 - it has limited access to the database "test"

I have two relevant schemas there:

  • public - for storing system and maintenance tables
  • data - where all working data is collected

All users have access to all objects (including functions and sequences) in these two schemas.

If I save a style in the database under postgres or user2, then it is stored in public.layer_styles
If I save a style in the database under user1 (db owner), then it is stored in data.layer_styles

So if I open the QGIS project with user1, then I can't neither read styles saved in public.layer_styles nor add new styles there. I double checked permissions for styles tables. All users are granted with ALL permissions to them.

History

#1 Updated by Jürgen Fischer over 2 years ago

  • Status changed from Open to Feedback

Have you verified the search_path of the users (search_path could be set by user: ALTER ROLE user1 SET search_path…)?

#2 Updated by Yaroslav Vasyunin over 2 years ago

Jürgen Fischer wrote:

Have you verified the search_path of the users (search_path could be set by user: ALTER ROLE user1 SET search_path…)?

Yes - that is the route of my problem! Thank you for the tip. For some reason user1 had schema data in the search_path, while other users doesn't have it.

So it turns out it is not a bug, but an obscure behaviour. It would be nice to have a possibility to choose in which schema to write and read the layer_styles table... The following query helped me to see all custom search paths for all users and databases:

SELECT r.rolname, d.datname, rs.setconfig
FROM pg_db_role_setting rs
LEFT JOIN pg_roles r ON r.oid = rs.setrole
LEFT JOIN pg_database d ON d.oid = rs.setdatabase;

#3 Updated by Giovanni Manghi over 2 years ago

Closing?

#4 Updated by Yaroslav Vasyunin over 2 years ago

Giovanni Manghi wrote:

Closing?

yes

#5 Updated by Giovanni Manghi over 2 years ago

  • Resolution set to invalid
  • Status changed from Feedback to Closed

Also available in: Atom PDF