3

I am using QGIS 2.0 with PostgreSQL 10 and PostGIS 2.4.

I have multiple users who need to access their vector data from the database. All the users have the same vector layers on their project, but need to create their own features and store them individually on the database, with the user being able to access only his/ her own tables.

I created a database user_markings and imported all the relevant vector layers into it using DB Manager. I cloned the public schema of this database using a script into multiple schemas, one for each user. I granted all privileges for a user to his/ her schema, and assigned the user login a path to the schema.

When I attempt to connect to the database as one of the users, I get the following error: Erronous query: SELECT postgis_version() returned 7[ERROR: function postgis_version() does not exist].

I am able to login as a Superuser. I have double checked the postgis and postgis_topology extensions in my database. Also as I read in one of the posts, I have assigned a path to postgis in the database properties.

5
  • Can you post an example of the code you used to assign path for each user? Look like this might come from here as test user don't "see" the postgis functions.
    – MarHoff
    Feb 15, 2018 at 16:20
  • The code I used for assigning paths was like this: ALTER ROLE fiona SET search_path = 'finance'; ALTER ROLE sally SET search_path = 'sales'; Feb 15, 2018 at 16:35
  • Can't reproduce yet. can you again specify what do you mean by "Also as I read in one of the posts, I have assigned a path to postgis in the database properties." Your problem clearly is some kind of search_path issue.
    – MarHoff
    Feb 15, 2018 at 17:03
  • I followed the instructions on this post: gis.stackexchange.com/questions/159094/… Feb 15, 2018 at 17:18
  • The code used was : ALTER DATABASE name_of _database SET search_path = public, postgis; Feb 15, 2018 at 17:22

2 Answers 2

5

You have installed postgis in a specific schema instead of public which need adequate search_path management.

It look like this

ALTER ROLE fiona SET search_path = finance;

totaly override the database settings that you made earlier

ALTER DATABASE name_of _database SET search_path = public, postgis;

I've looked for a cleverer answer (and i might have missed it) but you best shot is to repeat the appropriate schema for all users with a custom search_path like that:

ALTER ROLE fiona SET search_path = public, postgis, finance;

That should do the job, but if your user only need access to schema identical to their username it's even easier because you can use the following trick:

ALTER DATABASE your_db SET search_path = "$user", postgis, public;

But remember to do this for each user to undo your previous fiddling ALTER USER username RESET search_path;

Please try around a little until it work ;)


Important update!

Please note that you'll have to take special care for version of postgis extension >= 2.3 as they will be set to non-relocatable by default. This mean you won't be able to alter extension schema after creation. And so :

ALTER EXTENSION postgis set schema postgis; --will fail :(

So if you know you want to install postgis extension in a specific schema, best practice would be to use this command at creation time:

CREATE EXTENSION postgis SCHEMA whereever_I_damn_want_you_to_be;

In your case whereever_I_damn_want_you_to_be would be postgis

For existing installation that need to be moved this official workaraound was posted
https://postgis.net/2017/11/07/tip-move-postgis-schema/

(And this was also helpful for thoses wanting to dig-in more: https://trac.osgeo.org/postgis/ticket/3496)

15
  • I tried what you have suggested. But by adding public and postgis in the path by ALTER ROLE, the connection is established to the public schema. The finance schema is not listed. I tried by using only postgis and finance on the path, but that gives me the same error as earlier. Feb 16, 2018 at 7:57
  • Something is messy. Have you tried to connect directly to your instance using psql or another GUI tool than qgis to clarify where things are actually installed? Also if there is similarly named object in different namespace of your path, Postgres will pick the first it found. So command should be SET search_path = finance, postgis, public
    – MarHoff
    Feb 16, 2018 at 8:14
  • PS: but the fact that public seems needed make me wonder if you actually installed postgis in a different schema... you can test by calling schema qualified function SELECT public.postgis_version()ˋ and ˋSELECT postgis.postgis_version
    – MarHoff
    Feb 16, 2018 at 8:17
  • Yes. I am using both psql and pgAdmin. All the databases, schemas and users seem to be in place. Feb 16, 2018 at 8:20
  • Also stumble onto this article that might be helpful postgresonline.com/journal/archives/… So stupid question, did you remember to close and start a new connection between each testing?
    – MarHoff
    Feb 16, 2018 at 8:23
1

I finally got it working. As suggested by MarHoff, I created a fresh database. I added the postgis and postgis_topology extensions to the public schema. Then I created a schema called postgis, and added it to the search path of the database.

CREATE SCHEMA postgis;

GRANT ALL ON SCHEMA postgis to public;

ALTER DATABASE [database_name] set search_path = "$user", public, postgis;

Then I moved the extension postgis to the postgis schema.

ALTER EXTENSION postgis set schema postgis;

Now when I login as user b8, I get connected to the schema b8 on the database.

Thanks MarHoff, wouldn't have been possible without your help!

1
  • Thanks! I just updated my answer to include a remark about behavior of postgis version >= 2.3 that might throw and error for this command you used ALTER EXTENSION postgis set schema postgis; ;)
    – MarHoff
    Feb 23, 2018 at 9:57

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.