pg_service.conf: the spell your team forgot to learn

I’ll be honest with you. I’m old school. My IDE is vim. My PostgreSQL client is psql. I’ve been using it for almost 20 years and I still think it’s the best PostgreSQL client out there. I might be biased.

When I joined a new team recently, I noticed something: nobody was using psql. Everyone was connecting through their IDE. Which, fair enough, IDEs have saved connection profiles. You click, you’re in. I get it.

But I use vim. So I needed something else.

Enter pg_service.conf.

The problem with connection strings 🔗

When you connect to PostgreSQL, you need to remember a bunch of things: the host, the port, the database name, and the user. That’s four things that can go wrong. And here’s the fun part: the database name that lives in PostgreSQL is rarely the name your colleagues use when they talk about it. They say “prod”. The actual database is called mycompany_production_v2. Good luck remembering that.

What is pg_service.conf? 🔗

It’s a simple INI-format file where you define named connection profiles. Like this:

[prod]
host=db.mycompany.com
port=5432
dbname=mycompany_production_v2
user=laetitia

[staging]
host=db-staging.mycompany.com
port=5432
dbname=mycompany_staging
user=laetitia

That’s it. Now to connect, you just do:

psql service=prod

You name your services the way your team talks about them. No more mental mapping between human names and technical names.

Where does the file live? 🔗

Two options:

• Per-user: ~/.pg_service.conf (or wherever $PGSERVICEFILE points)
• System-wide: pg_service.conf in the directory returned by pg_config --sysconfdir

The per-user file takes precedence over the system-wide one if the same service name exists in both.

If you’re stuck on Windows and, in big companies, you sometimes don’t have the choice, the per-user file lives at %APPDATA%\postgresql\.pg_service.conf. It works the same way.

One file per environment 🔗

Here’s where it gets really interesting. You can have one file per environment. The trick: use the same service names in every file.

My ~/.pg_service_prod.conf and my ~/.pg_service_staging.conf both have a [db1], a [db2], a [reporting]. The aliases match what my team calls these databases. Only the connection details differ.

So psql service=db1 always means the same thing. Which db1 you land on depends on which file is loaded. No edits. No mistakes.

I took this one step further. We connect to our databases through AWS, which means authenticating, exporting credentials, and then connecting. I wrapped all of that in a script that takes two arguments: the environment and the database alias.

./connect.sh staging db1

The script handles the AWS authentication, sets PGSERVICEFILE to the right file, and calls psql service=db1. One command. Done. And because the aliases are consistent across files, the script stays simple.

What about passwords? 🔗

Don’t put passwords in the service file. Seriously.

The right companion for pg_service.conf is .pgpass. It’s another file (~/.pgpass on Linux/Mac) where you store passwords, matched by host, port, database, and user. PostgreSQL reads it automatically. The file must have 0600 permissions or PostgreSQL will refuse to use it.

The two files together give you passwordless connections without storing anything sensitive in a file you might accidentally share.

The best use case: onboarding 🔗

Put a system-wide pg_service.conf in your version control. When someone new joins the team, they clone the repo, copy the file to the right location, set up their .pgpass, and they can connect to every environment on day one.

No “hey can you send me the connection details?” Slack messages. No copy-paste errors. No wrong host in the wrong script.

One more thing 🔗

I said psql is the best PostgreSQL client. I maintain a whole website to prove it: psql-tips.org. If you’re not convinced yet, go have a look.

And if the inconsistency of PostgreSQL’s command-line tools drives you a little crazy (different flags, incompatible syntax between psql, pg_dump, pg_restore…), I built a bash wrapper for that too: PG Lord of the Ring. One tool to rule them all.