psql遇到的坑

postgres@aaa:~$ psql -U test_user

psql: FATAL: Peer authentication failed for user "test_user"

操作解读：在A用户上用B用户登录psql

答案来自stack overflow：

209

Your connection failed because by default psql connects over UNIX sockets using peer authentication, that requires the current UNIX user to have the same user name as psql. So you will have to create the UNIX user dev and then login as dev or use sudo -u dev psql test_development for accessing the database (and psql 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 with MD5.

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 database

ps：只要权限没有限制的情况下，登录的库可以不是该用户下的。