我刚刚安装了postgresql,在安装过程中我指定了密码x。 当我尝试做createdb并指定任何密码时,我得到消息:
createdb:无法连接数据库postgres: FATAL:用户密码验证失败
createuser也一样。
我该怎么开始呢? 我可以将自己作为用户添加到数据库吗?
我刚刚安装了postgresql,在安装过程中我指定了密码x。 当我尝试做createdb并指定任何密码时,我得到消息:
createdb:无法连接数据库postgres: FATAL:用户密码验证失败
createuser也一样。
我该怎么开始呢? 我可以将自己作为用户添加到数据库吗?
在Linux下,PostgresQL通常配置为允许根用户以postgres超级用户postgres的身份从shell(控制台或ssh)登录。
$ psql -U postgres
然后像往常一样创建一个新数据库:
CREATE ROLE myuser LOGIN password 'secret';
CREATE DATABASE mydatabase ENCODING 'UTF8' OWNER myuser;
这应该在不接触pg_hba.conf的情况下工作。如果你想通过网络使用一些GUI工具来做到这一点,那么你就需要修改pg_hba.conf。
其他的答案都不能让我完全满意。以下是Xubuntu 12.04.1 LTS上postgresql-9.1的工作原理。
Connect to the default database with user postgres: sudo -u postgres psql template1 Set the password for user postgres, then exit psql (Ctrl-D): ALTER USER postgres with encrypted password 'xxxxxxx'; Edit the pg_hba.conf file: sudo vim /etc/postgresql/9.1/main/pg_hba.conf and change "peer" to "md5" on the line concerning postgres: local all postgres peer md5 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. Restart the database : sudo /etc/init.d/postgresql restart (Here you can check if it worked with psql -U postgres). 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). Again, edit the pg_hba.conf file (see step 3 above), and change "peer" to "md5" on the line concerning "all" other users: local all all peer md5 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. Now createdb <dbname> should work.
编辑:警告:请阅读埃文·卡罗尔发布的答案。看来这种方法不安全,不推荐使用。
在标准的Ubuntu 14.04 64位安装中,这对我来说是有效的。
我按照http://suite.opengeo.org/4.1/dataadmin/pgGettingStarted/firstconnect.html上的说明做了一些小修改
安装postgreSQL(如果你的机器上还没有安装):
Sudo apt-get安装postgresql
使用postgres用户运行psql
sudo –u postgres psql postgres
设置postgres用户的新密码:
\password postgres
退出 psql
\q
编辑/etc/postgresql/9.3/main/pg_hba.conf,修改如下:
使用Unix域套接字登录数据库 本地所有postgres peer
To:
使用Unix域套接字登录数据库 本地所有postgres md5
重启postgreSQL:
Sudo服务postgresql重启
创建一个新数据库
Sudo -u postgres createdb mytestdb
再次使用postgres用户运行psql:
psql –U postgres –W
列出现有的数据库(你的新数据库现在应该在那里):
\l
有两种方法可以使用。两者都需要创建用户和数据库。
Using createuser and createdb, $ sudo -u postgres createuser --superuser $USER $ createdb mydatabase $ psql -d mydatabase 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 postgres user, SHOW port; Or, $ grep "port =" /etc/postgresql/*/main/postgresql.conf
旁注:postgres用户
我建议不要修改postgres用户。
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. 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. 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.
如果你像我一样运行macOS,你可能没有postgres用户。
当试图运行sudo -u postgres psql时,我得到了错误sudo: unknown user: postgres
幸运的是,postgres提供了可执行文件。
createuser -D /var/postgres/var-10-local --superuser --username=nick
createdb --owner=nick
然后我就可以毫无问题地访问psql了。
psql
psql (10.2)
Type "help" for help.
nick=#
如果您要从头创建一个新的postgres实例,下面是我所采取的步骤。我使用了一个非默认端口,因此可以运行两个实例。
mkdir /var/postgres/var-10-local
pg_ctl init -D /var/postgres/var-10-local
然后我用我喜欢的端口5433编辑/var/ postgresql.conf。
/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
完成了!
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`
在MacOS中,我按照以下步骤使其工作。
首次安装时,请在安装完成后获取系统用户名。
$ cd ~
$ pwd
/Users/someuser
$ psql -d postgres -U someuser
现在您已经登录到系统,可以创建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