오류 발생 : pgsql이 rails와 함께 작동하도록 시도 할 때 "postgres"사용자에 대한 피어 인증에 실패했습니다.

오류 발생 : pgsql이 rails와 함께 작동하도록 시도 할 때 "postgres"사용자에 대한 피어 인증에 실패했습니다.

---

오류가 발생합니다.

FATAL: Peer authentication failed for user "postgres"

Postgres를 Rails와 함께 사용하려고 할 때.

여기 my pg_hba.conf, my database.yml및 전체 추적 덤프가 있습니다 .

pg_hba에서 인증을 md5로 변경하고 다른 것을 시도했지만 아무것도 작동하지 않는 것 같습니다.

또한 Rails 3.2, FATAL : Peer authentication failed for user (PG :: Error)에 따라 새 사용자와 데이터베이스를 만들려고 했습니다.

그러나 그들은 pgadmin 또는 내가 실행할 때도 표시되지 않습니다 sudo -u postgres psql -l.

내가 어디로 잘못 가고 있는지 아십니까?

---

문제는 여전히 pg_hba.conf파일 ( /etc/postgresql/9.1/main/pg_hba.conf*)입니다.

이 줄 :

local   all             postgres                                peer

해야한다:

local   all             postgres                                md5

*이 파일을 찾을 수없는 경우 실행 locate pg_hba.conf하면 파일 위치가 표시됩니다.

이 파일을 변경 한 후 PostgreSQL 서버를 다시 시작하는 것을 잊지 마십시오. Linux를 사용하는 경우 sudo service postgresql restart.

인증 방법에 대한 공식 PostgreSQL 문서에 따르면 두 옵션에 대한 간략한 설명입니다 .

피어 인증

피어 인증 방법은 커널에서 클라이언트의 운영 체제 사용자 이름을 가져 와서 허용 된 데이터베이스 사용자 이름 (선택적 사용자 이름 매핑 포함)으로 사용하여 작동합니다. 이 방법은 로컬 연결에서만 지원됩니다.

비밀번호 인증

비밀번호 기반 인증 방법은 md5 및 비밀번호입니다. 이러한 방법은 암호가 연결을 통해 전송되는 방식, 즉 각각 MD5 해시 및 일반 텍스트를 제외하고는 유사하게 작동합니다.

암호 "스니핑"공격이 걱정된다면 md5가 선호됩니다. 가능하면 평범한 암호는 항상 피해야합니다. 그러나 md5는 db_user_namespace 기능과 함께 사용할 수 없습니다. 연결이 SSL 암호화로 보호되는 경우 암호를 안전하게 사용할 수 있습니다 (SSL 사용에 의존하는 경우 SSL 인증서 인증이 더 나은 선택 일 수 있음).

샘플 위치 pg_hba.conf:
/etc/postgresql/9.1/main/pg_hba.conf

---

Postgresql을 설치 한 후 아래 단계를 수행했습니다.

1. pg_hba.confUbuntu에 대한 파일 을 열고 /etc/postgresql/9.x/main다음 줄을 변경하십시오.

   로컬 모든 postgres 피어

   ...에

   로컬 모든 postgres 신뢰

2. 서버를 다시 시작하십시오.

   $ sudo service postgresql restart
   

3. psql에 로그인하고 비밀번호를 설정하십시오.

   $ psql -U postgres
   db> ALTER USER postgres with password 'your-pass';
   

4. 마지막으로 변경 pg_hba.conf에서을

   로컬 모든 postgres 신뢰

   ...에

   로컬 모든 postgres md5

postgresql 서버를 다시 시작한 후 자신의 암호로 액세스 할 수 있습니다.

인증 방법 세부 정보 :

trust-서버에 연결할 수있는 사람은 누구나 데이터베이스에 액세스 할 수 있습니다.

peer-클라이언트의 운영 체제 사용자 이름을 데이터베이스 사용자 이름으로 사용하여 액세스합니다.

md5-암호 기반 인증

자세한 내용은 여기에서 확인하세요.

---

localhost (127.0.0.1)를 통해 연결하는 경우 특정 문제가 발생하지 않아야합니다. pg_hba.conf를 많이 사용하지는 않지만 대신 연결 문자열을 조정합니다.

psql -U someuser -h 127.0.0.1 database

someuser는 연결하려는 사용자이고 database는 사용자가 연결할 권한이있는 데이터베이스입니다.

Postgres를 설정하기 위해 Debian에서 수행하는 작업은 다음과 같습니다.

http://www.postgresql.org/download/linux/debian/  (Wheezy 7.x)

as root …

    root@www0:~# echo "deb http://apt.postgresql.org/pub/repos/apt/ wheezy-pgdg main" >> /etc/apt/sources.list

    root@www0:~# wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -

    root@www0:~# apt-get update

    root@www0:~# apt-get install postgresql-9.4        

    root@www0:~# su - postgres 

    postgres@www0:~$ createuser --interactive -P someuser
    Enter password for new role:
    Enter it again:
    Shall the new role be a superuser? (y/n) n
    Shall the new role be allowed to create databases? (y/n) y
    Shall the new role be allowed to create more new roles? (y/n) n

    postgres@www0:~$ createdb -O someuser database

    postgres@www0:~$ psql -U someuser -h 127.0.0.1 database

즐겨!

---

이것은 나를 위해 일했습니다!

sudo -u postgres psql

---

If you have an issue, you need to locate your pg_hba.conf. The command is:

find / -name 'pg_hba.conf' 2>/dev/null

and after that change the configuration file:

Postgresql 9.3

Postgresql 9.4

The next step is: Restarting your db instance:

service postgresql-9.3 restart

If you have any problems, you need to set password again:

ALTER USER db_user with password 'db_password';

---

1. Go to this /etc/postgresql/9.x/main/ and open pg_hba.conf file

In my case:

$>  sudo nano /etc/postgresql/9.3/main/pg_hba.conf

2. Replace peer with md5

So this will be changed to:

Database administrative login by Unix domain socket local all postgres peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5

This:

Database administrative login by Unix domain socket local all postgres md5

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5

3. Then restart pg server:

   $> sudo service postgresql restart

Below is list of METHODS used to connect with postgres:

# METHOD can be "trust", "reject", "md5", "password", "gss", "sspi",
# "krb5", "ident", "peer", "pam", "ldap", "radius" or "cert".  Note that
# "password" sends passwords in clear text; "md5" is preferred since
# it sends encrypted passwords.

Note: If you have not create you postgres user yet. Create that and now you can access postgres server using that user credentials.

TIP: If it does not work after postgres restart then close terminal and open again.

---

I had the same problem.

The solution from depa is absolutely correct.

Just make sure that u have a user configured to use PostgreSQL.

Check the file:

$ ls /etc/postgresql/9.1/main/pg_hba.conf -l

The permission of this file should be given to the user you have registered your psql with.

Further. If you are good till now..

Update as per @depa's instructions.

i.e.

$ sudo nano /etc/postgresql/9.1/main/pg_hba.conf

and then make changes.

---

sudo psql --host=localhost --dbname=database-name --username=postgres

This solved my issue

---

If you want to keep the default config but want md5 authentication with socket connection for one specific user/db connection, add a "local" line BEFORE the "local all/all" line:

# TYPE  DATABASE     USER         ADDRESS             METHOD

# "local" is for Unix domain socket connections only
local   username     dbname                           md5  # <-- this line
local   all          all                              peer
# IPv4 local connections:
host    all          all          127.0.0.1/32        ident
# IPv6 local connections:
host    all          all          ::1/128             ident

---

The edits above worked for me, after I figured out that I needed to restart the postgres server after making them. For ubuntu:

sudo /etc/init.d/postgresql restart

---

Use host=localhost in connection.

PGconn *conn = PQconnectdb(
    "host=localhost user=postgres dbname=postgres password=123"
);

---

I was moving data directory on a cloned server and having troubles to login as postgres. Resetting postgres password like this worked for me.

root# su postgres
postgres$ psql -U postgres
psql (9.3.6) 
Type "help" for help. 
postgres=#\password 
Enter new password: 
Enter it again: 
postgres=#

---

the below command works for me:

psql -d myDb -U username -W

---

Changing METHOD peer to trust in pg_hba.conf (/etc/postgresql/9.1/main/pg_hba.conf | line 85) solves the issue. Adding md5 asks for a password, hence if there is a requirement to avoid using passwords, use trust instead of md5.

---

You need just set METHOD to trust.

#TYPE  DATABASE        USER            ADDRESS                 METHOD
local    all             all                                     trust

And reload postgres server.

# service postgresql-9.5 reload

Changes in pg_hba.conf dont require RESTART postgres server. just RELOAD.

---

Many of the other answers pertain to settings in the various config files, and the ones pertaining to the pg_hba.conf do apply and are 100% correct. However, make sure you are modifying the correct config files.

As others have mentioned the config file locations can be overridden with various settings inside the main config file, as well as supplying a path to the main config file on the command line with the -D option.

You can use the following command while in a psql session to show where your config files are being read (assuming you can launch psql). This is just a troubleshooting step that can help some people:

select * from pg_settings where setting~'pgsql';  

You should also make sure that the home directory for your postgres user is where you expect it to be. I say this because it is quite easy to overlook this due to the fact that your prompt will display '~' instead of the actual path of your home directory, making it not so obvious. Many installations default the postgres user home directory to /var/lib/pgsql.

If it is not set to what it is supposed to be, stop the postgresql service and use the following command while logged in as root. Also make sure the postgres user is not logged into another session:

usermod -d /path/pgsql postgres

Finally make sure your PGDATA variable is set correctly by typing echo $PGDATA, which should output something similar to:

/path/pgsql/data

If it is not set, or shows something different from what you expect it to be, examine your startup or RC files such as .profile or .bash.rc - this will vary greatly depending on your OS and your shell. Once you have determined the correct startup script for your machine, you can insert the following:

export PGDATA=/path/pgsql/data

For my system, I placed this in /etc/profile.d/profile.local.sh so it was accessible for all users.

You should now be able to init the database as usual and all your psql path settings should be correct!

---

My issue was that I did not type any server. I thought it is a default because of placeholder but when I typed localhost it did work.

---

If you are trying to locate this file in Cloud 9, you can do

sudo vim /var/lib/pgsql9/data/pg_hba.conf

Press I to edit/insert, press ESC 3 times and type :wq will save the file and quit

---

If you are facing this issue with rails and you know that you already have created that user-name with password along with correct rights then you just need to put following at the end of your database.yml file.

host: localhost

overall file will look like below

development:
  adapter: postgresql
  encoding: unicode
  database: myapp_development
  pool: 5
  username: root
  password: admin
  host: localhost

You do not need to touch you pg_hba.conf file at all. Happy coding

---

pg_config is for compliation information, to help extensions and client programs compile and link against PostgreSQL. It knows nothing about the active PostgreSQL instance(s) on the machine, only the binaries.

pg_hba.conf can appear in many other places depending on how Pg was installed. The standard location is pg_hba.conf within the data_directory of the database (which could be in /home, /var/lib/pgsql, /var/lib/postgresql/[version]/, /opt/postgres/, etc etc etc) but users and packagers can put it wherever they like. Unfortunately.

The only valid ways find pg_hba.conf is to ask a running PostgreSQL instance where it's pg_hba.conf is, or ask the sysadmin where it is. You can't even rely on asking where the datadir is and parsing postgresql.conf because an init script might passed a param like -c hba_file=/some/other/path when starting Pg.

What you want to do is ask PostgreSQL:

SHOW hba_file;

This command must be run on a superuser session, so for shell scripting you might write something like:

psql -t -P format=unaligned -c 'show hba_file';

and set the environment variables PGUSER, PGDATABASE, etc to ensure that the connection is right.

Yes, this is somewhat of a chicken-and-egg problem, in that if the user can't connect (say, after screwing up editing pg_hba.conf) you can't find pg_hba.conf in order to fix it.

Another option is to look at the ps command's output and see if the postmaster data directory argument -D is visible there, e.g.

ps aux  | grep 'postgres *-D'

since pg_hba.conf will be inside the data directory (unless you're on Debian/Ubuntu or some derivative and using their packages).

If you're targeting specifically Ubuntu systems with PostgreSQL installed from Debian/Ubuntu packages it gets a little easier. You don't have to deal with hand-compiled-from-source Pg that someone's initdb'd a datadir for in their home dir, or an EnterpriseDB Pg install in /opt, etc. You can ask pg_wrapper, the Debian/Ubuntu multi-version Pg manager, where PostgreSQL is using the pg_lsclusters command from pg_wrapper.

If you can't connect (Pg isn't running, or you need to edit pg_hba.conf to connect) you'll have to search the system for pg_hba.conf files. On Mac and Linux something like sudo find / -type f -name pg_hba.conf will do. Then check the PG_VERSION file in the same directory to make sure it's the right PostgreSQL version if you have more than one. (If pg_hba.conf is in /etc/, ignore this, it's the parent directory name instead). If you have more than one data directory for the same PostgreSQL version you'll have to look at database size, check the command line of the running postgres from ps to see if it's data directory -D argument matches where you're editing, etc. https://askubuntu.com/questions/256534/how-do-i-find-the-path-to-pg-hba-conf-from-the-shell/256711

참고URL : https://stackoverflow.com/questions/18664074/getting-error-peer-authentication-failed-for-user-postgres-when-trying-to-ge