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?

score 23 · Answer 1 · answered Nov 30 '17 at 14:09

23

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

answered Nov 30 '17 at 14:09

Łukasz Kamiński

5,630
1
19
32

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. – Vikram Nov 30 '17 at 15:25
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. – Łukasz Kamiński Dec 01 '17 at 07:11

score 5 · Answer 2 · edited Dec 05 '18 at 08:44

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;

score 5 · Answer 3 · answered May 17 '19 at 10:18

5

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

answered May 17 '19 at 10:18

Logovskii Dmitrii

2,629
4
27
44

This does not work for me. server 10.12 (Ubuntu 10.12-0ubuntu0.18.04.1)) – gerardw May 13 '20 at 13:37
1

Wow, what a time saver – Hambone Feb 05 '21 at 18:42

Postgres: user mapping not found for "postgres"

3 Answers3