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?

joaoal
  • 891
  • 7
  • 14

1 Answers1

-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;
joaoal
  • 891
  • 7
  • 14
  • 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 '18 at 11:58
  • how about posting an adequate answer then? – joaoal Mar 16 '18 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 '18 at 13:06