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.

underdark
  • 84,148
  • 21
  • 231
  • 413
Ganesh Nagarajan
  • 145
  • 6
  • 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 '18 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'; – Ganesh Nagarajan Feb 15 '18 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 '18 at 17:03
  • I followed the instructions on this post: https://gis.stackexchange.com/questions/159094/with-schema-function-postgis-full-version-does-not-exist – Ganesh Nagarajan Feb 15 '18 at 17:18
  • The code used was : ALTER DATABASE name_of _database SET search_path = public, postgis; – Ganesh Nagarajan Feb 15 '18 at 17:22

2 Answers2

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)

MarHoff
  • 1,250
  • 6
  • 14
  • 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. – Ganesh Nagarajan Feb 16 '18 at 07: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 '18 at 08: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 '18 at 08:17
  • Yes. I am using both psql and pgAdmin. All the databases, schemas and users seem to be in place. – Ganesh Nagarajan Feb 16 '18 at 08:20
  • Also stumble onto this article that might be helpful http://www.postgresonline.com/journal/archives/279-Schema-and-search_path-surprises.html So stupid question, did you remember to close and start a new connection between each testing? – MarHoff Feb 16 '18 at 08:23
  • I have given the user name to the schema too. For instance, the user b12 needs to connect to schema b12. I hope naming them similarly is not creating any issues. – Ganesh Nagarajan Feb 16 '18 at 08:28
  • The article you have linked is in great detail. I’ll take some time to digest it, will revert after that. – Ganesh Nagarajan Feb 16 '18 at 08:31
  • Also, I read that part about closing and starting a new connection somewhere else as well. How do I do that if I am trying to establish the connection from QGIS? – Ganesh Nagarajan Feb 16 '18 at 08:35
  • Actually if so the answer is even more simple as of https://dba.stackexchange.com/questions/56023/what-is-the-search-path-for-a-given-database-and-user – MarHoff Feb 16 '18 at 08:56
  • I resumed my troubleshooting after the weekend, but still no breakthrough. I read both the documents that you have suggested. I reset the search_path and then reassigned the path following the hierarchy given in the document, also tried using “$user” in the path as the role name and schema names are the same. I have ensured that postgis is included in the path. Despite all this I continue to get the same error message as before. When I add public to the path, I am able to connect, but only to the public schema, my other schemas are not listed. – Ganesh Nagarajan Feb 20 '18 at 05:22
  • I checked whether postgis extension is installed in the b13 schema by running the function SELECT b13.postgis_version(); This gives me the error message function b13.postgis_version() does not exist. Since the error in QGIS is also the same, can the issue be resolved by adding the postgis_version() function to the b13 schema? If yes, how do I copy the function from public schema to b13 schema? – Ganesh Nagarajan Feb 20 '18 at 05:38
  • You should never install more than one instance of postgis into a database unless you want your application to unexpectly broke at some point in the future. Postgis is a huge collection of objects that must lie in the same schema and must never be moved manually. You define at extension creation in which schema it live and that's it. Your problem definitively come from a bad configuration, I'd really suggest that you don't try to fix it by adding other potential bug. – MarHoff Feb 20 '18 at 07:53
  • Take time to read postgresql documentation about schemas, extension and postgis doc about how to achieved a correct installation. This will take time but it will be a valuable knowledge. And this is the only way for you to understand what did you specifically misconfigured. If possible the best option would be to start from scratch on a test server by installing things step by step in order to identify which step fall short. – MarHoff Feb 20 '18 at 08:02
  • You are right. I’ll try a fresh configuration. Thanks for all the help. I’ll revert if a fresh attempt also runs into trouble! – Ganesh Nagarajan Feb 20 '18 at 08:04
  • Also feel free to upvote. I did’nt solved by I took time to try ;) – MarHoff Feb 20 '18 at 08:06
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!

Ganesh Nagarajan
  • 145
  • 6
  • 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 '18 at 09:57