Postgresql & OpenBSD first connexion

Question

I don't understand how Postgres works with openBSD. I didn't have these problems with debian (I don't have to do the initdb).

I did as follow

pkg_add postgresql-server php-pgsql
su - _postgresql
initdb -D /var/postgresql/data -U postgres - E UTF8 -A md5 -W

But after that, I don't have the result that I expect

I can start the database with

 pg_ctl -D /var/postgresql/data/ -l logfile start

or with

 rcctl enable postgresql 
 rcctl start postgresql

But I don't understand how to connect to it

Because if I do:

# su - _postgresql        
$ psql
Password: 
psql: FATAL:  password authentication failed for user "_postgresql"

Why it's the _postgresql user and not postgres? Which password I'm supposed to use?

This is the pg_hba.conf, I changed the end,

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            
password
# IPv6 local connections:
host    all             all             ::1/128                 md5

So with this, I think, i'm login but I have a new probleme, there is really something I don't understand

$ pg_ctl -D /var/postgresql/data/ -l logfile stop 
waiting for server to shut down.... done
server stopped
$ pg_ctl -D /var/postgresql/data/ -l logfile start
server starting
$ psql
psql: FATAL:  role "_postgresql" does not exist

thanks

Because the (unix-)username has a leading underscore, the postgres-username does not. If you don't supply a username, psql assumes you want to use your unix-username. `psql -U postgres` will probably do what you want. — wildplasser, Oct 11 '17 at 14:55

remy_dev · Accepted Answer · 2017-10-12T07:57:48.163

So the normal case when a create a new database is

With the user

su - _postgresql


initdb -D /var/postgresql/data -U postgres -k -E UTF8 -A md5 -W

and you have to chose a password

Start the database

pg_ctl -D /var/postgresql/data/ -l logfile start

And you can easily login with

psql -U postgres

But if it didn't work, i suppose i did a mistake with the password

With the user

su - _postgresql

First I need to change the ph_hba.conf to trust

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     trust

so when I start the database with

pg_ctl -D /var/postgresql/data/ -l logfile start

so I can login with the postgres user

psql -U postgres

And finaly change the password

ALTER USER postgres WITH PASSWORD '123';

Don't forget the " ; " at the end!

Change the pg_hba.conf file First I need to change the ph_hba.conf to password (or maybe MD5)

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                   password

And restart

pg_ctl -D /var/postgresql/data/ -l logfile stop
pg_ctl -D /var/postgresql/data/ -l logfile start

And i can finaly login properly

psql -U postgres

Postgresql & OpenBSD first connexion

1 Answers1