Blog

cPanel, postgresql and a default database issue with 8.4.x versions of postgres

Tags: cPaneldefault databasePostgreSQLtemplate1

Scenario:

This PostgreSQL database issue was there, because customer wanted a latest version of Postgresql, latest than what cPanel support by default.

Issue reported in the phppGAdmin page was

FATAL:  password authentication failed for user "cPanel_username"
FATAL:  permission denied for database "template1"
DETAIL:  User does not have CONNECT privilege.

Solution

# su - postgres
-bash-3.2$ psql
psql (8.4.2)
Type "help" for help.

postgres=# alter user cPanel_username with password 'password_here';
ALTER ROLE
postgres=# alter user cPanelusername_username with password 'password_here';

Check for issues in /var/lib/pgsql/pgstartup.log . That is where postgreSQL throws out errors which will be helpful in debugging the issues.

In prior releases, template1 was used both as a default connection for utilities like createuser, and as a template for new databases. This caused CREATE DATABASE to sometimes fail, because a new database cannot be created if anyone else is in the template database. With this change, the default connection database is now postgres, meaning it is much less likely someone will be using template1 during CREATE DATABASE.

Also, do change the postgresql configuration file of cPanel installation, /usr/local/cpanel/base/3rdparty/phpPgAdmin/conf/config.inc.php

Look for

$conf['servers'][0]['defaultdb'] = 'template1';

and change it to

$conf['servers'][0]['defaultdb'] = 'postgres';

Don’t forget to chattr also. BTW now a days, cPanel is intelligent enough to detect and remove the chattrs also :D.

Category : cPanel, PostgreSQL, Troubleshooting