postgres@aaa:~$ psql -U test_user
psql: FATAL: Peer authentication failed for user "test_user"
操作解读:在A用户上用B用户登录psql
答案来自stack overflow:
209Your connection failed because by default
psql
connects over UNIX sockets usingpeer
authentication, that requires the current UNIX user to have the same user name aspsql
. So you will have to create the UNIX userdev
and then login asdev
or usesudo -u dev psql test_development
for accessing the database (andpsql
should not ask for a password).If you cannot or do not want to create the UNIX user, like if you just want to connect to your database for ad hoc queries, forcing a socket connection using
psql --host=localhost --dbname=test_development --username=dev
(as pointed out by @meyerson answer) will solve your immediate problem.But if you intend to force password authentication over Unix sockets instead of the peer method, try changing the following
pg_hba.conf
* line:from
# TYPE DATABASE USER ADDRESS METHOD local all all peer
to
# TYPE DATABASE USER ADDRESS METHOD local all all md5
peer
means it will trust the identity (authenticity) of UNIX user. So not asking for a password.
md5
means it will always ask for a password, and validate it after hashing withMD5
.You can, of course, also create more specific rules for a specific database or user, with some users having
peer
and others requiring passwords.After changing
pg_hba.conf
if PostgreSQL is running you'll need to make it re-read the configuration by reloading (pg_ctl reload
) or restarting (sudo service postgresql restart
).* The file
pg_hba.conf
will most likely be at/etc/postgresql/9.x/main/pg_hba.conf
尝试从root直接登录psql
~# psql Password: psql: FATAL: password authentication failed for user "root"
失败!
换一种姿势
先用postgres用户添加root,
再给root创建一个库,
root直接访问库
或者在root登录psql时加入 -d参数指定一下数据库
PS:可以登录到不同的数据库,只要还是默认权限,就没有问题。
一旦登录,在默认权限下,可以给所有用户库添加自己的表,不能操作其他用户的表
结论:
psql的登录要加数据库名!!!
可以切换Liunx用户,作为其他用户登录psql
登陆的时候liunx用户要和该登录用户保持一致
如果没有该linux用户就去创建一个,然后
sudo -u dev psql test_development
或
使用SSL作为其他用户登录psql,不需要创建用户
sudo psql username -h 127.0.0.1 -d databaseps:只要权限没有限制的情况下,登录的库可以不是该用户下的。