错误:功能 geography_eq 的权限被拒绝 [英] ERROR: permission denied for function geography_eq

问题描述

最近我将我的 PostgreSQL 11 数据库的 Docker 容器从 postgres 换成了 postgis/postgis:11-2.5-alpine，添加了列 geolocation public.geography(Point,4326) 到 public.user 表，就是这样.

Recently I've swapped Docker container for my PostgreSQL 11 database from postgres to postgis/postgis:11-2.5-alpine, added column geolocation public.geography(Point,4326) to public.user table and that's it.

目前没有使用 PostGIS 中的任何内容.

Nothing from PostGIS is being used so far.

在我的应用程序的某些时候，我收到以下错误:

In some point of my application, I'm getting following error:

ERROR:  permission denied for function geography_eq
CONTEXT:  SQL function "user_private_message_peers" statement 1
STATEMENT:  select to_json((__local_0__."id")) as "id", to_json((with __local_1__ as (select to_json((json_build_object('id'::text, (__local_2__."id"), 'nickname'::text, (__local_2__."nickname")))) as "@nodes"
db_1    |   from "public"."user_private_message_peers"(__local_0__) as __local_2__
db_1    |   
db_1    |   where (TRUE) and (TRUE)
db_1    |   
db_1    |   
db_1    |   ), __local_3__ as (select json_agg(to_json(__local_1__)) as data from __local_1__) select json_build_object('data'::text, coalesce((select __local_3__.data from __local_3__), '[]'::json)) )) as "@privateMessagePeers"
db_1    |   from "public"."current_user"() as __local_0__
db_1    |   
db_1    |   where (not (__local_0__ is null)) and (TRUE) and (TRUE)

迁移到 PostGIS 后，函数 user_private_message_peers 没有改变:

A function user_private_message_peers has not changed after migrating to PostGIS:

CREATE FUNCTION public.user_private_message_peers("user" public."user") RETURNS SETOF public."user"
    LANGUAGE sql STABLE STRICT
    AS $$
        SELECT
          distinct u.*
        FROM
          "user" AS u
        INNER JOIN
          "privateMessage" pm ON (
            pm."recipientId" = u.id OR pm."senderId" = u.id
          )
        WHERE (
          u.id != "user".id
        ) AND ((
          pm."senderId" = 1 AND pm."recipientId" != 1
        ) OR (
          pm."senderId" != 1 AND pm."recipientId" = 1
        ));
      $$

public.user.geolocation 列没有索引.

SELECT
    tablename,
    indexname,
    indexdef
FROM
    pg_indexes
WHERE
    schemaname = 'public' AND tablename = 'user'
ORDER BY
    tablename,
    indexname;

<头>

tablename	索引名	indexdef
用户	user_nickname_key	CREATE UNIQUE INDEX user_nickname_key ON public.user"使用 btree(昵称)
用户	user_pkey	CREATE UNIQUE INDEX user_pkey ON public.user"使用 btree (id)

是否在某处隐式使用了 geography_eq 函数?

Is there some implicit usage of geography_eq function somewhere?

更新:

默认权限为:

\ddp
Default access privileges
-[ RECORD 1 ]-----+--------------------
Owner             | postgres
Schema            | 
Type              | function
Access privileges | postgres=X/postgres

推荐答案

该错误意味着为 geography 类型实现相等运算符的函数已损坏权限.不知何故，你的 PostGIS 安装搞砸了.

That error means that the function that implements the equality operator for the geography type has broken permissions. Somehow, your PostGIS installation got messed up.

最好的解决方法是删除并重新创建 postgis 扩展.

The best fix would be to drop and recreate the postgis extension.

检查 psql 是否定义了任何默认权限:\ddp

Check with psql if you have any default privileges defined: \ddp

这篇关于错误:功能 geography_eq 的权限被拒绝的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！