Postgresql_fdw Authentication Changes in PostgreSQL 13

September 30, 2020

Author

Ibrar Ahmed

PostgreSQL

Security

Share this Post:

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;<br>CREATE EXTENSION<br><br>postgres=# CREATE SERVER postgres_svr FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'postgres');<br>CREATE SERVER<br><br>postgres=# CREATE FORIENG TABLE foo_for(a INT) SERVER postgres_svr OPTIONS(table_name 'foo');<br>CREATE FOREIGN TABLE<br><br>postgres=# create user MAPPING FOR vagrant SERVER postgres_svr;<br>CREATE USER MAPPING<br>postgres=# SELECT * FROM foo_for;<br> a <br>---<br> 1<br> 2<br> 3<br>(3 rows)

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;<br>CREATE ROLE<br><br>postgres=# create user MAPPING FOR nonsup SERVER postgres_svr;<br>CREATE USER MAPPING<br><br>postgres=# grant ALL ON foo_for TO nonsup;<br>GRANT<br><br>vagrant@vagrant:/work/data$ psql postgres -U nonsup;<br>psql (13.0)<br>Type "help" for help.<br><br>postgres=> SELECT * FROM foo_for;<br>2020-09-28 13:00:02.798 UTC [16702] ERROR:  password is required<br>2020-09-28 13:00:02.798 UTC [16702] DETAIL:  Non-superusers must provide a password in the user mapping.<br>2020-09-28 13:00:02.798 UTC [16702] STATEMENT:  SELECT * FROM foo_for;<br>ERROR:  password is required<br>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<br>psql (13.0)<br>Type "help" for help.<br><br>postgres=# DROP USER MAPPING FOR nonsup SERVER postgres_svr;<br>DROP USER MAPPING<br><br>postgres=# CREATE USER MAPPING FOR nonsup SERVER postgres_svr OPTIONS(password_required 'false');<br>CREATE USER MAPPING<br><br>vagrant@vagrant:/work/data$ psql postgres -U nonsup;<br>psql (13.0)<br>Type "help" for help.<br><br>postgres=> SELECT * FROM foo_for;<br> a <br>---<br> 1<br> 2<br> 3<br>(3 rows)<br><br><br>

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<br>Generating RSA private key, 1024 bit long modulus (2 primes)<br>.+++++<br>..................+++++<br>e is 65537 (0x010001)<br>Enter pass phrase for server.key:<br>Verifying - Enter pass phrase for server.key:<br><br><br>vagrant@vagrant$ openssl rsa -in server.key -out server.key<br>Enter pass phrase for server.key:<br>writing RSA key

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

1	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<br>-----<br>Country Name (2 letter code) [AU]:PK<br>State or Province Name (full name) [Some-State]:ISB<br>Locality Name (eg, city) []:Islamabad<br>Organization Name (eg, company) [Internet Widgits Pty Ltd]:Percona<br>Organizational Unit Name (eg, section) []:Dev<br>Common Name (e.g. server FQDN or YOUR name) []:localhost<br>Email Address []:ibrar.ahmad@gmail.com<br><br><br>vagrant@vagrant$ cp server.crt root.crt

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<br>Generating RSA private key, 1024 bit long modulus (2 primes)<br>..........................+++++<br>.....................................................+++++<br>e is 65537 (0x010001)<br>Enter pass phrase for /tmp/postgresql.key:<br>Verifying - Enter pass phrase for /tmp/postgresql.key:<br><br><br><br>vagrant@vagrant$ openssl rsa -in /tmp/postgresql.key -out /tmp/postgresql.key<br>Enter pass phrase for /tmp/postgresql.key:<br>writing RSA key<br><br><br>vagrant@vagrant$ openssl req -new -key /tmp/postgresql.key -out /tmp/postgresql.csr <br>-----<br>Country Name (2 letter code) [AU]:PK<br>State or Province Name (full name) [Some-State]:ISB<br>Locality Name (eg, city) []:Islamabad<br>Organization Name (eg, company) [Internet Widgits Pty Ltd]:Percona<br>Organizational Unit Name (eg, section) []:Dev<br>Common Name (e.g. server FQDN or YOUR name) []:127.0.0.1<br>Email Address []:ibrar.ahmad@gmail.com<br><br>Please enter the following 'extra' attributes<br>to be sent with your certificate request<br>A challenge password []:pakistan<br>An optional company name []:Percona <br>

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 /tmp/postgresql.csr ----- 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/

1	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'<br>psql (13.0)<br>SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)<br>Type "help" for help.<br>postgres=> q

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');<br>CREATE SERVER<br><br>postgres=# create user MAPPING FOR vagrant SERVER postgres_ssl_svr;<br>CREATE USER MAPPING<br><br>postgres=# create foreign table foo_ssl_for(a int) server postgres_ssl_svr options(table_name 'foo');<br>CREATE FOREIGN TABLE

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;<br><br> a <br>---<br> 1<br> 2<br> 3<br>(3 rows)

1	postgres=# select * from foo_ssl_for;<br><br> a <br>---<br> 1<br> 2<br> 3<br>(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