94 lines
2.6 KiB
Markdown
94 lines
2.6 KiB
Markdown
psql peer authetication error
|
||
|
||
**by llm**
|
||
Ah, the **"Peer authentication failed for user"** error typically occurs because PostgreSQL is using **peer authentication**, which means it tries to authenticate you based on your OS username (the user you're logged in as) rather than with a password.
|
||
|
||
In this case, your system username doesn’t match the `postgres` user, so PostgreSQL is rejecting the connection.
|
||
|
||
To fix this, you can change the authentication method in PostgreSQL’s `pg_hba.conf` file to use **password authentication** (or any other method you prefer).
|
||
|
||
Here’s how you can fix this:
|
||
|
||
### 1. **Edit `pg_hba.conf` file**
|
||
|
||
The first thing you need to do is modify the PostgreSQL authentication settings to allow password-based authentication.
|
||
|
||
* Find the location of the `pg_hba.conf` file. It’s typically in one of these locations:
|
||
|
||
* `/etc/postgresql/<version>/main/pg_hba.conf` (on Debian-based systems like Ubuntu)
|
||
* `/var/lib/pgsql/data/pg_hba.conf` (on Red Hat/CentOS/Fedora systems)
|
||
|
||
* Open the `pg_hba.conf` file using a text editor like `nano` or `vim`:
|
||
|
||
```bash
|
||
sudo nano /etc/postgresql/<version>/main/pg_hba.conf
|
||
```
|
||
|
||
OR
|
||
|
||
```bash
|
||
sudo nano /var/lib/pgsql/data/pg_hba.conf
|
||
```
|
||
|
||
### 2. **Change authentication method**
|
||
|
||
Look for lines that look something like this (for local connections):
|
||
|
||
```plaintext
|
||
local all postgres peer
|
||
```
|
||
|
||
This means PostgreSQL is using **peer authentication** for the `postgres` user when connecting locally.
|
||
|
||
Change the `peer` method to `md5` (password authentication):
|
||
|
||
```plaintext
|
||
local all postgres md5
|
||
```
|
||
|
||
This will require a password for the `postgres` user.
|
||
|
||
### 3. **Reload PostgreSQL**
|
||
|
||
After making the changes, you need to reload PostgreSQL to apply them:
|
||
|
||
```bash
|
||
sudo systemctl reload postgresql
|
||
```
|
||
|
||
### 4. **Set a password for the `postgres` user**
|
||
|
||
If you haven’t already set a password for the `postgres` user, do so by logging in as `postgres`:
|
||
|
||
```bash
|
||
sudo -u postgres psql
|
||
```
|
||
|
||
Then, inside the `psql` shell, run:
|
||
|
||
```sql
|
||
ALTER USER postgres PASSWORD 'your_password';
|
||
```
|
||
|
||
Replace `'your_password'` with the password you want to set.
|
||
|
||
### 5. **Try connecting again**
|
||
|
||
Now you should be able to connect using `psql -U postgres` with the password:
|
||
|
||
```bash
|
||
psql -U postgres -h localhost
|
||
```
|
||
|
||
You will be prompted for the password you just set.
|
||
|
||
---
|
||
|
||
### Recap:
|
||
|
||
1. Modify `pg_hba.conf` to use `md5` instead of `peer` for `postgres` user.
|
||
2. Reload PostgreSQL.
|
||
3. Set a password for the `postgres` user.
|
||
4. Try connecting again using `psql -U postgres`.
|
||
|
||
Let me know if this works or if you're still running into any issues!
|