postgres_fdw
disallows passwordless connections by non-superusers unless 2ndQPostgres 11 r1.3 is in use and the password_required
user mapping option is false.
Because of CVE-2007-6601 and CVE-2007-3278, postgres_fdw
requires that connections by non-superusers must specify a password in the user mapping and that the server must actually use that password when authenticating the user. postgres_fdw
will report:
ERROR: password is required
DETAIL: Non-superusers must provide a password in the user mapping.
or
ERROR: password is required
DETAIL: Non-superuser cannot connect if the server does not request a password.
in these cases.
This stops unprivilveged users from escalating to superuser privileges, accessing another user's services, or gaining access to another server they shouldn't usually be able to by using the rights of the unix user the postgres
server runs as. They cannot use trust
or peer
authentication, cannot use a .pgpass
, .pg_service.conf
, cannot use cert
authentication using a client certificate and key readable by the postgres user, etc.
2ndQPostgres 11 r1.3 or greater support the password_required
boolean option as a postgres_fdw
user mapping setting as a 2ndQPostgres extension. It defaults to true, preserving the behaviour set out above. Because it requires a superuser to set it and it applies only to a specific server and user mapping, this extension does not re-open the security hole addressed by CVE-2007-6601 or CVE-2007-3278. It is the administrator's responsibility to ensure that the user mapping permits only the intended level of access. See the 2ndQPostgres postgres_fdw
extension documentation for details.
Setting password_required 'false'
permits the specifically mapped unprivileged users to successfully establish postgres_fdw
with passwordless authentication methods like peer
, gssapi
, sspi
, cert
, etc.
For example, if cert
authentication is configured in pg_hba.conf
on the target server, the sslcert
and sslkey
must be specified. They may be set in the foreign server options or (as a 2ndQPostgres extension, 11 r1.3+) in the user mapping options. If both the server and user mapping options are set, the user mapping options override the server options. The values are paths relative to the server home directory. The certificate must be readable by the unix user the server runs as. The key file must have mode 0600
(u=rw,g=,o=
) or less, and be owned by the unix user the postgres server runs as.
As a 2ndQPostgres extension (11 r1.3+), the sslpassword
option may be set in a user mapping only, specifying the passphrase to use to decrypt the client certificate key in sslkey
if it's encrypted. See the 2ndQPostgres libpq
documentation for details.
This example modifies an existing server and user mapping. To create a new server and user mapping add the same settings shown here to the rest of your options lists, without the prefixed ADD
keyword.
ALTER SERVER my_ssl_server OPTIONS
(
-- To verify foreign server's identity; assuming you haven't already set these:
ADD sslmode 'verify-ca'
ADD sslrootcert 'root.crt'
-- To verify client identity to foreign server
ADD sslcert 'client.crt'
ADD sslkey 'client.key'
);
-- As superuser:
ALTER USER MAPPING FOR my_unprivileged_user
SERVER my_ssl_server OPTIONS (
ADD password_required 'false' -- password_required is a 2ndQPostgres 11 r1.3 extension
);
-- You may optionally set or override the sslcert and the sslkey per-user-mapping
-- instead, e.g. if you want per-user password-protected keys:
ALTER USER MAPPING FOR my_unprivileged_user
SERVER my_ssl_server OPTIONS (
ADD sslcert 'my_unprivileged_user.cert', -- sslcert in user mapping is a 2ndQPostgres 11 r1.3 extension
ADD sslkey 'my_unprivilged_user_encrypted.key', -- sslkey in user mapping is a 2ndQPostgres 11 r1.3 extension
ADD sslpassword 'thekeypassword' -- sslpassword is a 2ndQPostgres 11 r1.3 extension
);