Checking whether all users have a secure password

PostgreSQL has no built-in facilities to make sure that you are using strong passwords.

The best you can do is make sure that all users' passwords are encrypted, and that your pg_hba.conf file does not allow logins with a plain password. That is, always use MD5 as the login method for users.

For client applications connecting from trusted private networks, either real or virtual (VPN), you may use host-based access, that is, if 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.

How to do it…

To see which users have unencrypted passwords, use this query:

test2=# select usename,passwd from pg_shadow where passwd not like 'md5%' or length(passwd) <> 35;
 usename  |    passwd    
----------+--------------
 tim      | weakpassword
 asterisk | md5chicken
(2 rows)

To see users with encrypted passwords...