Easier Postgres User Management

I have a Postgres instance that runs on my main server, and provides database services to all of my apps. Like everything, this has grown over the years. So I thought be time for some cleaning.

When I configured the services, I did it inconsistently because I didn’t really understand the different authentication options. Most of the time I would just create a username and password, contact the server over TCP/IP, and call it a day. This meant tracking users + passwords in external password managers, and writing long passwords in configuration files. I knew about postgresql sockets but never really used them. That is, until I discovered peer authentication. This allows postgres to authenticate users locally. You can then specify which users have access to which databases in pga_hba.conf. This allows you to easily add new users and give them permissions for databases, without having to fuck around with passwords. You can configure it by adding the specific users and databases that you want to activate peer authentication for.

For example: if you configure a gitlab instance, and want to give the gitlab user access to the gitlab database, add the following to pg_hba.conf:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             postgres                                peer

# All other users that can access the socket
local   foo             foo                                     peer
local   bar             bar                                     peer
local   gitlab          gitlab                                  peer

This provides an easy way to ensure only specific users are allowed to access specific databases. It has the added benefit of being faster and more reliable, since it uses a Unix socket. I also removed the network authentication for localhost, and disabled listening on a network socket, since that is no longer needed.

/var/lib/postgres/data/postgresql.conf

listen_addresses = ''