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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | 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
1 2 3 4 5 6 7 8 9 10 11 12 | 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
1 | vagrant@vagrant$ chmod og-rwx server.key |
Step 3: Generate the certificate
1 2 3 4 5 6 7 8 9 10 11 12 | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | 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
1 | vagrant@vagrant$ cp data5555/root.crt /tmp/ |
Step 6: Test the connection using a certificate
1 2 3 4 5 | 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.
1 2 3 4 5 6 7 8 | 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.
1 2 3 4 5 6 7 8 | 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.
Ibrar Ahmed.. I saw your videos on index even some more articles on PostgreSQL.. Its amazing and the way you explain is also perfect..
I also wants to make some articles on EDB PostgreSQL. Like BART, EFM, Cascading Replication, pg_backrest, pg_receivewal even some topics because that articles are not available from scratch.. Is it possible to connect with you..
openssl req -new -key /tmp/postgresql.key -out
—->>>This command is not correct！
@leiyanliang yes last word of the command is missing.
openssl req -new -key /tmp/postgresql.key -out /tmp/postgresql.csr
CREATE server myserver foreign data wrapper postgres_fdw options (dbname ‘xxx’, host ‘xx.xx.xx.xx’, port ‘5555’, sslcert ‘/tmp/client/client.crt’, sslkey ‘/tmp/client/client.key’, sslrootcert ‘/tmp/client/root.crt’);
CREATE USER MAPPING FOR xxxx SERVER myserver;
create foreign table abcd (name text) server myserver;
$psql ‘host=localhost port=5555 dbname=postgres user=ibrar sslmode=verify-full sslcert=/tmp/postgresql.crt sslkey=/tmp/postgresql.key sslrootcert=/tmp/root.crt’
working fine…
but through postgresql_fdw its not working
select count(*) from abcd;
ERROR: could not connect to server “myserver”
(DETAIL: FATAL: connection requires a valid client certificate)
i am using postgresql-13..
Thanks