Can't connect to PostgreSQL in pqxx C++

Question

when trying to connect to the database the following error appears

error text

terminate called after throwing an instance of 'pqxx::broken_connection'
  what():  connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  role "zxctatar" does not exist

code

#include <iostream>
#include "../../include/Database.h"

Database::Database()
{
    createDatabase();
}

void Database::createDatabase()
{
    try
    {
        pqxx::connection connection_to_postgres_("host=127.0.0.1 port=5432 dbname=pg_database user=admin password=15900512"); // подклбчение к postgres серверу

        if(connection_to_postgres_.is_open())
        {
        }
    }
    catch (const std::exception &e)
    {
        std::cout << e.what() << std::endl;
    }
}

after googling people wrote that you need to change pg_hba.conf, in the end I changed it like this (I think there might be a mistake somewhere here)

pg_hba.conf

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   all             all                                     md5
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5
host    all             all             0.0.0.0/0               md5

I also changed listen_address in postgresql.conf

postgresql.conf

listen_addresses = '*'

user admin created in postgresql

postgresql

                             List of roles
 Role name |                         Attributes                         
-----------+------------------------------------------------------------
 admin     | Superuser
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS

the server also works correctly on port 5432

ss -plnt | grep 5432

LISTEN 0      200          0.0.0.0:5432      0.0.0.0:*    users:(("postgres",pid=20277,fd=6))       
LISTEN 0      200             [::]:5432         [::]:*    users:(("postgres",pid=20277,fd=7))   

OS Ubuntu 24.04.1 LTS

UPD 1.0

Commands that work in the terminal

psql "host=127.0.0.1 port=5432 dbname=postgres user=postgres password=15900521"

psql "hostaddr=127.0.0.1 port=5432 dbname=postgres user=postgres password=15900521"

psql -U postgres -h localhost -d postgres

in this case, no errors occur, but if I take the first command and place all the same data in pqxx::connect, an error will occur

error text

terminate called after throwing an instance of 'pqxx::broken_connection'
  what():  connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: fe_sendauth: no password supplied

UPD 2.0

it turned out that pqxx doesn't work in a separate class, now I've moved the code with the connection to the server to main and everything works as it should, then the question is why doesn't it work in a separate class?

int main()
{
    try
    {
        pqxx::connection C("dbname=postgres user=postgres password=180305Vfcz hostaddr=127.0.0.1");

        if(C.is_open())
        {
            std::cout << "nice";
        }
    }
    catch (const std::exception &e)
    {
        std::cout << e.what() << std::endl;
    }

    return 0;
}

in this case everything is connected as it should

UPD FINAL

the problem is solved, all that was needed was to add a destructor to the class...

Answer 1

Check this steps:

CREATE ROLE zxctatar WITH LOGIN;
GRANT CONNECT ON DATABASE pg_database TO zxctatar;
GRANT CONNECT ON DATABASE postgres TO zxctatar;

CREATE ROLE admin WITH LOGIN PASSWORD '15900512';
GRANT CONNECT ON DATABASE pg_database TO admin;
GRANT CONNECT ON DATABASE postgres TO admin;

https://www.postgresql.org/docs/current/sql-createrole.html

pg_hba.conf:

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

Actually this should work;

int main()
{
    try
    {
        pqxx::connection C("");
        // pqxx::connection C("dbname=somename");
        // pqxx::connection C("dbname=somename user=someuser");
        // pqxx::connection C("dbname=somename user=someuser password=somepassword");
        if(C.is_open())
        {
            std::cout << "nice";
        }
    }
    catch (const std::exception &e)
    {
        std::cout << e.what() << std::endl;
    }

    return 0;
}