Unable to Create a PostgreSQL Instance

In this blog post, we are going to discuss causes, reasons and the solutions which related to the following error “Unable to Create a PostgreSQL Instance”. Here are the error messages which you can receive:

Invalid PostgreSQL User Name and Password.

or

Connection to PostgreSQL server failed.

All PostgreSQL Server error messages that we are going to review in this article you can face during the PostgreSQL instance creation. Bellow you can find 4 causes and solution how to fix this or that error message. So let’s start!

Unable to Create a PostgreSQL Instance – Cause  1

7991 21550700 10/19 04:12:25 ### libpqInterface :: ConnectDb() - Connection Info string: port = '5432' dbname = 'postgresql' user = 'admin' connect_timeout = '' password = *******

7991 21550700 10/19 04:12:25 ### libpqInterface :: ValidateConnection() - eRRoR -- PQreset failed to reset the connection. Error:FATAL: no
pg_hba.conf entry for host "[local]", user "admin", database "postgresql", SSL off
FATAL: no pg_hba.conf entry for host "[local]", user "admin", database "postgresql", SSL off

“[local]” here is related to a UNIX socket connection. So, there is no record for pg_hba.conf file to enable the socket connection from cvd that generate psql -u postgres [username] from working. This can be tested from the client as root.

Solution 1

Make sure that pg_hba.conf file has an applicable line to match the user. With the help of the default line below all users can connect to the client via the UNIX socket, as part of a default PostgreSQL install. It may be hashed out.

local all all md5

As the alternative, you can use the following variation to make it secure:

local all [username_entered_in_gui_for_instance_creation] md5

With the help of this users can connect to all databases locally via the UNIX socket.

Unable to Create a PostgreSQL Instance – Cause 2

18041 1184f700 04/13 05:23:14 ### libpqInterface :: libpqInterface() - eRRoR -- Exception in [libpqInterface :: libpqInterface]
Type:[APPLICATION_EXCEPTION] Msg:[Lib dir is not present]

18041 1184f700 04/13 05:23:14 ### ExecutePostgre::init() - eRRoR -- Exception in [libpqInterface :: libpqInterface] Type:[APPLICATION_EXCEPTION] Msg:[Lib dir is not present] 

18041 1184f700 04/13 05:23:14 ### PostgresBrowseAgent::verifyUserInfo() - connection failed 

18041 1184f700 04/13 05:23:14 ### PostgresBrowseAgent::verifyUserInfo() - exepg.postgre_exe failed

Solution 2

Make sure the correct PostgreSQL lib directory is specified in the instance creation dialog. If it contains libpq.so library file and other related library files that everything is OK. The libpq.so library is used to execute queries and to connect to the database.

  • Execute the following command to locate the lib directory:
    bash-3.2# ./pg_config --libdir

    Here is the output example:

    /PostgreSQL/9.2/lib
  • If you have noticed that the lib path contains libpq.so.x.x instead of libpq.so, make a symlink libpq.so to libpq.so.x.x just before attempting instance creation.
    bash-3.2# ln -s /usr/lib64/libpq.so.x.x /usr/lib64/libpq.so

    Try again to create the instance, now you should be able to do it  successfully.

Unable to Create a PostgreSQL Instance – Cause 3

24576 1026b700 11/12 08:14:42 ### PostgresBrowseAgent::verifyUserInfo() -
m_PostgresServer->GetConfigFilePath() = Simpana

24576 1026b700 11/12 08:14:42 ### libpqInterface :: ConnectDb() - Connection Info string: port = '5432' dbname = 'database' user = 'admin' connection_timeout = '' password = ******** 

24576 1026b700 11/12 08:14:42 ### libpqInterface :: IsSuperUser() - eRRoR - Exception in [libpqInterface :: IsSuperUser] Type:[APPLCATION_EXCEPTION] Msg:[The user is not a super user] 

24576 1026b700 11/12 08:14:42 ### libpqInterface :: libpqInterface() - eRRoR - Exception in [libpqInterface :: IsSuperUser] Type:[APPLCATION_EXCEPTION] Msg:[The user is not a super user] 

24576 1026b700 11/12 08:14:42 ###ExecutePostgre::init() - eRRoR - Exception in [libpqInterface :: IsSuperUser] ] Type:[APPLCATION_EXCEPTION] Msg:[The user is not a super user] 

24576 1026b700 11/12 08:14:42 ### PostgresBrowseeAgent::verifyUseerInfo() - connection failed 

24576 1026b700 11/12 08:14:42 ###PostgresBrowseeAgent::verifyUseerInfo() - exepg.postgre.exe failed 

24576 1026b700 11/12 08:14:42 ### PostgresBrowseeAgent::BrowseDBs() - verifyUser failed (err=) 

24576 1026b700 11/12 08:14:42 ### PostgresBrowseeAgent::DoBrowse() - BrowseDBs failed. M_errorNum = 1577058305 

24576 1026b700 11/12 08:14:42 ### PostgresBrowseeAgent::SendBrowseResponse() - Sending mstype = POSTGRESQL_BROWSE_FAILED 

24576 1026b700 11/12 08:14:42 ### Main() - DoBrowse() failed

There is missing special attribute usesuper or superuser in the instance creation dialog box.

Solution 3

Here are the simple steps how you can add the superuser attribute to the user:

  1. Connect to PostgreSQL and check if the usesuper attribute is incorrectly set using the following query to:
    select * from pg_user;
  1. After you have connected to PostgreSQL as superuser, run the query bellow to add the attribute:
    PGSQL> ALTER USER username WITH superuser;
  2. Check all using the following command:
    select * from pg_user;

Unable to Create a PostgreSQL Instance – Cause 4

25925 ae3d6740 09/10 06:12:57 ### libpqInterface :: ConnectDb() - Connection Info string: port = '5433' dbname = 'postgresql' user = 'admin' connect_timeout = ' password = *******

25925 ae3d6740 09/10 06:12:57 ### libpqInterface :: ValidateConnection() - eRRoR -- PQreset failed to reset the connection. Error:FATAL: Peer authentication failed for user "postgres" FATAL: Peer authentication failed for user "postgres" 

25925 ae3d6740 09/10 06:12:57 ### libpqInterface :: ValidateConnection() - eRRoR -- Exception in [libpqInterface :: ValidateConnection] Type:[POSTGRE_EXCEPTION] Msg:[PQreset failed to reset the connection] 

25925 ae3d6740 09/10 06:12:57 ### libpqInterface :: libpqInterface() - eRRoR -- Exception in [libpqInterface :: ValidateConnection] Type:[POSTGRE_EXCEPTION] Msg:[PQreset failed to reset the connection] 

25925 ae3d6740 09/10 06:12:57 ### ExecutePostgre::init() - eRRoR -- Exception in [libpqInterface :: ValidateConnection] Type:[POSTGRE_EXCEPTION] Msg:[PQreset failed to reset the connection] 

25925 ae3d6740 09/10 06:12:57 ### PostgresBrowseAgent::verifyUserInfo() - connection failed 25925 ae3d6740 09/10 06:12:57 ### PostgresBrowseAgent::verifyUserInfo() - exepg.postgre_exe failed 

25925 ae3d6740 09/10 06:12:57 ### PostgresBrowseAgent::BrowseDBs() - verifyUser failed (err=)

Solution 4

The software tries to connect to PostgreSQL as a root user while instance creating. Add the following mappings pg_hba.conf and the pg_ident.conf files to make authentication successful.

  • You need to add corresponding map name configured for the PostGres DB user in the pg_hba.conf file. Look at the example bellow.
    # TYPE    DATABASE    USER    ADDRESS    METHOD
    
    # "local" is for Unix domain socket connections only
    
    local    all    postgres    peer    map=testmap
  • Now add the mapping between the PostGres DB user and the system user ‘root’ in the pg_ident.conf file. Look at the following example.
    # Put your actual configuration here
    
    # ----------------------------------
    
    # MAPNAME    SYSTEM-USERNAME    PG-USERNAME
    
    testmap    postgres    postgres
    
    testmap    root    postgres

With the help of the next command reload PostgreSQL Server:

./pg_ctl -D /opt/PostgreSQL/9.1/data reload
[Total: 2    Average: 4/5]

Leave a Reply

Your email address will not be published. Required fields are marked *