Changing your (Homebrew) PostgreSQL configuration from trust to md5
January 11th, 2019
As I had mentioned in my previous post Upgrading PostgreSQL from version 10.4 to 11.1 via Homebrew (OSX), I had one last step I had to take to ensure that my PostgreSQL upgrade configuration mirrored that of the previous version. I had to change the following configuration that was implemented on version upgrade:
WARNING: enabling "trust" authentication for local connections You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.
This means that any
postgres user, root or otherwise, can login to PostgreSQL in the Command Line whether it be the system Terminal or integrated Terminal in a code editor, without providing a password. That is the default behavior when installing with Homebrew. I had changed it in the previous version so that all users had to login with their password.
Once you KNOW what you have to do, it really is not that complicated.
The following are the steps I had to take to change my PostgreSQL configuration from trust to md5:
First I had to go login to
postgres via the Command Line:
Then I typed the following command inside my
postgres root user (
This command returned the following:
hba_file ------------------------------------- /usr/local/var/postgres/pg_hba.conf (1 row)
This is the path to the
pg_hba.conf file. This is the PostgreSQL configuration file.
Next I scrolled down toward the bottom of the file until I came across a table that contains a column called Method. It contains the trust configuration. I changed all instances of trust with md5.
CAUTION: Configuring the system for local "trust" authentication # allows any local user to connect as any PostgreSQL user, including # the database superuser. If you do not trust all your local users, # use another authentication method.
md5 ensures that users have to provide their user passwords at
psql login in any Terminal instance. This adds another layer of security to your
postgres databases, and mirrors login to the pgAdmin GUI.
Next I closed the
pg_hba.conf file, quit all instances of Terminal, and then reloaded it.
Then I typed
psql to login to
postgres and was prompted to enter my password. I did, but it failed! of course it failed, because when I created my root user, I never entered my password. I only created the database naming it with my username, so as far as PostgreSQL wass concerned, since it did not know of any password for this user, authentication failed.
But I had already removed
superuser powers from my
postgres user, so the
postgres user did not have the authorization to alter the root user in any way.
I had to go back into my
pg_hba.conf file again and temporarily replace
trust so that I could re-login to PostgreSQL as root without a password, alter the
postgres user to
superuser, logout as root, and then login as
postgres and alter the root user by adding a password:
ALTER USER username PASSWORD 'password';
Then I logged out of
postgres and even stopped it from running with the command
brew services stop postgresql
because brew services is my preferred way of starting and stopping the PostgreSQL server. This is necessary in order for any changes made to be recognized at the next login. The same goes for making changes to the
pg_hba.conf file. You have to make sure that
postgresql is not running in order for the configuration changes to be recognized at the next
Next I quit Terminal and then reloaded as before.
Then I tried to login again as the root user (
whoami). This time I was prompted to provide my password, and everything worked as expected.
This taught me that maybe it would be good to have a
backup superuser for cases like this, so I kept the
superuser configuration for the
postgres user for the time being. Its superuser powers, however, are NOT as extensive as the root user.