Checking whether all users have a secure password

By default, as of PostgreSQL 14, passwords are encrypted using the SCRAM-SHA-256 login method for users, which was added in PostgreSQL 10. Any servers upgrading from earlier versions should upgrade from MD5 to SCRAM-SHA-256 password encryption since the MD5 authentication method is considered insecure for many applications.

For client applications connecting from trusted private networks, either real or a virtual private network (VPN), you may use host-based access, provided you know that the machine on which the application is running is not used by some non-trusted individuals. For remote access over public networks, it may be a better idea to use SSL client certificates. See the later recipe, Using SSL certificates to authenticate, for more on this.

How to do it…

To see which users don't yet have SCRAM-encrypted passwords, use this query:

test2=# select usename,passwd from pg_shadow where passwd
not like ...