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 configurationmirrored 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
postgresuser, root or otherwise, can login to PostgreSQL in the Command Line whether it be the systemTerminal or integratedTerminal 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
postgresvia the Command Line:
- Then I typed the following command inside my
postgresroot user (whoami):
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 loginin 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
psqlto login to
postgresand 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 is concerned, since it did not know of any password for this user, authentication failed.
- But I had already removed
superuserpowers from my
postgresuser, so the
postgresuser did not have the authorization to alter the root user in any way.
- I had to go back into my
pg_hba.conffile again and temporarily replace
trustso that I could re-login to PostgreSQL as root without a password, alter the
superuser, logout as root, and then login as
postgresand alter the root user by adding a password:
ALTER USER username PASSWORD 'password';
- Then I logged out of
postgresand 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 superuserfor cases like this, so I kept the
superuserconfiguration for the
postgresuser for the time being. Its superuser powers, however, are NOT as extensive as the root user.