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

问题描述

在薄荷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用户

我建议不要修改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屋！