This article describes the specific setup needed in order for the root user on your machine to be able to login to PostgreSQL without entering a password for the connection.
Each cPanel server comes with the following cron job:
If there is no PostgreSQL installed for the server this specific cron job works for the MySQL database only.
In case PostgreSQL is installed for the server the expected behavior for the root user is to be able to connect to the postgre databases without supplying a password each time. In order for this to be possible MD5 authentication should be enabled instead of ident authentication.
How to enable MD5 authentication for PostgreSQL
Here are the steps that needs to be taken in order to allow connection for the root user to PostgreSQL without requesting a password.
- su - postgres
- psql -Upostgres template1
- alter user postgres with password 'NEWPASSHERE';
- vi /var/lib/pgsql/data/pg_hba.conf
- add the following lines in the pg_hba.conf file (note that those are all rules that you might need for 1h products PostgreSQL authentication):
local cpustats cpustats_cpanel md5 host cpustats cpustats_cpanel 127.0.0.1 255.255.255.255 md5 host cpustats cpustats_cpanel ::1/128 md5 local cpustats cpustats_local md5 host cpustats cpustats_local 127.0.0.1 255.255.255.255 md5 host cpustats cpustats_local ::1/128 md5 local portal_local portal_local md5 host portal_local portal_local 127.0.0.1 255.255.255.255 md5 host portal_local portal_local ::1/128 md5
- Comment out all lines ending with ident
- cat > /var/lib/pgsql/.pgpass
- chmod 600 /var/lib/pgsql/.pgpass
- chown postgres: /var/lib/pgsql/.pgpass
- vi /root/.pgpass
- add the following line in the /root/.pgpass file:
- chmod 600 /root/.pgpass
- chown root: /root/.pgpass
- /etc/init.d/postgresql restart
That is all. You should now be able to login without being asked for password using the root user:
psql -Upostgres template1