Thread: Fine grained permissions on User Mapping
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";
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.
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.
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
Paul Bonaud <paul@bonaud.fr> writes:
> 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”.
> ...
> *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.
So you're doing it wrong at at least two levels here:
1. The remote user you're mapping to ought to have just the privileges
you want the local user to have w.r.t. that database. User IDs are
cheap in Postgres; don't be so miserly as not to create a separate one
for each privilege level you need. If you did that, you wouldn't really
care whether the user could also connect directly to the remote DB.
2. You don't want to grant USAGE on the foreign server to the local
user, either. It's possibly an error in the design of SQL/MED that
foreign server USAGE grants both the ability to create/delete foreign
tables and the ability to create/delete/inspect user mappings. But
that's how the committee did it, so we're stuck.
If it's really too painful to not let the local user create/delete his
own foreign tables, then what you could do is make sure the remote user
ID's password is useless for any purpose except connecting from the
source database. One way to do that is to adjust the remote DB's
pg_hba.conf to disallow the remote user ID from connecting from
anyplace except the local database server.
regards, tom lane
Hi Tom,
Thank you very much for your answer.
I was worried to get this kind of solution, i.e. “don't be so miserly as not to create a separate one for each privilege level you need.”, however in the case of a remote database *you have no control over* it sounds pretty impossible to do.
If I understand correctly, my initial question doesn't have a solution within postgres, does this sound right?
Thanks again !
Paul
On Tue, 2 Jun 2020 at 16:08, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Paul Bonaud <paul@bonaud.fr> writes:
> 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”.
> ...
> *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.
So you're doing it wrong at at least two levels here:
1. The remote user you're mapping to ought to have just the privileges
you want the local user to have w.r.t. that database. User IDs are
cheap in Postgres; don't be so miserly as not to create a separate one
for each privilege level you need. If you did that, you wouldn't really
care whether the user could also connect directly to the remote DB.
2. You don't want to grant USAGE on the foreign server to the local
user, either. It's possibly an error in the design of SQL/MED that
foreign server USAGE grants both the ability to create/delete foreign
tables and the ability to create/delete/inspect user mappings. But
that's how the committee did it, so we're stuck.
If it's really too painful to not let the local user create/delete his
own foreign tables, then what you could do is make sure the remote user
ID's password is useless for any purpose except connecting from the
source database. One way to do that is to adjust the remote DB's
pg_hba.conf to disallow the remote user ID from connecting from
anyplace except the local database server.
regards, tom lane
On 6/3/20 4:11 AM, Paul Bonaud wrote: > Hi Tom, > > Thank you very much for your answer. > > I was worried to get this kind of solution, i.e. “don't be so miserly as > not to create a separate one for each privilege level you need.”, > however in the case of a remote database **you have no control over**it > sounds pretty impossible to do. > > If I understand correctly, my initial question doesn't have a solution > within postgres, does this sound right? As it stands now I can't think of one. You might reach out to the postgres_fdw folks and see if they could get it to use a service file: https://www.postgresql.org/docs/12/libpq-pgservice.html Then the user mapping could use information the end user can't see unless they had permissions on the file system. > > Thanks again ! > Paul > ** -- Adrian Klaver adrian.klaver@aklaver.com
Hi again Tom,
I re-read your point 2. “You don't want to grant USAGE on the foreign server to the localuser, either.” to find out this was exactly the solution I was looking for. That is: it's fine to not let the basic user create the foreign tables.
Wow, it was as easy as moving the foreign table creations up to a higher “admin” level and giving only classical “select” grants to my local “basic” user. That's it, it works! When the basic user tries to list the existing user mappings of the database the “FDW options” column is now empty thus not revealing the remote server's username and password.
Thank you very much !
On Wed, 3 Jun 2020 at 22:22, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 6/3/20 4:11 AM, Paul Bonaud wrote:
> Hi Tom,
>
> Thank you very much for your answer.
>
> I was worried to get this kind of solution, i.e. “don't be so miserly as
> not to create a separate one for each privilege level you need.”,
> however in the case of a remote database **you have no control over**it
> sounds pretty impossible to do.
>
> If I understand correctly, my initial question doesn't have a solution
> within postgres, does this sound right?
As it stands now I can't think of one. You might reach out to the
postgres_fdw folks and see if they could get it to use a service file:
https://www.postgresql.org/docs/12/libpq-pgservice.html
Then the user mapping could use information the end user can't see
unless they had permissions on the file system.
>
> Thanks again !
> Paul
> **
--
Adrian Klaver
adrian.klaver@aklaver.com