postgresql 如何让 psql 从 shell 在 ubuntu 中工作：$ psql -U admin -d mydb
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14540570/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I get psql to work in ubuntu from shell: $ psql -U admin -d mydb
提问by zabouti
Apparently this is a pretty hard question to ask because, simple as it is, no one seems to have answered it.
显然，这是一个很难问的问题，因为虽然简单，但似乎没有人回答过它。
I have PostgreSQL running on ubuntu 12.10 server (no GUI).
我在 ubuntu 12.10 服务器（无 GUI）上运行 PostgreSQL。
My goal - my question - is to create a database named "mydb" and a user "admin" such that I can give this command as an ordinary user from the shell and connect to the database:
我的目标——我的问题——是创建一个名为“mydb”的数据库和一个用户“admin”，这样我就可以作为普通用户从 shell 发出这个命令并连接到数据库：
$ psql -U admin -d mydb
$ psql -U 管理员 -d mydb
But I cannot find the answer to this anywhere.
但我无法在任何地方找到答案。
I can log in as user postgresby su'ing and running the psqlcommand:
我可以通过 su'ing 并运行psql命令以用户postgres登录：
$ sudo su -m postgres
postgres@baseubu1210dev:~$ psql
psql (9.1.7)
Type "help" for help.
postgres=#
I have figured out how to create a database named "mydb" and a user "admin" such that:
我已经想出了如何创建一个名为“mydb”的数据库和一个用户“admin”，这样：
postgres=# \du
Role name | List of roles Attributes | Member of
-----------+------------------------------------------------
admin | Superuser, Create role, Create DB | {}
postgres | Superuser, Create role, Create DB, Replication | {}
I have notfigured out how to make sure that user "admin" can connect to the "mydb" database - perhaps someone with such privileges doesn't need to? [As I write, I notice that I should probably revoke Superuser privileges from admin.]
我还没有弄清楚如何确保用户“admin”可以连接到“mydb”数据库——也许拥有这种权限的人不需要？[在我写的时候，我注意到我可能应该从管理员那里撤销超级用户权限。]
And what I really want to know is how to connect from a regular user's shell:
我真正想知道的是如何从普通用户的 shell 连接：
$ psql -U postgres
psql: FATAL: Peer authentication failed for user "postgres"
There are surely unwritten assumptions about running postgreSQL that I'm misunderstanding, but a couple of hours of searching, looking at tutorials, etc., has not solved the problem. I'll be perfectly happy if someone says this has already been answered, especially if it has. I assume and hope that the answer is simple and appreciate your help.
肯定有关于运行 postgreSQL 的不成文假设，我误解了，但是几个小时的搜索，查看教程等，并没有解决问题。如果有人说已经回答了这个问题，我会非常高兴，尤其是如果已经回答了。我假设并希望答案很简单，并感谢您的帮助。
Thanks, ge
谢谢，格
采纳答案by thaJeztah
From the error-message, it seems that the authentication-settings have not been properly set.
从错误消息来看，似乎没有正确设置身份验证设置。
Besides the settings in the database itself, PostgreSQL also uses a configuration file (pg_hba.conf) that specifies what authentication-mechanisms can be used per user, host and database. You can specify that users can only connect via password/md5 password or even 'no authentication required'
除了数据库本身的设置外，PostgreSQL 还使用一个配置文件 (pg_hba.conf)，指定每个用户、主机和数据库可以使用哪些身份验证机制。您可以指定用户只能通过密码/md5 密码甚至“无需身份验证”进行连接
Currently your configuration probably uses 'Peer' authentication for all users, which simply means: Only allow that user if it is the currently logged-in user (e.g. your shell-user should also be called 'admin'). From your question, this is notwhat you want, you'll probably want to use password or md5 authentication mechanism.
当前，您的配置可能对所有用户使用“Peer”身份验证，这仅表示：仅允许该用户是当前登录的用户（例如，您的 shell 用户也应称为“admin”）。从您的问题来看，这不是您想要的，您可能想要使用密码或 md5 身份验证机制。
I think these pages will give you the answer you need:
我认为这些页面会给你你需要的答案：
http://web.archive.org/web/20131001194046/http://blog.deliciousrobots.com/2011/12/13/get-postgres-working-on-ubuntu-or-linux-mint/(archived version)
Official documentation:
官方文档：
http://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html
http://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html
Remember to reload postgresql after modifying the pg_hba.conf file!
修改pg_hba.conf文件后记得重新加载postgresql！
update
更新
The original blog-post I referred to is now offline, the link now points to an archived version. Thanks to @O_the_Del for reporting.
我提到的原始博客文章现在离线，链接现在指向存档版本。感谢@O_the_Del 的报告。
回答by Ray O'Donnell
On Debian, PostgreSQL listens by default on localhost, so if you have a rule in pg_hba.conf which allows TCP/IP connections then you can use the -h parameter to match that rule:
在 Debian 上，PostgreSQL 默认在 localhost 上侦听，因此如果您在 pg_hba.conf 中有允许 TCP/IP 连接的规则，那么您可以使用 -h 参数来匹配该规则：
psql -U admin -h localhost mydb
