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.

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

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

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

Step 2:  Change the mode of the server.key

Step 3: Generate the certificate

Now we need to generate the client certificate.

Step 4: Generate a Client key

Step 5:  Copy root.crt to the client

Step 6: Test the connection using a certificate

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

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

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

Share this post

Comments (3)

  • Md Athar Ishteyaque Reply

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

    October 19, 2020 at 1:08 pm
  • leiyanliang Reply

    openssl req -new -key /tmp/postgresql.key -out 
    —->>>This command is not correct!

    November 2, 2020 at 11:23 pm
    • Ibrar Ahmed Reply

      @leiyanliang yes last word of the command is missing.
      openssl req -new -key /tmp/postgresql.key -out /tmp/postgresql.csr

      November 4, 2020 at 2:33 pm

Leave a Reply