Postgres: user mapping not found for "postgres"

Question

I'm connected to schema apm.

Trying to execute a function and getting below error:

ERROR:  user mapping not found for "postgres"

Database connection info says:

apm on postgres@PostgreSQL 9.6

psql version: PostgreSQL 9.6.3, compiled by Visual C++ build 1800, 64-bit

How can this error be addressed?

Answer 1

It means that you are trying to use foreign table and your role (in this case postgres) does not have defined user and password for remote server.

You can add this by executing such query:

 CREATE USER MAPPING
    FOR postgres
 SERVER remote_server_name
OPTIONS (user 'bob', password 'secret');

You can get server name for table like that:

SELECT srvname
  FROM pg_foreign_table t
  JOIN pg_foreign_server s ON s.oid = t.ftserver
 WHERE ftrelid = 'schemaname.tablename'::regclass

Answer 2

If you want to create user mapping for all users, you can do it like this

CREATE USER MAPPING
FOR PUBLIC
SERVER remote_server_name
OPTIONS (user 'bob', password 'secret');

Answer 3

https://www.postgresql.org/docs/current/static/sql-createusermapping.html

CREATE USER MAPPING — define a new mapping of a user to a foreign server

Your function queries foreign tables, using some server, for which you need a user mapping. Apparently it exists for the user owner, and not for you. Or just run the function with a user that has user mapping created.

you can view them with:

SELECT um.*,rolname
  FROM pg_user_mapping um
  JOIN pg_roles r ON r.oid = umuser
  JOIN pg_foreign_server fs ON fs.oid = umserver;