Bug report #19449
Strange behaviour when saving styles in Postgres
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 tablesdata
- 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 about 6 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 about 6 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 about 6 years ago
Closing?
#4
Updated by Yaroslav Vasyunin about 6 years ago
Giovanni Manghi wrote:
Closing?
yes
#5
Updated by Giovanni Manghi about 6 years ago
- Resolution set to invalid
- Status changed from Feedback to Closed