With PostgreSQL's postgres_fdw, which objects do you create to query another server's tables, and which object holds the host and which the password?
answer
- server, mapping, then tables
- connection options versus credentials
- import a whole remote schema
- USAGE on the foreign server
- non-superusers must send a password
basics
~20 sAfter CREATE EXTENSION postgres_fdw, a foreign server holds host, port and dbname; a user mapping holds each local role's remote user and password; foreign tables, usually made by IMPORT FOREIGN SCHEMA, name the remote tables.
solid answer
~40 sThe chain is four objects. `CREATE EXTENSION postgres_fdw` installs the wrapper. `CREATE SERVER billing_srv FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host ..., dbname ...)` takes libpq connection options except `user`, `password` and `sslpassword`. `CREATE USER MAPPING FOR <role> SERVER billing_srv OPTIONS (user ..., password ...)` supplies credentials per local role, or for `PUBLIC`. Then `IMPORT FOREIGN SCHEMA billing LIMIT TO (...) FROM SERVER billing_srv INTO billing_remote` creates matching foreign tables. Creating foreign tables needs `USAGE` on the server; importing also needs `CREATE` on the target schema. A non-superuser's connection must authenticate with a password or delegated GSSAPI credentials, and only a superuser can set `password_required 'false'`. In PostgreSQL 18, `use_scram_passthrough` reuses the client's SCRAM login instead of a stored password.
code
sql · 17 linesCREATE EXTENSION IF NOT EXISTS postgres_fdw;
CREATE SERVER billing_srv
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'billing-db.internal', port '5432', dbname 'billing');
CREATE USER MAPPING FOR finance_reporting
SERVER billing_srv
OPTIONS (user 'finance_ro', password 'change-me');
GRANT USAGE ON FOREIGN SERVER billing_srv TO finance_reporting;
CREATE SCHEMA billing_remote;
IMPORT FOREIGN SCHEMA billing
LIMIT TO (invoices, payments, customers)
FROM SERVER billing_srv
INTO billing_remote;go deeper
Recall the order: extension, foreign server, user mapping, foreign tables, and that host and dbname go on the server while user and password go on the mapping.
Explain why credentials live per role on the mapping, what USAGE on the server grants, and what IMPORT FOREIGN SCHEMA copies and skips.
Show the security judgement: why non-superusers must really authenticate, why password_required false is dangerous, and how 18's SCRAM pass-through removes stored passwords.
Decide how cross-team database links are owned: one read-only remote role per consumer, who rotates its secret, and when a link should become a replicated copy instead.
## The four objects The finance reporting database needs to read the billing team's tables on a separate PostgreSQL server. `postgres_fdw` gets there through four catalog objects, created in order: 1. **The extension.** `CREATE EXTENSION postgres_fdw` registers the wrapper in this database. Installing extensions in general is a topic of its own; here it is one line. 2. **The foreign server.** `CREATE SERVER billing_srv FOREIGN DATA WRAPPER postgres_fdw OPTIONS (...)` describes where the remote database is. It accepts the same keywords as a libpq connection string. 3. **The user mapping.** `CREATE USER MAPPING FOR finance_reporting SERVER billing_srv OPTIONS (user ..., password ...)` says which remote role a local role becomes. A mapping `FOR PUBLIC` applies to any local role that has no mapping of its own. 4. **The foreign tables.** Either one at a time with `CREATE FOREIGN TABLE`, or in bulk with `IMPORT FOREIGN SCHEMA`, which reads the remote catalog and writes matching definitions. ## Where each option belongs | Option | Foreign server | User mapping | Foreign table | |---|---|---|---| | `host`, `port`, `dbname`, `sslmode` | yes | no | no | | `user`, `password`, `sslpassword` | **not allowed** | yes | no | | `sslcert`, `sslkey` | yes | yes, and it wins (superuser only) | no | | `schema_name`, `table_name` | no | no | yes | | `fetch_size`, `use_remote_estimate`, `async_capable` | yes | no | yes, and it wins | | `extensions`, `keep_connections`, `parallel_commit` | yes | no | no | The split exists so one server definition can serve many local roles, each with its own remote identity and privileges. Columns are matched to the remote table **by name, not position**, and `column_name` can rename one. ## Privileges and who can see the password - **`USAGE` on the foreign server** is needed to create foreign tables against it (`GRANT USAGE ON FOREIGN SERVER billing_srv TO ...`). `IMPORT FOREIGN SCHEMA` also needs `CREATE` on the local target schema, which must already exist. - **Who can create mappings:** the server owner can create a mapping for any role; a role holding `USAGE` can create one for itself. - **Password visibility:** in the `pg_user_mappings` view, `umoptions` reads as null unless you are the mapped user with ownership of or `USAGE` on the server, the server owner looking at a `PUBLIC` mapping, or a superuser. - **Non-superusers must really authenticate.** If a non-superuser's connection would not use a password or delegated GSSAPI credentials, postgres_fdw stops with `password or GSSAPI delegated credentials required`, for example when the remote `pg_hba.conf` says `trust`. Otherwise the remote login would borrow the operating-system identity of the local server process, its `.pgpass` file and its client certificates. - **`password_required 'false'`** on a mapping lifts that check. Only a superuser can set it, and the manual warns never to set it on the `PUBLIC` mapping. ## IMPORT FOREIGN SCHEMA in detail `IMPORT FOREIGN SCHEMA billing LIMIT TO (invoices, payments) FROM SERVER billing_srv INTO billing_remote` creates one foreign table per remote table or view. `EXCEPT (...)` inverts the list. Its postgres_fdw options: - `import_collate` (default true) copies column collations; the manual warns that turning it off risks collations that do not match the data. - `import_default` (default false) copies `DEFAULT` expressions, which is risky because `nextval()` would run against a local sequence. - `import_generated` (default true) copies generated-column expressions. - `import_not_null` (default true) copies `NOT NULL`. No other constraint is ever imported: `CHECK`, unique and foreign keys stay behind, because an expression might evaluate differently on the two servers. Partitions are skipped unless named in `LIMIT TO`, since the partitioned root already exposes their rows. Columns of user-defined types need types of the same names locally. The import is a one-time snapshot of the definitions: a column added on the billing server later is invisible until you re-create or alter the foreign table. ## PostgreSQL 18: SCRAM pass-through Storing a remote password in a user mapping is the classic weak point. PostgreSQL 18 adds the `use_scram_passthrough` option, on the server or on a mapping, which makes postgres_fdw authenticate with the SCRAM secret the client itself just used. Its conditions are strict: - the remote server must request `scram-sha-256`; - both servers must hold **identical SCRAM secrets** for the role, the same salt and iteration count, not merely the same password; - the client's own session must have logged in with SCRAM; - the mapping's `password` option is then not used. `dblink` gained the same pass-through in 18.
- What does IMPORT FOREIGN SCHEMA bring across, and what does it leave behind?It creates foreign tables with the remote column names and types, plus `NOT NULL`, collations and generated expressions by default. Defaults come only with `import_default 'true'`. `CHECK`, unique and foreign-key constraints are never imported, partitions are skipped unless named in `LIMIT TO`, and later remote column changes are not tracked.
- A non-superuser's postgres_fdw query fails with password or GSSAPI delegated credentials required. Why?Either the user mapping has no password, or the remote server let the connection in without asking for one, for example through `trust`. postgres_fdw refuses so a non-superuser cannot borrow the local server process's own credentials. Store a password, delegate GSSAPI credentials, use 18's `use_scram_passthrough`, or, as a superuser, set `password_required 'false'` on that specific mapping.
- What has to be true for use_scram_passthrough to work in PostgreSQL 18?The remote server must ask for `scram-sha-256`, the client must have logged in to the local server with SCRAM, and both servers must store identical SCRAM secrets for the role, same salt and iterations. Only the side running postgres_fdw needs 18; the mapping's password is ignored.
- Put the remote user and password in the CREATE SERVER options.
- IMPORT FOREIGN SCHEMA copies the remote CHECK and foreign key constraints.
- Anyone who can query pg_user_mappings sees every stored password.
- Setting password_required to false on the PUBLIC mapping is a safe shortcut.
- After IMPORT FOREIGN SCHEMA, new remote columns show up automatically.
- use_scram_passthrough only needs the same password on both servers.
Assumes PostgreSQL 18; the use_scram_passthrough option of postgres_fdw is new in 18, and earlier releases need a password stored in the user mapping.