Setting up the PostgreSQL database

Set up a PostgreSQL database for Icinga 2

export PGPASSWORD=icinga
psql -U icinga -d icinga < /usr/share/icinga2-ido-pgsql/schema/pgsql.sql

I get the following message.

psql: FATAL: Peer authentication failed for user “icinga”

Hi,

that requires proper permissions specified in your pg_hba.conf file. Which PostgreSQL version and OS is involved here?

Cheers,
Michael

Pemissions are set as written in the documentation at:

Setting up the PostgreSQL database[¶

https://icinga.com/docs/icinga2/latest/doc/02-getting-started/#setting-up-the-postgresql-database)

I know what’s written in the docs, I’d like to see your config. Also whether they have been reloaded after editing the file - which command did you use?

Could you specify which logs or config files you need ?

Depending on your system, pg_hba.conf and the output of systemctl reload postgresql and its log from /var/log/postgresql. This may differ between Debian/Ubuntu, RHEL and SLES.

You should add -h “hostname” when importing the database schema. That fixed it for me

2 Likes

I am also having same error, Can you please help me here

Like already said, I would assume that adding -h … could help.

For example:

psql -h 127.0.0.1 -U icinga -d icinga < /usr/share/icinga2-ido-pgsql/schema/pgsql.sql

Otherwise, instead of 127.0.0.1 also localhost might be possible.

Changing this option and argument could already be enough to get the command working.

Additional:

The problem commonly occurs in conjunction with settings in the pg_hba.conf, where additional settings may be required.

Snipplet of the pg_hba.conf:

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5

It might be required to add/edit this line. Probably, instead of md5, only peer is set.

Please be aware, that a misconfigured pg_hba.conf might block the postgresql server from being started.

Yes, I had the same issue - adding -h (localhost in my case) resolved this quickly.