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 <t...@sss.pgh.pa.us> wrote: > Paul Bonaud <p...@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 >
