0

I am trying to create a shared server for community, and now stock=k with users privilege and roles, I get an error on QGIS when login with my user account.

Steps I did :

  1. I revoked all privileges from public schema.

    REVOKE ALL ON DATABASE sudanmap FROM PUBLIC; REVOKE ALL ON SCHEMA public FROM PUBLIC; REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC; REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA public FROM PUBLIC;

  2. I created role named userread only to view as demo.

    CREATE ROLE userread;

  3. Granted Connection to database, usage to public schema and Grant Select .

    GRANT CONNECT ON DATABASE sudanmap TO userread; Grant USAGE ON SCHEMA public TO userread; GRANT SELECT ON ALL TABLES IN SCHEMA Public TO userread;

  4. Created user with password, and grant userread to it.

    CREATE USER open PASSWORD '123456789'; GRANT userread TO open;

  5. Login to QGIS is successful. I can see my schema but I get this (failed to get layer).

What I try I grant all to my user still the same, only why he can see the layers by making him superuser.

What am doing wrong ?

Edit: All Sequences grant to user, but still the same problem enter image description here

Ahmed Ismail
  • 121
  • 7
  • Can you give more details on your point 5 ? Do you see your tables ? When do you get the error message ? And I don't really understand the last sentence : if you connect as superuser, that works ? – Cupain May 17 '20 at 10:46
  • QGIS requires the PostGIS extension. I don't see that mentioned in the question. – Mapperz May 17 '20 at 17:17
  • PostGIS setup https://gis.stackexchange.com/questions/232945/setting-up-postgis-database-and-opening-it-in-qgis-on-windows – Mapperz May 17 '20 at 17:18
  • Thanks all for replying , I already import the layer , and also add Postgis Extension, layers working fine with my Superuser , But the problem only when using this user I create , so like I can't make Users With Different Privileges . Only Super User can See all layers – Ahmed Ismail May 17 '20 at 18:00
  • You didn’t grant select on all sequences in public schema to that user. – kartoza-geek May 18 '20 at 00:40
  • I Grant Sequences for the user but still same... – Ahmed Ismail May 19 '20 at 06:29
  • 1
    It needs access to the views in the public schema as well, that's where all the table metadata is stored. Why mess with the public schema in the first place? It's the public schema for a reason, it needs to be accessible to users. Just revoke write access. – HeikkiVesanto May 19 '20 at 08:53

0 Answers0