4

I've created a two new PostgreSQL users; one that is supposed to have read-only privileges and another one that should have most privileges (admin-user). When I connect/log in to the new users in QGIS I can't see or access the tables though.

Since there is no difference in what I can access no matter which of these users I connect to, I probably missed out on something fundamental here. When connecting in QGIS with default PostgreSQL user (superuser) I get access to all tables. Here is my grants for the two users I created:

User_1

CREATE USER user_1 WITH PASSWORD 'xxxxx';

GRANT CONNECT ON DATABASE mydb TO user_1;
GRANT USAGE ON SCHEMA my_schema TO user_1;
GRANT SELECT ON ALL TABLES IN SCHEMA my_schema TO user_1;
GRANT SELECT, USAGE ON ALL SEQUENCES IN SCHEMA my_schema TO user_1;
ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema GRANT SELECT ON TABLES TO user_1;
ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema GRANT SELECT, USAGE ON SEQUENCES TO user_1;

User_2

CREATE USER user_2 WITH CREATEDB CREATEROLE CREATEUSER PASSWORD 'xxxxxx';

GRANT CONNECT ON DATABASE mydb TO user_2;
GRANT USAGE ON SCHEMA my_schema TO user_2;
GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA my_schema TO user_2;
GRANT ALL ON ALL SEQUENCES IN SCHEMA my_schema TO user_2;
ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES TO user_2;
ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema GRANT ALL ON SEQUENCES TO user_2;
ALTER USER user_2 WITH NOSUPERUSER;

I've read the documentation for PostgreSQL about grants but can't seem to find anything missing.

Any ideas why this doesn't result in any access to tables in QGIS for my new users?

Improve this question

1 Answer 1

Reset to default
0

I solved this by granting usage to all schemas in the db before granting any privileges to tables. Grant a user priveleges to a specific schema and it's tables doesn't seem to work for accsessing data i qgis.

Improve this answer
3
  • 1
    glad you got it working. i've had similar issues with multiple schemas and visibility in other apps, (qgis, etc.) Adding the custom schema to the search path has helped. "ALTER DATABASE my_db SET search_path = my_schema, public;"
    – jamierob
    Nov 8, 2018 at 15:57
  • 1
    I think you were missing the permissions for the schema that has the postgis extension enabled in (by default, it ends up in the public schema). I don't think you need to grant usage for all the schemas.
    – SaultDon
    Nov 8, 2018 at 15:58
  • I agree my solution is not the best answer to this. I will try with the public schema first thing next time. Thanks for your tips!
    – Spiran
    Nov 8, 2018 at 20:02

Your Answer

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

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