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 = ''