Postgres: user mapping not found for "postgres"

sql postgresql mapping postgres-fdw
Vikram picture Vikram · Nov 30, 2017 · Viewed 17.2k times · Source

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

Łukasz Kamiński picture Łukasz Kamiński · Nov 30, 2017

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

Related questions

Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How do I (or can I) SELECT DISTINCT on multiple columns?

I need to retrieve all rows from a table where 2 columns combined are all different. So I want all the sales that do not have any other sales that happened on the same day for the same price. The sales …

Read More
How to select the nth row in a SQL database table?

I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases: SQL Server …

Read More