全新安装后如何登录并验证 Postgresql? [英] How do I login and authenticate to Postgresql after a fresh install?

问题描述

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

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

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

When I type psql, it just tells me

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

推荐答案

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

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

默认情况下 psql 连接到与用户同名的数据库.因此，有一个约定，即 用户的数据库".如果您的用户只需要一个数据库，则没有理由打破该约定.我们将使用 mydatabase 作为示例数据库名称.

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 mydatabase as the example database name.

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

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

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

$ sudo -u postgres psql postgres

然后，在 psql shell 中

And, then in the psql shell

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

然后就可以登录了，

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

如果你不知道端口，你总是可以通过运行以下命令来获取它，作为 postgres 用户，

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

SHOW port;

或者，

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

旁注:postgres 用户

我建议 NOT 修改 postgres 用户.

Sidenote: the postgres user

I suggest NOT modifying the postgres user.

1. 它通常被操作系统锁定.任何人都不应该以 postgres 的身份登录"到操作系统.您应该有 root 才能以 postgres 身份进行身份验证.
2. 它通常不受密码保护，并委托给主机操作系统.这是好事.这通常意味着为了以 postgres 登录，它是 SQL Server 的 SA 的 PostgreSQL 等价物，您必须具有对底层数据文件的写访问权限.而且，这意味着无论如何你通常都会造成严重破坏.
3. 通过禁用此功能，您可以消除通过指定超级用户进行暴力攻击的风险.隐藏和掩盖超级用户的名字是有好处的.

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 postgres which 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.

这篇关于全新安装后如何登录并验证 Postgresql?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！