Hello, I couldn't find any answer in the PostgreSQL documentation so here I am with a question regarding FDW and User Mappings. *Is it possible to define permissions on user mappings to hide the connection info (mainly the password) to a user?*
More details of the context: ---- Imagine you have a destination database which you have no control over. Let's call it “external-db”. This database has a unique pg user (no specific pg permission attributes) with read-write access to the whole database let's call it “external-user”. Now over to our own database which we have control over. Imagine we want to use a pg foreign data wrapper to access tables from the “external-db” from a basic (non superuser) user, let's call it “basic-user”. -- Setup as a superuser -- Setup FDW CREATE EXTENSION postgres_fdw; -- Create foreign server CREATE SERVER "external-db" FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '127.0.0.1', dbname 'external-db', port '5434'); CREATE USER MAPPING FOR "basic-user" SERVER "external-db" OPTIONS (user 'external-user', password 'external_user_super_secret_password'); GRANT USAGE ON FOREIGN SERVER "external-db" TO "basic-user"; If we connect now with the “basic-user” we can create foreign tables to access the “external-db” which is great. The issue: ---- *However*, we would like to avoid our “basic-user” to have full control over the external-db. We would like this basic user to only be able to *read* the external database. With this current setup the user can very simply list the user mappings with details (\deu+ in psql) to collect the username/password combination and thus directly connect to the initial “external-db” with full access. Does PostgreSQL offer some kind of permissions over the USER MAPPING options so it can be *used* by a pg user but not *seen*? Is there any other solution for this kind of requirement? Many thanks for reading and any help will be very appreciated :), Paul
