如何让普通Linux用户无需sudo权限访问PostgreSQL数据库？

Alright, let's work through why your regular Linux user can't connect to the db_name database, even though you can access it with sudo. Based on the pg_hba.conf snippet you shared, here's what's going on and how to fix it.

What's the Root Cause? #

Your pg_hba.conf sets trust for local Unix socket connections (local entries), which means local users don't need a password to connect. But PostgreSQL still requires two key things:

1. The regular user has a matching PostgreSQL role (by default, PostgreSQL uses your Linux username as the default role name when connecting)
2. That role has explicit permission to access the db_name database

When you use sudo, you're likely connecting as the postgres user (which has full admin access), so it works. Your regular user either doesn't have a corresponding PostgreSQL role, or that role lacks the necessary database permissions.

Step-by-Step Fix #

1. First, Confirm the Exact Error #

Have your regular user run this command and note the error message:

psql -d db_name

If you see something like FATAL: role "your_regular_user" does not exist, that confirms the missing role is the core issue.

2. Create the Matching PostgreSQL Role & Grant Permissions #

Switch to the postgres user (the default admin account for PostgreSQL):

sudo -u postgres bash

Create a PostgreSQL role that matches your regular Linux username (replace your_regular_user with the actual username):

createuser your_regular_user

Next, grant this role full access to db_name (adjust permissions if you don't want full control):

psql -d db_name -c "GRANT ALL PRIVILEGES ON DATABASE db_name TO your_regular_user;"

If you only need read-only access, replace ALL PRIVILEGES with SELECT instead.

3. Test the Connection as the Regular User #

Exit the postgres shell (run exit), then have your regular user run the connection command again:

psql -d db_name

You should now be able to access the database without sudo.

Alternative: Connect with an Existing Role (No New Role Needed) #

If you don't want to create a new role, you can have your regular user connect using an existing privileged role (like postgres) directly:

psql -d db_name -U postgres

Since your pg_hba.conf uses trust for local connections, you won't need to enter a password.

Quick Check: Unix Socket Permissions #

If the above steps don't work, verify the PostgreSQL Unix socket directory has the right permissions. The default path is /var/run/postgresql:

ls -ld /var/run/postgresql

It should have permissions like drwxrwxr-x (readable/executable by all users). If not, fix it with:

sudo chmod 775 /var/run/postgresql

内容的提问来源于stack exchange，提问作者smeng