Using passwordless authentication with postgres_fdw (e.g. client certificates)

Craig Ringer
Craig Ringer

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.

Why postgres_fdw restricts passwordless connections

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.


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.

Permitting passwordless connections with 2ndQPostgres

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.

-- 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

Was this article helpful?

0 out of 0 found this helpful