如何修改pg_hba.conf解决PostgreSQL peer认证失败问题？

Hey there, let's get that peer authentication error sorted out so your george user can connect with a password. Here's exactly what you need to do:

1. Locate your pg_hba.conf file #

On Raspberry Pi, PostgreSQL stores this config file in a version-specific directory. You can find it with this command:

sudo find /etc/postgresql -name pg_hba.conf

Typically it'll be something like /etc/postgresql/15/main/pg_hba.conf (the version number might vary based on what you installed).

2. Modify the authentication rule for local connections #

Open the file with a text editor (like nano):

sudo nano /etc/postgresql/<your-version>/main/pg_hba.conf

Look for the section dealing with Unix domain socket connections—you'll see lines starting with local. Right now, your setup has:

local all postgres peer

You need to add or update a rule for the george user to use password authentication instead of peer. Here are two options:

• Option 1: Only for the george user
  Add this line right after the existing local all postgres peer line:

  local all george scram-sha-256
  

  (Scram-sha-256 is the recommended secure method for PostgreSQL 10+; if you're on an older version, use md5 instead.)

• Option 2: For all local users (if you prefer this approach)
  If you want every local PostgreSQL user to connect with a password, find the line that looks like local all all peer (it might be commented out or further down) and change the method to scram-sha-256 (or md5):

  local all all scram-sha-256
  

3. Set a password for the george user (if you haven't already) #

Even if you created the user, you might not have set a password yet. Log into PostgreSQL as the admin postgres user:

sudo -u postgres psql

Then run this command to set a secure password for george:

ALTER USER george WITH PASSWORD 'your-strong-password-here';
\q

4. Restart PostgreSQL to apply the changes #

On Raspberry Pi, restart the service with:

sudo systemctl restart postgresql

That's it! Now your george user should be able to connect using password authentication, and the psycopg2.OperationalError should be gone.

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