How to configure postgresql for the first time?
LinuxDatabasePostgresqlConfigurationLinux Problem Overview
I have just installed postgresql and I specified password x during installation.
When I try to do createdb
and specify any password I get the message:
> createdb: could not connect to database postgres: FATAL: password authentication failed for user
Same for createuser
.
How should I start? Can I add myself as a user to the database?
Linux Solutions
Solution 1 - Linux
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
peermd5 -
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.
Solution 2 - Linux
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
.
Solution 3 - Linux
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
postgres
user
Sidenote: the 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.
Solution 4 - Linux
This is my solution:
su root
su postgres
psql
Solution 5 - Linux
EDIT: Warning: Please, read the answer posted by Evan Carroll. It seems that this solution is not safe and not recommended.
This worked for me in the standard Ubuntu 14.04 64 bits installation.
I followed the instructions, with small modifications, that I found in http://suite.opengeo.org/4.1/dataadmin/pgGettingStarted/firstconnect.html
- Install postgreSQL (if not already in your machine):
sudo apt-get install postgresql
- Run psql using the postgres user
sudo –u postgres psql postgres
- Set a new password for the postgres user:
\password postgres
- Exit psql
\q
- Edit /etc/postgresql/9.3/main/pg_hba.conf and change:
#Database administrative login by Unix domain socket local all postgres peer
To:
#Database administrative login by Unix domain socket local all postgres md5
- Restart postgreSQL:
sudo service postgresql restart
- Create a new database
sudo –u postgres createdb mytestdb
- Run psql with the postgres user again:
psql –U postgres –W
- List the existing databases (your new database should be there now):
\l
Solution 6 - Linux
In MacOS, I followed the below steps to make it work.
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
Solution 7 - Linux
If you're running macOS like I am, you may not have the postgres user.
When trying to run sudo -u postgres psql
I was getting the error sudo: unknown user: postgres
Luckily there are executables that postgres provides.
createuser -D /var/postgres/var-10-local --superuser --username=nick
createdb --owner=nick
Then I was able to access psql
without issues.
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.
mkdir /var/postgres/var-10-local
pg_ctl init -D /var/postgres/var-10-local
Then I edited /var/postgres/var-10-local/postgresql.conf
with my preferred port, 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!
Solution 8 - Linux
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`
Solution 9 - Linux
You probably need to update your pg_hba.conf
file. This file controls what users can log in from what IP addresses. I think that the postgres user is pretty locked-down by default.
Solution 10 - Linux
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!