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
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
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,
But I had already removed
superuser powers from my
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
trust so that I could re-login to
PostgreSQL as root without a password, alter the
superuser, logout as root, and then login as
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
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.