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.
My prefered solution here is to create the foreign table, then create a view on that foreign table, and grant select rights on the view to whoever needs them. Only the owner of the table needs a mapping, the view users will inherit the usage of the mapping (without being able to see the details of the mapping, in case you want that to be secret, although in the past there were bugs which exposed this, so don't use ancient minor releases).
