CODEWITHSUNDEEP
linuxdatabasepostgresqlconfiguration
Rohit Banga
Rohit Banga

Reputation: 18918

How to configure postgresql for the first time?

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?

Upvotes: 227

Views: 351194

Answers (13)

SuperNova
SuperNova

Reputation: 27436

The below commands helped me setup the database post installation

ubuntu@server1:~$ sudo -i -u postgres
postgres@server1:~$ 
postgres@server1:~$ 
postgres@server1:~$ psql
psql (12.18 (Ubuntu 12.18-0ubuntu0.20.04.1))
Type "help" for help.

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

Upvotes: 0

Vidura Adikari
Vidura Adikari

Reputation: 648

After installing PostgresSQL version 16 these were the steps.

To check the server status:

sudo systemctl status postgresql

If you are using a tool like pgAdmin you have to setup a password for postgres user. To do this login as postgres user.

sudo -u postgres psql

To set a password for postgres user

\password postgres

postgres in this command is the username not the password. Then it will prompt Enter new password for user "postgres":

Finally \q to exit.

Upvotes: 0

bl3ssedc0de
bl3ssedc0de

Reputation: 995

In my case, I did not know how to enter postgresql for the first time, in Ubuntu 22.04 after a recent installation, for me the command sudo -u postgres psql worked.

asciicast

Upvotes: 0

SuperNova
SuperNova

Reputation: 27436

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

Upvotes: 11

Manur
Manur

Reputation: 8743

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

  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.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.

  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.conf file (see step 3 above), and change "peer" to "md5" on the line concerning "all" other users:

    local      all     all     peer md5

  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.

Upvotes: 364

Robert
Robert

Reputation: 32745

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

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

sudo apt-get install postgresql

  1. Run psql using the postgres user

sudo –u postgres psql postgres

  1. Set a new password for the postgres user:

\password postgres

  1. Exit psql

\q

  1. 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

  1. Restart postgreSQL:

sudo service postgresql restart

  1. Create a new database

sudo –u postgres createdb mytestdb

  1. Run psql with the postgres user again:

psql –U postgres –W

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

\l

Upvotes: 14

Suman Astani
Suman Astani

Reputation: 1191

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`

Upvotes: 4

Evan Carroll
Evan Carroll

Reputation: 1

There are two methods you can use. Both require creating a user and a 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 postgres user,

    SHOW port;

    Or,

    $ grep "port =" /etc/postgresql/*/main/postgresql.conf

Sidenote: the postgres user

I suggest NOT modifying the postgres user.

  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.

Upvotes: 40

Nick Woodhams
Nick Woodhams

Reputation: 12677

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!

Upvotes: 4

Wellington Ribeiro
Wellington Ribeiro

Reputation: 25

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!

Upvotes: -1

Oliver Weichhold
Oliver Weichhold

Reputation: 10296

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.

Upvotes: 65

Admdebian
Admdebian

Reputation: 660

This is my solution:

su root
su postgres
psql

Upvotes: 23

Neall
Neall

Reputation: 27114

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.

Upvotes: 3

Related Questions