Create Postgres FDW using the postgres super user yet have read only access to the remote schema

Question

I am trying to create an FDW using the postgres super user credentials using the following steps -

1. My super user is postgres
2. CREATE SERVER testfdw FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '10.10.10.171', dbname 'testdb', port '5432', fetch_size '50000');
3. I have created a read only remote user called testdb_read_only_user on testdb and granted read only privileges to all tables and sequences and functions in there
4. I now create a user mapping for my local db super user postgres
5. CREATE USER MAPPING FOR postgres SERVER testfdw OPTIONS (user 'testdb_read_only_user', password 'testpasswrd')
6. Then I run IMPORT FOREIGN SCHEMA public FROM SERVER testfdw INTO test_local_schema;
7. Now when I try to create a new foreign table into test_local_schema using the postrges super user, I am still able to create one, but the actual behaviour I expect is a permission denied error and my user mapping is based on the read only user I created on the Remote DB

Am I missing something here. Please guide.

Answer 1

The foreign schema test_local_schema is owned by postgres(Superuser) and PostgreSQL treating it as a local schema(on target) gives you option to create a new table, Now when you are creating a new foreign table in test_local_schema postgres will check below constraints

1. The name of the foreign table must be distinct from the name of any other foreign table, table, sequence, index, view, or materialized view in the same schema.
2. To create a foreign table, you must have USAGE(read) privilege on the foreign server, as well as USAGE privilege on all column types used in the table
3. CREATE privilege on the target schema.

As your user mapping has read-only user in source database you should be able to fetch data in order to create a foreign table in target database, however whenever you will update and insert new data it will give you error as testdb_read_only_user is not having any update/insert privileges.

https://www.postgresql.org/docs/9.5/sql-createforeigntable.html https://www.postgresql.org/docs/9.5/sql-importforeignschema.html