0

I have uploaded a geojson file to a fresh Postgis installation with ogr2ogr following this example here.

I than created a new psql user for my colleague without any special rights but afterwards I granted him access rights to the db and all rights to all tables in public following the first answer in this question.

If I connect to the db in QGIS with the postgis connector and his credentials, I can see the table, however there is an error message telling me that I have to specify the data-type and the SRID. If I do so (data-type is multipolygon and SRID is 4326) I can import the table, however no single feature is shown.

If I connect to the db with my own user credentials that have all rights (SUPERUSER) I can import the table without any error, not even having to specify datatype and SRID. So the data is definitely okay.

I guess the problem is that granting all rights to all tables in public is not enough and somehow I also need to grant the rights to use the postgis extension which is in schema postgis or something like that. Can anybody give me a hint?

0

1 Answer 1

-1

Okay, The following edits to schema in postgis resolved the issue:

-- Schema: postgis

-- DROP SCHEMA postgis;

CREATE SCHEMA postgis
  AUTHORIZATION postgres;

GRANT ALL ON SCHEMA postgis TO public;
GRANT ALL ON SCHEMA postgis TO pg_signal_backend;
REVOKE ALL ON SCHEMA postgis FROM postgres;
3
  • This is not an appropriate way to manage a database. Best practice calls for "least privilege". Granting ALL to public is an antipattern.
    – Vince
    Mar 16, 2018 at 11:58
  • how about posting an adequate answer then?
    – joaoal
    Mar 16, 2018 at 12:40
  • There are too many unquantified actions in the question to write an answer. The best solution would be to start over, then create a login role and schema for the data owner (no data should ever be loaded as postgres), load the data as that user, then create a role for read access, grant that role SELECT on the loaded table, then create a login role for the second user, and grant them the access role.
    – Vince
    Mar 16, 2018 at 13:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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