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
