Linux 第一次如何配置postgresql？

How to configure postgresql for the first time?

提问by Rohit Banga

I have just installed postgresql and I specified password x during installation. When I try to do createdband specify any password I get the message:

我刚刚安装了 postgresql，并在安装过程中指定了密码 x。当我尝试执行createdb并指定任何密码时，我收到消息：

createdb: could not connect to database postgres: FATAL: password authentication failed for user

createdb：无法连接到数据库 postgres：致命：用户密码验证失败

Same for createuser.

对于createuser.

How should I start? Can I add myself as a user to the database?

我应该如何开始？我可以将自己作为用户添加到数据库吗？

采纳答案by Manur

The other answers were not completely satisfying to me. Here's what worked for postgresql-9.1 on Xubuntu 12.04.1 LTS.

其他答案对我来说并不完全令人满意。这是适用于 Xubuntu 12.04.1 LTS 上的 postgresql-9.1 的内容。

1. Connect to the default database with user postgres:

   sudo -u postgres psql template1

2. Set the password for user postgres, then exit psql (Ctrl-D):

   ALTER USER postgres with encrypted password 'xxxxxxx';

3. Edit the pg_hba.conffile:

   sudo vim /etc/postgresql/9.1/main/pg_hba.conf

   and change "peer" to "md5" on the line concerning postgres:

   local      all     postgres     peermd5

   To know what version of postgresql you are running, look for the version folder under /etc/postgresql. Also, you can use Nano or other editor instead of VIM.

4. Restart the database :

   sudo /etc/init.d/postgresql restart

   (Here you can check if it worked with psql -U postgres).

5. Create a user having the same name as you (to find it, you can type whoami):

   sudo createuser -U postgres -d -e -E -l -P -r -s <my_name>

   The options tell postgresql to create a user that can login, create databases, create new roles, is a superuser, and will have an encrypted password. The really important ones are -P -E, so that you're asked to type the password that will be encrypted, and -d so that you can do a createdb.

   Beware of passwords: it will first ask you twice the new password (for the new user), repeated, and then once the postgres password (the one specified on step 2).

6. Again, edit the pg_hba.conffile (see step 3 above), and change "peer" to "md5" on the line concerning "all" other users:

   local      all     all     peermd5

7. Restart (like in step 4), and check that you can login without -U postgres:

   psql template1

   Note that if you do a mere psql, it will fail since it will try to connect you to a default database having the same name as you (i.e. whoami). template1 is the admin database that is here from the start.

8. Now createdb <dbname>should work.

1. 使用用户 postgres 连接到默认数据库：

   sudo -u postgres psql 模板1

2. 为用户 postgres 设置密码，然后退出 psql (Ctrl-D)：

   使用加密密码“xxxxxxx”更改用户 postgres；

3. 编辑pg_hba.conf文件：

   须藤vim /etc/postgresql/9.1/main/pg_hba.conf

   并将有关 postgres 的行上的“peer”更改为“md5”：

   本地所有     postgres      peermd5

   要了解您正在运行的 postgresql 版本，请在/etc/postgresql. 此外，您可以使用 Nano 或其他编辑器代替 VIM。

4. 重启数据库：

   须藤 /etc/init.d/postgresql 重启

   （在这里您可以检查它是否与 一起使用psql -U postgres）。

5. 创建一个与您同名的用户（要找到它，您可以输入whoami）：

   须藤 createuser -U postgres -d -e -E -l -P -r -s <my_name>

   这些选项告诉 postgresql 创建一个可以登录、创建数据库、创建新角色的用户，是一个超级用户，并且有一个加密的密码。真正重要的是 -P -E，以便您输入将被加密的密码，以及 -d 以便您可以执行createdb.

   注意密码：它会首先询问您两次新密码（对于新用户），重复一次，然后是一次 postgres 密码（在第 2 步中指定的密码）。

6. 再次编辑pg_hba.conf文件（请参阅上面的第 3 步），并在有关“所有”其他用户的行上将“peer”更改为“md5”：

   本地所有     所有     对等md5

7. 重新启动（如第 4 步），并检查您是否可以在没有 -U postgres 的情况下登录：

   psql模板1

   请注意，如果您只执行psql，它将失败，因为它会尝试将您连接到与您同名的默认数据库（即whoami）。template1 是从一开始就在这里的管理数据库。

8. 现在createdb <dbname>应该工作。

回答by Neall

You probably need to update your pg_hba.conffile. This file controls what users can log in from what IP addresses. I think that the postgres user is pretty locked-down by default.

您可能需要更新您的pg_hba.conf文件。此文件控制哪些用户可以从哪些 IP 地址登录。我认为默认情况下 postgres 用户是非常锁定的。

回答by Oliver Weichhold

Under Linux PostgresQL is usually configured to allow the root user to login as the postgres superuser postgresfrom the shell (console or ssh).

在 Linux 下 PostgresQL 通常被配置为允许 root 用户postgres从 shell（控制台或 ssh）以 postgres 超级用户的身份登录。

$ psql -U postgres

Then you would just create a new database as usual:

然后你就可以像往常一样创建一个新的数据库：

CREATE ROLE myuser LOGIN password 'secret';
CREATE DATABASE mydatabase ENCODING 'UTF8' OWNER myuser;

This should work without touching pg_hba.conf. If you want to be able to do this using some GUI tool over the network - then you would need to mess with pg_hba.conf.

这应该可以在不接触的情况下工作pg_hba.conf。如果您希望能够通过网络使用某些 GUI 工具来执行此操作，那么您需要将pg_hba.conf.

回答by Admdebian

This is my solution:

这是我的解决方案：

su root
su postgres
psql

回答by Wellington Ribeiro

Just browse up to your installation's directory and execute this file "pg_env.bat", so after go at bin folder and execute pgAdmin.exe. This must work no doubt!

只需浏览到您的安装目录并执行此文件“pg_env.bat”，然后进入 bin 文件夹并执行 pgAdmin.exe。毫无疑问，这必须有效！

回答by Robert

EDIT: Warning: Please, read the answer posted by Evan Carroll. It seems that this solution is not safe and not recommended.

编辑：警告：请阅读Evan Carroll发布的答案。似乎这种解决方案不安全，不推荐。

This worked for me in the standard Ubuntu 14.04 64 bitsinstallation.

这在标准的Ubuntu 14.04 64 位安装中对我有用。

I followed the instructions, with small modifications, that I found in http://suite.opengeo.org/4.1/dataadmin/pgGettingStarted/firstconnect.html

我按照在http://suite.opengeo.org/4.1/dataadmin/pgGettingStarted/firstconnect.html 中找到的说明进行了小的修改

1. Install postgreSQL (if not already in your machine):

1. 安装 postgreSQL（如果您的机器中尚未安装）：

sudo apt-get install postgresql

sudo apt-get install postgresql

1. Run psqlusing the postgres user

2. 使用 postgres 用户运行psql

sudo –u postgres psql postgres

sudo –u postgres psql postgres

1. Set a new password for the postgres user:

3. 为 postgres 用户设置新密码：

\password postgres

\password postgres

1. Exit psql

4. 退出 psql

\q

\q

1. Edit /etc/postgresql/9.3/main/pg_hba.confand change:

5. 编辑/etc/postgresql/9.3/main/pg_hba.conf并更改：

#Database administrative login by Unix domain socket local all postgres peer

#Database administrative login by Unix domain socket local all postgres peer

To:

到：

#Database administrative login by Unix domain socket local all postgres md5

#Database administrative login by Unix domain socket local all postgres md5

1. Restart postgreSQL:

6. 重启 postgreSQL：

sudo service postgresql restart

sudo service postgresql restart

1. Create a new database

7. 创建一个新的数据库

sudo –u postgres createdb mytestdb

sudo –u postgres createdb mytestdb

1. Run psql with the postgres user again:

8. 再次使用 postgres 用户运行 psql：

psql –U postgres –W

psql –U postgres –W

1. List the existing databases (your new database should be there now):

9. 列出现有数据库（您的新数据库现在应该在那里）：

\l

\l

回答by Evan Carroll

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

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

1. Using createuser and createdb,

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

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 --superuser $USER
   $ createdb mydatabase
   $ psql -d mydatabase
   

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 Nick Woodhams

If you're running macOS like I am, you may not have the postgres user.

如果你像我一样运行 macOS，你可能没有 postgres 用户。

When trying to run sudo -u postgres psqlI was getting the error sudo: unknown user: postgres

尝试运行时sudo -u postgres psql出现错误sudo: unknown user: postgres

Luckily there are executables that postgres provides.

幸运的是 postgres 提供了可执行文件。

createuser -D /var/postgres/var-10-local --superuser --username=nick
createdb --owner=nick

Then I was able to access psqlwithout issues.

然后我就可以psql毫无问题地访问了。

psql
psql (10.2)
Type "help" for help.

nick=#

---

---

If you're creating a new postgres instance from scratch, here are the steps I took. I used a non-default port so I could run two instances.

如果您要从头开始创建一个新的 postgres 实例，以下是我采取的步骤。我使用了一个非默认端口，所以我可以运行两个实例。

mkdir /var/postgres/var-10-local
pg_ctl init -D /var/postgres/var-10-local

Then I edited /var/postgres/var-10-local/postgresql.confwith my preferred port, 5433.

然后我/var/postgres/var-10-local/postgresql.conf用我喜欢的端口 5433 进行编辑。

/Applications/Postgres.app/Contents/Versions/10/bin/postgres -D /Users/nick/Library/Application\ Support/Postgres/var-10-local -p 5433

createuser -D /var/postgres/var-10-local --superuser --username=nick --port=5433
createdb --owner=nick --port=5433

Done!

完毕！

回答by Suman Astani

Note: textdb is the database which you are going to explore with 'alex' user 

root@kalilinux:~# sudo su - postgres 
postgres=#  psql   
postgres=#  create database testdb;
postgres=#  create user alex with password 'alex';
postgres=# GRANT ALL PRIVILEGES ON DATABASE testdb TO alex;`enter code here`

回答by SuperNova

In MacOS, I followed the below steps to make it work.

在 MacOS 中，我按照以下步骤使其工作。

For the first time, after installation, get the username of the system.

第一次安装后，获取系统的用户名。

$ cd ~
$ pwd
/Users/someuser
$ psql -d postgres -U someuser

Now that you have logged into the system, and you can create the DB.

现在您已登录系统，可以创建数据库。

postgres=# create database mydb;
CREATE DATABASE
postgres=# create user myuser with encrypted password 'pass123';
CREATE ROLE
postgres=# grant all privileges on database mydb to myuser;
GRANT