How to configure postgresql for the first time?
The other answers were not completely satisfying to me. Here's what worked for postgresql-9.1 on Xubuntu 12.04.1 LTS.
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
peermd5To 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
peermd5Restart (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.
Under Linux PostgresQL is usually configured to allow the root user to login as the postgres superuser postgres
from the shell (console or ssh).
$ 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
.
There are two methods you can use. Both require creating a user and a database.
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
Sidenote: the postgres
user
I suggest NOT modifying the postgres
user.
- 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 aspostgres
. - 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'sSA
, 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.