Paul Bonaud <paul@xxxxxxxxx> 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
