Postgresql_fdw Authentication Changes in PostgreSQL 13

PostgreSQL 13 is released with some cool features, such as index enhancement, partition enhancements, and many others. Along with these enhancements, there are some security-related enhancements that require some explanation. There are two major ones: one is related to libpq and the other is related to postgres_fdw. As it is known that postgres_fdw  is considered to be a “reference implementation” for other foreign data wrappers, all other foreign data wrappers follow their footsteps in development.  This is a community-supported foreign-data wrapper. The blog will explain the security changes in postgresq_fdw.

1 – The superuser can permit the non-superusers to establish a password-less connection on postgres_fdw

Previously, only the superuser can establish a password-less connection with PostgreSQL using postgres_fdw. No other password-less authentication method was allowed. It had been observed that in some cases there is no password required, so it does not make sense to have that limitation. Therefore, PostgreSQL 13 introduced a new option (password_required) where superusers can give permission to non-superusers to use a password-less connection on postgres_fdw.

postgres=# CREATE EXTENSION postgres_fdw;
CREATE EXTENSION

postgres=# CREATE SERVER postgres_svr FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'postgres');
CREATE SERVER

postgres=# CREATE FORIENG TABLE foo_for(a INT) SERVER postgres_svr OPTIONS(table_name 'foo');
CREATE FOREIGN TABLE

postgres=# create user MAPPING FOR vagrant SERVER postgres_svr;
CREATE USER MAPPING
postgres=# SELECT * FROM foo_for;
 a 
---
 1
 2
 3
(3 rows)

When we perform the same query from a non-superuser, then we will get this error message:

ERROR:  password is required
DETAIL:  Non-superusers must provide a password in the user mapping

postgres=# CREATE USER nonsup;
CREATE ROLE

postgres=# create user MAPPING FOR nonsup SERVER postgres_svr;
CREATE USER MAPPING

postgres=# grant ALL ON foo_for TO nonsup;
GRANT

vagrant@vagrant:/work/data$ psql postgres -U nonsup;
psql (13.0)
Type "help" for help.

postgres=> SELECT * FROM foo_for;
2020-09-28 13:00:02.798 UTC [16702] ERROR:  password is required
2020-09-28 13:00:02.798 UTC [16702] DETAIL:  Non-superusers must provide a password in the user mapping.
2020-09-28 13:00:02.798 UTC [16702] STATEMENT:  SELECT * FROM foo_for;
ERROR:  password is required
DETAIL:  Non-superusers must provide a password in the user mapping.

Now perform the same query from non-superuser after setting the new parameter password_required ‘false’ while creating the user mapping.

vagrant@vagrant:/work/data$ psql postgres
psql (13.0)
Type "help" for help.

postgres=# DROP USER MAPPING FOR nonsup SERVER postgres_svr;
DROP USER MAPPING

postgres=# CREATE USER MAPPING FOR nonsup SERVER postgres_svr OPTIONS(password_required 'false');
CREATE USER MAPPING

vagrant@vagrant:/work/data$ psql postgres -U nonsup;
psql (13.0)
Type "help" for help.

postgres=> SELECT * FROM foo_for;
 a 
---
 1
 2
 3
(3 rows)

2 – Authentication via an SSL certificate

A new option is provided to use an SSL certificate for authentication in postgres_fdw. To achieve this, the two new options added to use that feature are sslkey and sslcert.

Before performing this task we need to configure SSL for server and client. There are many blogs available (How to Enable SSL authentication for an EDB Postgres Advanced Server and SSL Certificates For PostgreSQL) to setup SSL for PostgreSQL, and this blog tries to configure SSL with minimum requirements.

Step 1: Generate Key in $PGDATA

vagrant@vagrant$  openssl genrsa -des3 -out server.key 1024
Generating RSA private key, 1024 bit long modulus (2 primes)
.+++++
..................+++++
e is 65537 (0x010001)
Enter pass phrase for server.key:
Verifying - Enter pass phrase for server.key:


vagrant@vagrant$ openssl rsa -in server.key -out server.key
Enter pass phrase for server.key:
writing RSA key

Step 2:  Change the mode of the server.key

vagrant@vagrant$  chmod og-rwx server.key

Step 3: Generate the certificate

vagrant@vagrant$ openssl req -new -key server.key -days 3650 -out server.crt -x509
-----
Country Name (2 letter code) [AU]:PK
State or Province Name (full name) [Some-State]:ISB
Locality Name (eg, city) []:Islamabad
Organization Name (eg, company) [Internet Widgits Pty Ltd]:Percona
Organizational Unit Name (eg, section) []:Dev
Common Name (e.g. server FQDN or YOUR name) []:localhost
Email Address []:ibrar.ahmad@gmail.com


vagrant@vagrant$ cp server.crt root.crt

Now we need to generate the client certificate.

Step 4: Generate a Client key

vagrant@vagrant$ openssl genrsa -des3 -out /tmp/postgresql.key 1024
Generating RSA private key, 1024 bit long modulus (2 primes)
..........................+++++
.....................................................+++++
e is 65537 (0x010001)
Enter pass phrase for /tmp/postgresql.key:
Verifying - Enter pass phrase for /tmp/postgresql.key:



vagrant@vagrant$ openssl rsa -in /tmp/postgresql.key -out /tmp/postgresql.key
Enter pass phrase for /tmp/postgresql.key:
writing RSA key


vagrant@vagrant$ openssl req -new -key /tmp/postgresql.key -out 
-----
Country Name (2 letter code) [AU]:PK
State or Province Name (full name) [Some-State]:ISB
Locality Name (eg, city) []:Islamabad
Organization Name (eg, company) [Internet Widgits Pty Ltd]:Percona
Organizational Unit Name (eg, section) []:Dev
Common Name (e.g. server FQDN or YOUR name) []:127.0.0.1
Email Address []:ibrar.ahmad@gmail.com

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:pakistan
An optional company name []:Percona 

Step 5:  Copy root.crt to the client

vagrant@vagrant$ cp data5555/root.crt /tmp/

Step 6: Test the connection using a certificate

vagrant@vagrant$ psql 'host=localhost port=5555 dbname=postgres user=ibrar sslmode=verify-full sslcert=/tmp/postgresql.crt sslkey=/tmp/postgresql.key sslrootcert=/tmp/root.crt'
psql (13.0)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=> \q

Now we are ready, and we can create a foreign server in PostgreSQL with certificates.

postgres=# CREATE server postgres_ssl_svr foreign data wrapper postgres_fdw options (dbname 'postgres', host 'localhost', port '5555', sslcert '/tmp/postgresql.crt', sslkey '/tmp/postgresql.key', sslrootcert '/tmp/root.crt');
CREATE SERVER

postgres=# create user MAPPING FOR vagrant SERVER postgres_ssl_svr;
CREATE USER MAPPING

postgres=# create foreign table foo_ssl_for(a int) server postgres_ssl_svr options(table_name 'foo');
CREATE FOREIGN TABLE

Now we are ready and set to query a foreign table by postgres_fdw using certificate authentication.

postgres=# select * from foo_ssl_for;

 a 
---
 1
 2
 3
(3 rows)

Note:  Only superusers can modify user mappings options sslcert and sslkey settings.

---

Our white paper “Why Choose PostgreSQL?” looks at the features and benefits of PostgreSQL and presents some practical usage examples. We also examine how PostgreSQL can be useful for companies looking to migrate from Oracle.

Download PDF

by Ibrar Ahmed via Percona Database Performance Blog