CODEWITHSUNDEEP
postgresqlpostgis
AntonAL
AntonAL

Reputation: 17430

ERROR: permission denied for function geography_eq

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.

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)

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
        ));
      $$

No indexes on public.user.geolocation column are presented.

SELECT
    tablename,
    indexname,
    indexdef
FROM
    pg_indexes
WHERE
    schemaname = 'public' AND tablename = 'user'
ORDER BY
    tablename,
    indexname;
tablename indexname indexdef
user user_nickname_key CREATE UNIQUE INDEX user_nickname_key ON public."user" USING btree (nickname)
user user_pkey CREATE UNIQUE INDEX user_pkey ON public."user" USING btree (id)

Is there some implicit usage of geography_eq function somewhere?

Update:

Default privileges are:

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

Upvotes: 0

Views: 1146

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247665

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.

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

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

Upvotes: 0

Related Questions