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?

Tyson

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

Millikan

I'm trying to run below command: postgres=# create user mapping for postgres server localhost; ERROR: server "localhost" does not exist How can I find the server name? Can I skip the options as I work in my PC? The query you provided to get the server name didn't return any records. – Tyson

Did you change schemaname.tablename to the object identifier of table that you are attempting to access? Those names in my answer are just examples. If you did and found nothing, then try select * from pg_foreign_server and you likely have only one foreign server there. As for skipping it, you can't if you want to access that table from that server. It is connecting to remote host/server after all and requires login and password for that. – Posehn

Answer 2

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;

Stereotyped

Answer 3

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

Cottonweed

This does not work for me. server 10.12 (Ubuntu 10.12-0ubuntu0.18.04.1)) – Repeated

Wow, what a time saver – Helle