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.
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.
|
1 |
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) |
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
|
1 |
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. |
Now perform the same query from non-superuser after setting the new parameter password_required ‘false’ while creating the user mapping.
|
1 |
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> |
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.
|
1 |
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 |
|
1 |
vagrant@vagrant$ chmod og-rwx server.key |
|
1 |
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 |
Now we need to generate the client certificate.
|
1 |
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> |
|
1 |
vagrant@vagrant$ cp data5555/root.crt /tmp/ |
|
1 |
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 |
Now we are ready, and we can create a foreign server in PostgreSQL with certificates.
|
1 |
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 |
Now we are ready and set to query a foreign table by postgres_fdw using certificate authentication.
|
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.
