Re: Fine grained permissions on User Mapping

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Paul Bonaud <paul(at)bonaud(dot)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
>