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
- 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. - Switch to the postgres system user. Run
sudo -i -u postgresto get a shell as the PostgreSQL superuser. - Create the PostgreSQL role. Run
createuser --login --no-superuser --no-createdb --no-createrole deploy. This creates a role nameddeploythat can log in but has limited privileges. If you need it to create databases, omit--no-createdb. - 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. - Grant database access. If your database already exists, grant privileges:
psql -c "GRANT CONNECT ON DATABASE yourdb TO deploy;"andpsql -c "GRANT ALL ON SCHEMA public TO deploy;"(adjust schema name if needed). If the database doesn’t exist, create it withcreatedb -O deploy yourdb. - Locate the PostgreSQL configuration files. Run
psql -t -c "SHOW hba_file;"to get the path topg_hba.conf. Also runpsql -t -c "SHOW config_file;"forpostgresql.conf. - 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 likelocal all all peerfor Unix sockets. If it’s not alreadypeer, change it topeerfor the local line. This tells PostgreSQL to trust the OS username and not ask for a password. - 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 thattrustis insecure; better to usescram-sha-256with a password or set up SSL certificates. For a truly passwordless local setup, peer is the safest option. - Reload PostgreSQL. Run
sudo systemctl reload postgresqlorsudo pg_ctl reload(depending on your system). - Test the connection. Exit the postgres shell (
exit), then switch to the deploy user:sudo -i -u deploy. Runpsql -d yourdb -c "SELECT current_user;". You should seedeploywithout any password prompt. - Set up your deploy script. In your script, use
psql -d yourdbor set the environment variablePGUSER=deployandPGDATABASE=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
trustfor 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 bedeploy. - 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
hostsslwithcertauthentication. - 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
\duin 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
psqlwithout-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;andGRANT 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 deployin 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
- Samsung Smart Speaker Rollable Display: Facts
- HP 9730e: Wide Prints, Not A3 Scans
- What 8888 Does When You Use It as DNS Server
- Credential Manager: Seamless Sign-In Explained
- API Governance for Public Data: What Growing Portfolios Need
- Enterprise AI Hidden Cost: Data Architecture ROI