postgis不适用于所有postgres用户 [英] postgis not available for all postgres users

问题描述

在启用了Potgis的数据库上创建新用户时，遇到一个奇怪的问题，即新用户无法访问postgis扩展，而较早创建的用户可以.

When creating a new user on our Potgis enabled database I run into the strange issue that that new user cannot access the postgis extension while earlier created users can.

使用我的用户帐户，我得到以下输出:

With my user account I get the following output:

mydb => SELECT postgis_version();
postgis_version
---------------------------------------
 2.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
(1 row)

有了新用户，我得到以下信息:

With the new user I get the following:

mydb => SELECT postgis_version()
mydb-> ;
ERROR:  function postgis_version() does not exist
LINE 1: SELECT postgis_version()
           ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

QGIS还给我提示postgis不活跃:

Also QGIS is giving me hint that postgis is not active:

2018-01-23T16:38:13 1   No PostGIS support in the database.

我正在连接到完全相同的数据库. 用户确实有权访问公共架构和geometry_columns表.

I am connecting to the exact same database. The user does have access to the public schema and to the geometry_columns table.

我在这里有点迷茫，因为根据我的信息，Postgis是数据库级别的扩展，应该对所有用户都存在.

I am a bit lost here since according to my info Postgis is an extansion on the database level and it should be there for all users.

推荐答案

PostGIS扩展必须安装在也必须位于用户搜索路径中的架构中.

The PostGIS extension must be installed in a schema that must also be in the user search path.

您可以使用命令检查其安装位置

You can check where it is installed with the command

select e.extname,n.* 
from pg_extension e, pg_namespace n 
where e.extnamespace = n.oid and e.extname='postgis';

您可以通过发出

show search_path;

如果没有，则可以通过更改用户来永久添加路径.

If not, you can permanently add the path by altering the user.

ALTER USER username SET search_path TO "$user", public, postgis_schema;

由于上一条命令仅在下次登录时生效，因此您可以通过应用

As the previous command takes effect at the next login only, you can apply it immediately by applying

SET search_path TO "$user", public, postgis_schema;

这篇关于postgis不适用于所有postgres用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！