Default PostgreSQL connection configurations

A list of the default connection configurations of PostgreSQL in the pg_hba.conf installed by different means.

Mac OS - PostgreSQL installed via macports

# "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

Mac OS - PostgreSQL installed via brew

# "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

Ubuntu 18.04 - PostgreSQL install via apt

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

Ubuntu 16.04 - PostgreSQL install via apt

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

PostgreSQL installed via conda

# "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

CentOS 8 - PostgreSQL install via yum

# "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

Summary

• On MacOS (macports, brew), CentOS (yum) or any conda installation, no password is needed for any local connection and psycopg2 will work out of the box, both over local sockets and TCP/IP.
• On Ubuntu with Postgresql installed via apt, passwordless login is possible only for postgres users with the same name as the UNIX user. E.g. if you are the ubuntu user, you cannot use a local connection to connect to the server as the postgres user - you first need to become postgres. Furthermore, after you've become postgres and created a new database, database user and password, you cannot use local connections to connect as your new database user via sockets. You must use TCP/IP (-h localhost).