Create shared user mapping in PostgreSQL foreign data wrapper server

We are using a foreign data wrapper to query across databases on a single PostgreSQL RDS. The foreign data wrapper server needs a user mapping for each user who will query against the remote server. However, adding the user mapping for each user may prove to be error prone.

All of our users who need to query against the foreign data server have a shared role, e.g. role_name, on our PostgreSQL server.

How can we share the foreign data wrapper server user mapping between users?

The only way to share a user mapping is if you define it for PUBLIC, but then it applies to all users. Groups don't work in that context, probably because it would cause ambiguity if several user mappings apply to a single user through inheritance.

If you don't want to go with a user mapping per user, you could opt to create one for PUBLIC but limit access to the foreign tables to role_name through permissions.

I know that this is not a great solution either, because it will allow all users to see the user mapping and the password in there (if there is one). But at least it is easy to GRANT SELECT ON ALL TABLES IN SCHEMA ... TO role_name.

StackExchange Database Administrators Q#281797, answer score: 5