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
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 |