How to set up PostgreSQL 12 on macOS Big Sur

UPDATE: I now recommend using Docker to set up Postgres on Mac. See this other article from me.

From starting a clean Mac to developing backends with Postgres with it takes some setup.

At the end of this article, you'll have PostgreSQL version 12 set up on your mac, with a user postgres with password postgres., accepting local http connections.

This weak username/password combination is ok because we'll set up Postgres to only allow connections from localhost.

Installing Homebrew

If you've already got homebrew installed, you can skip this section.

If not, you can install the package manager for mac by following their instructions on the homebrew website.

Once you've got brew installed and working, we can install Postgres!

Installing PostgreSQL@12

To install Postgres versions other than the latest one, we need to specify the version. Run the following in your terminal:

brew install

Now let's start Postgres:

brew services start postgresql@12

Enable md5 auth for localhost connections

To connect to a Postgres server via TCP, we need to enable md5 authentication and then create a user with a password.

If you don't have the file /usr/local/var/postgresql@12/pg_hba.conf you need to run initdb /usr/local/var/postgresql@12.

For the Postgres user, edit the file /usr/local/var/postgresql@12/pg_hba.conf, e.g. with nano. Go down until you see something that looks like this:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

And change it to this (add the lines with md5):

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 trust
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

Now restart PostgreSQL:

brew services restart postgresql@12

Add the Postgres Binaries to Bash/Zsh

Before we log into Postgres and use the psql binary, we need to add the Postgres /bin to our PATH.

Use the following command if you're using Bash:

echo 'export PATH="/usr/local/opt/postgresql@12/bin:$PATH"' >> ~/.bashrc
source ~/.bashrc

And use this command if you're using Zsh:

echo 'export PATH="/usr/local/opt/postgresql@12/bin:$PATH"' >> ~/.zshrc
source ~/.zshrc

Setup postgres user with password authentication

Now you can open a connection to Postgres with your current user:

psql -h localhost -p 5432 -d postgres

Then, create a new user named postgres with password postgres:

CREATE USER postgres WITH PASSWORD 'postgres';

Finally, give this user full superuser access:

ALTER USER postgres WITH SUPERUSER;

And you're done! You can quit psql with \q. You should now be able to connect from any local application to Postgres with this connection info:

username = postgres
password = postgres
host = localhost
port = 5432

Of course, you need to create a database within Postgres with psql -h localhost -p 5432 -U postgres -c "CREATE DATABASE shinynewdb".