全新安装后如何登录并验证 Postgresql？

How do I login and authenticate to Postgresql after a fresh install?

提问by user61734

Did a new install of postgres 8.4 on mint ubuntu. How do I create a user for postgres and login using psql?

在 mint ubuntu 上新安装了 postgres 8.4。如何为 postgres 创建用户并使用 psql 登录？

When I type psql, it just tells me

当我输入 psql 时，它只是告诉我

psql: FATAL: Ident authentication failed for user "my-ubuntu-username"

回答by Evan Carroll

There are two methods you can use. Both require creating a user anda database.

您可以使用两种方法。两者都需要创建用户和数据库。

By default psql connects to the database with the same name as the user. So there is a convention to make that the "user's database". And there is no reason to break that convention if your user only needs one database. We'll be using mydatabaseas the example database name.

默认情况下，psql 连接到与用户同名的数据库。所以有一个约定，使“用户的数据库”。如果您的用户只需要一个数据库，则没有理由打破这一约定。我们将使用mydatabase作为示例数据库名称。

1. Using createuser and createdb, we can be explicit about the database name,

   $ sudo -u postgres createuser -s $USER
   $ createdb mydatabase
   $ psql -d mydatabase
   

   You should probably be omitting that entirely and letting all the commands default to the user's name instead.

   $ sudo -u postgres createuser -s $USER
   $ createdb
   $ psql
   

2. Using the SQL administration commands, and connecting with a password over TCP

   $ sudo -u postgres psql postgres
   

   And, then in the psql shell

   CREATE ROLE myuser LOGIN PASSWORD 'mypass';
   CREATE DATABASE mydatabase WITH OWNER = myuser;
   

   Then you can login,

   $ psql -h localhost -d mydatabase -U myuser -p <port>
   

   If you don't know the port, you can always get it by running the following, as the postgresuser,

   SHOW port;
   

   Or,

   $ grep "port =" /etc/postgresql/*/main/postgresql.conf
   

1. 使用 createuser 和 createdb，我们可以明确数据库名称，

   $ sudo -u postgres createuser -s $USER
   $ createdb mydatabase
   $ psql -d mydatabase
   

   您可能应该完全省略这一点，而是让所有命令默认为用户名。

   $ sudo -u postgres createuser -s $USER
   $ createdb
   $ psql
   

2. 使用 SQL 管理命令，并通过 TCP 使用密码连接

   $ sudo -u postgres psql postgres
   

   然后在 psql shell 中

   CREATE ROLE myuser LOGIN PASSWORD 'mypass';
   CREATE DATABASE mydatabase WITH OWNER = myuser;
   

   然后就可以登陆了

   $ psql -h localhost -d mydatabase -U myuser -p <port>
   

   如果您不知道端口，您始终可以通过以postgres用户身份运行以下命令来获取它，

   SHOW port;
   

   或者，

   $ grep "port =" /etc/postgresql/*/main/postgresql.conf
   

Sidenote: the postgresuser

旁注：postgres用户

I suggest NOTmodifying the postgresuser.

我建议不要修改postgres用户。

1. It's normally locked from the OS. No one is supposed to "log in" to the operating system as postgres. You're supposed to have root to get to authenticate as postgres.
2. It's normally not password protected and delegates to the host operating system. This is a good thing. This normally means in order to log in as postgreswhich is the PostgreSQL equivalent of SQL Server's SA, you have to have write-access to the underlying data files. And, that means that you could normally wreck havoc anyway.
3. By keeping this disabled, you remove the risk of a brute force attack through a named super-user. Concealing and obscuring the name of the superuser has advantages.

1. 它通常被操作系统锁定。没有人应该以postgres. 您应该拥有 root 身份才能进行身份验证postgres。
2. 它通常不受密码保护并委托给主机操作系统。这是一件好事。这通常意味着为了以postgresPostgreSQL 等价于 SQL Server 的方式登录SA，您必须具有对底层数据文件的写访问权限。而且，这意味着你通常可以破坏破坏。
3. 通过禁用此功能，您可以消除通过指定超级用户进行暴力攻击的风险。隐藏和隐藏超级用户的名称具有优势。

回答by user262976

by default you would need to use the postgres user:

默认情况下，您需要使用 postgres 用户：

sudo -u postgres psql postgres

回答by cope360

The error your are getting is because your-ubuntu-username is not a valid Postgres user.

您得到的错误是因为 your-ubuntu-username 不是有效的 Postgres 用户。

You need to tell psql what database username to use

你需要告诉 psql 使用什么数据库用户名

psql -U postgres

You may also need to specify the database to connect to

您可能还需要指定要连接的数据库

psql -U postgres -d <dbname>

回答by Dutch Glory

you can also connect to database as "normal" user (not postgres):

您还可以作为“普通”用户（不是 postgres）连接到数据库：

postgres=# \connect opensim Opensim_Tester localhost;

Password for user Opensim_Tester:    

You are now connected to database "opensim" as user "Opensim_Tester" on host "localhost" at port "5432"

回答by toka

If your database client connects with TCP/IP and you have ident auth configured in your pg_hba.conf check that you have an identd installed and running. This is mandatory even if you have only local clients connecting to "localhost".

如果您的数据库客户端使用 TCP/IP 连接并且您在 pg_hba.conf 中配置了 ident auth，请检查您是否安装并运行了 identd。即使只有本地客户端连接到“localhost”，这也是强制性的。

Also beware that nowadays the identd may have to be IPv6 enabledfor Postgresql to welcome clients which connect to localhost.

还要注意，现在 identd 可能必须为 Postgresql启用 IPv6才能欢迎连接到本地主机的客户端。

回答by Braian Coronel

The main difference between logging in with a postgres user or any other user created by us, is that when using the postgres user it is NOT necessary to specify the host with -hand instead for another user if.

使用 postgres 用户或我们创建的任何其他用户登录的主要区别在于，当使用 postgres 用户时，不需要使用-h指定主机，而是使用其他用户 if。

Login with postgres user

使用 postgres 用户登录

$ psql -U postgres 

Creation and login with another user

使用其他用户创建和登录

# CREATE ROLE usertest LOGIN PASSWORD 'pwtest';
# CREATE DATABASE dbtest WITH OWNER = usertest;
# SHOW port;
# \q

$ psql -h localhost -d dbtest -U usertest -p 5432

GL

GL

Source

来源