keepcalculator

06 Numbers and Powers

Passwordless PostgreSQL Access for Deploy Users

Card No. 06·72649 · Updated · Standard formula, shown below

This guide shows you how to set up passwordless PostgreSQL access for a dedicated deploy user, so your deployment scripts and automation can connect to the database without storing or typing a password. You’ll create a role, configure peer or certificate authentication, and test the connection. You need sudo access to the PostgreSQL server and the ability to edit its configuration files.

What You’ll Need

  • A Linux server (Ubuntu/Debian, RHEL/CentOS, etc.) where PostgreSQL is installed and running.
  • Sudo or root access to that server.
  • The name of the database your application uses (or permission to create one).
  • A basic understanding of the command line and file editing.

Step-by-Step

  1. Create the deploy user (OS level). If you don’t already have a Unix user named deploy, create it: sudo adduser deploy. This user will run your deployment scripts.
  2. Switch to the postgres system user. Run sudo -i -u postgres to get a shell as the PostgreSQL superuser.
  3. Create the PostgreSQL role. Run createuser --login --no-superuser --no-createdb --no-createrole deploy. This creates a role named deploy that can log in but has limited privileges. If you need it to create databases, omit --no-createdb.
  4. Set a password (optional but recommended for backup). Even though you’re going passwordless, set a strong password in case you ever need to connect remotely: psql -c "ALTER USER deploy WITH PASSWORD 'a-strong-password';". Note this down securely, but you won’t use it for normal deploys.
  5. Grant database access. If your database already exists, grant privileges: psql -c "GRANT CONNECT ON DATABASE yourdb TO deploy;" and psql -c "GRANT ALL ON SCHEMA public TO deploy;" (adjust schema name if needed). If the database doesn’t exist, create it with createdb -O deploy yourdb.
  6. Locate the PostgreSQL configuration files. Run psql -t -c "SHOW hba_file;" to get the path to pg_hba.conf. Also run psql -t -c "SHOW config_file;" for postgresql.conf.
  7. Edit pg_hba.conf. Open the file with sudo: sudo nano /etc/postgresql/15/main/pg_hba.conf (adjust version/path). Find the lines for local connections. They typically look like local all all peer for Unix sockets. If it’s not already peer, change it to peer for the local line. This tells PostgreSQL to trust the OS username and not ask for a password.
  8. If you need TCP connections (e.g., from the same host via localhost), add a line like: host all deploy 127.0.0.1/32 trust — but note that trust is insecure; better to use scram-sha-256 with a password or set up SSL certificates. For a truly passwordless local setup, peer is the safest option.
  9. Reload PostgreSQL. Run sudo systemctl reload postgresql or sudo pg_ctl reload (depending on your system).
  10. Test the connection. Exit the postgres shell (exit), then switch to the deploy user: sudo -i -u deploy. Run psql -d yourdb -c "SELECT current_user;". You should see deploy without any password prompt.
  11. Set up your deploy script. In your script, use psql -d yourdb or set the environment variable PGUSER=deploy and PGDATABASE=yourdb. No password needed.

Common Mistakes

  • Editing the wrong pg_hba.conf. There might be multiple PostgreSQL versions installed. Always use SHOW hba_file; to get the exact path.
  • Using trust for remote connections. Trust means anyone who can reach the port can connect as any user. Only use it for local testing, never for production over network.
  • Forgetting to reload. Changes to pg_hba.conf require a reload, not a restart, but if you forget, the old rules apply.
  • Mismatched OS user and DB role. Peer authentication requires the OS username to exactly match the PostgreSQL role name. If your deploy user is deploy, the role must be deploy.
  • Not granting schema privileges. Even if the user can connect, they might not be able to read or write tables if they lack schema privileges.

Tips

  • Use a dedicated deploy role with minimal privileges. Don’t use the postgres superuser.
  • If you need to connect from a remote server, use SSL certificates instead of trust. Create a client certificate and set hostssl with cert authentication.
  • Keep a password set for the role as a fallback for emergency access.
  • Test your deploy script in a staging environment first.
  • Document the exact pg_hba.conf lines you changed, so you can revert if needed.

Troubleshooting

  • “Peer authentication failed” — This means the OS user doesn’t match the DB role. Check your role name with \du in psql and ensure it matches the Unix user.
  • “Password authentication failed” — You’re connecting via TCP (host) and the config requires a password. Either switch to a Unix socket (use psql without -h) or adjust pg_hba.conf to use peer for local TCP.
  • “FATAL: no pg_hba.conf entry” — Your connection type (local or host) isn’t covered. Add the appropriate line and reload.
  • “Permission denied for schema public” — Grant schema privileges: GRANT USAGE ON SCHEMA public TO deploy; and GRANT CREATE ON SCHEMA public TO deploy; if needed.
  • Cannot connect from a script running under a different user — Ensure the script runs as the deploy user, or use sudo -u deploy in your script.
  • Changes not taking effect — Check that you reloaded, not just edited. Run SELECT pg_reload_conf(); as postgres or restart the service.

Frequently Asked Questions

Is passwordless PostgreSQL access secure?

Yes, when using peer authentication over a Unix socket, because it relies on OS user permissions. It’s secure as long as only the deploy user can access the socket and the OS is properly hardened.

Can I use this for remote connections?

Peer authentication only works for local Unix sockets. For remote, you’d need SSL certificates or an SSH tunnel. The guide focuses on local, which is typical for deploys on the same server.

What if my deploy user is not the same as the OS user?

You can map OS users to DB roles in pg_hba.conf using the map option with a user name map. But it’s simpler to create a matching OS user.

Do I need to set a password at all?

No, for peer authentication you don’t need a password. But setting one is a good fallback for remote troubleshooting.

How do I revoke access later?

Remove the role with DROP ROLE deploy; or revoke privileges with REVOKE ALL ON DATABASE yourdb FROM deploy;. Also remove the OS user if no longer needed.

What if I get “role does not exist”?

You haven’t created the role. Go back to step 3 and run createuser.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.

More cards in this drawer

The whole Numbers and Powers drawer →