ERROR: user mapping not found for "gpadmin"

Products

• VMware Tanzu Data Suite
• VMware Tanzu Greenplum
• Greenplum
• VMware Tanzu Data Suite

Issue/Introduction

The following query reports an error when foreign tables exist in the database:

gpadmin=# select * from information_schema.tables;
ERROR:  user mapping not found for "gpadmin"

 

Environment

Greenplum 7.x and 6.x

Cause

There is a bug in the code for function `pg_relation_is_updatable(regclass, boolean)` that causes the error when it is run on a foreign tables that have the foreign data wrapper(FDW) 'greenplum_fdw' or 'postgres_fdw'.

The function requires a user mapping for the current user (i.e. the caller of `pg_relation_is_updatable()`) to the "foreign server" used in the foreign table.

 

Resolution

Workaround

This workaround will find all foreign tables that use foreign data wrapper(FDW) "greenplum_fdw" or "postgres_fdw". It will then create the required user mapping for the server.

1. List all the foreign tables using the FDW:

select c.relname, r.rolname, s.srvname, srvowner, w.fdwname, um.*
from pg_foreign_table f
join pg_class c on f.ftrelid = c.oid
join pg_roles r on c.relowner = r.oid
join pg_foreign_server s on f.ftserver = s.oid
join pg_foreign_data_wrapper w on s.srvfdw = w.oid
left join pg_user_mapping um on s.oid = um.umserver
where fdwname in ('greenplum_fdw', 'postgres_fdw');

The output will be like:

   relname    | rolname |    srvname     | srvowner |    fdwname    | umuser | umserver | umoptions 
--------------+---------+----------------+----------+---------------+--------+----------+-----------
 psql_table01 | gpadmin | pg01_postgres  |       10 | postgres_fdw  |        |          | 
 gpdb_table01 | gpadmin | pg01_greenplum |       10 | greenplum_fdw |        |          | 
(2 rows)

If the "umuser" column is blank or not "0", then you will need to create a user mapping

2. Create a user mapping for "public" on the servers listed in the "srvname" column:

CREATE USER MAPPING FOR PUBLIC SERVER <srvname>;

For example:

CREATE USER MAPPING FOR PUBLiC SERVER pg01_postgres;
CREATE USER MAPPING FOR PUBLiC SERVER pg01_greenplum;

3. Verify everything is fixed with:

SELECT * FROM information_schema.tables;

The query should no longer report an error.

Fix

Fix is being developed and will be released in Greenplum versions 6.29.x and 7.5.