Unable to connect to PostgreSQL database using schemas

Question

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.

Answer 1

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)

Answer 2

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!