我得到了错误:
FATAL: Peer authentication failed for user "postgres"
当我尝试让postgres与Rails一起工作时。
这是pg_hba.conf,我的数据库。Yml,以及完整跟踪的转储。
我在pg_hba中将身份验证更改为md5,并尝试了不同的方法,但似乎都不起作用。
我还尝试按照Rails 3.2创建一个新用户和数据库,FATAL: Peer authentication failed for user (PG::Error)
但是它们不会在pgadmin上显示,甚至当我运行sudo -u postgres psql -l时也不会显示。
知道我哪里错了吗?
如果您通过localhost(127.0.0.1)连接,则不应该遇到这个特定问题。我不会对pg_hba.conf做太多的改动,但我会调整你的连接字符串:
psql -U someuser -h 127.0.0.1 database
其中someuser是你连接的用户,database是你的用户有权限连接的数据库。
下面是我在Debian上设置postgres的方法:
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
享受吧!
安装Postgresql后,我执行了以下步骤。
Open the file pg_hba.conf. For Ubuntu, use for example /etc/postgresql/13/main$ sudo nano pg_hba.conf and change this line at the bottom of the file, it should be the first line of the settings:
local all postgres peer
to
local all postgres trust
Side note: If you want to be able to connect with other users as well, you also need to change:
local all all peer
to
local all all md5
If you used nano editor, exit with double Escape, x, y, Enter to save the config file.
Restart the server
$ sudo service postgresql restart
Output: * Restarting PostgreSQL 13 database server
Login into psql and set your password
$ psql -U postgres
db> ALTER USER postgres with password 'your-pass';
Output: ALTER ROLE
Side note: If you have other users, they will need a password as well:
db> ALTER USER my_user with password 'your-pass';
Then enter:
exit
Finally change the pg_hba.conf from
local all postgres trust
to
local all postgres md5
Restart the server again
$ sudo service postgresql restart
Output: * Restarting PostgreSQL 13 database server
Login at psql with postgres user
After restarting the postgresql server, the postgres user accepts the password that you chose:
psql -U postgres
Output:
Password for user postgres:
psql (13.4 (Ubuntu 13.4-1.pgdg20.04+1))
Type "help" for help.
And you are in psql:
postgres=#
Side note: Same now works for my_user if you added the user and password:
psql -d YOUR_DB_NAME -U my_user
Which will ask you for the new password of my_user.
认证方法详细说明:
信任——任何可以连接到服务器的人都被授权访问数据库
对端-使用客户端操作系统用户名作为数据库用户名访问它。
Md5基于密码的认证
如需进一步参考资料,请点击这里