Bug report #19449
Strange behaviour when saving styles in Postgres
|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|
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
If I save a style in the database under user1 (db ), then it is stored in
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.
#2 Updated by Yaroslav Vasyunin almost 2 years ago
Jürgen Fischer wrote:
Have you verified the
search_pathof the users (
search_pathcould 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;