Fine grained permissions on User Mapping

From: Paul Bonaud <paul(at)bonaud(dot)fr>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Fine grained permissions on User Mapping
Date: 2020-06-02 10:01:36
Message-ID: CAE8rFSv7GwNRG4rdATuQdmaBOd+DMSFJtRHMcZi8wWGk9eAjVg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Nico De Ranter 2020-06-02 11:23:32 Re: pg_dump crashes
Previous Message Grigory Smolkin 2020-06-02 09:59:31 Re: Oracle vs. PostgreSQL - a comment