Search Postgresql Archives

Fine grained permissions on User Mapping

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux