Re: "Failed to connect to Postgres database" - Mailing list pgsql-general

On 9/27/19 5:58 AM, Marco Ippolito wrote:
> Thanks Daniel.
> After adding the password, now ssh connection to the cluster fabmnet works:

You might want to take a look at:

https://help.ubuntu.com/lts/serverguide/postgresql.html


> And may be the fact the it's compulsory to add a password is testified
> also by the fact that changing the ownership of the database while
> adding a password, lets connect with ssh to the database:

First it is SSL.
Second password and SSL are two different things. This is covered in the
auth file pg_hba.conf:

https://www.postgresql.org/docs/11/auth-pg-hba-conf.html

What you are seeing below is dependent on whether you connect using a
host(-h localhost) or a socket(no -h). That behavior is in turn
determined by the settings in pg_hba.conf.

Also to help down the road when you are setting up the fabric-ca server
you need to remember you are now running two Postgres servers:

Ver Cluster Port Status Owner    Data directory                 Log file
11  fabmnet 5433 online postgres /var/lib/postgresql/11/fabmnet
/var/log/postgresql/postgresql-11-fabmnet.log
11  main    5432 online postgres /var/lib/postgresql/11/main
/var/log/postgresql/postgresql-11-main.log

The most important part is that the fabric server needs to connect to
the one using port 5433.  FYI, this also means that it is not necesssary
to use the --cluster option to psql. Just set the appropriate port -p
5432 for maon and -p 5433 for fabmnet.

More below.
>
> postgres=# CREATE USER fabmnet_admin;
> CREATE ROLE
> postgres=# ALTER USER fabmnet_admin WITH PASSWORD 'A';
> ALTER ROLE
>
> postgres=# ALTER DATABASE fabmnet_ca OWNER TO fabmnet_admin;
> ALTER DATABASE
> postgres=# \l
>                                   List of databases
>      Name    |     Owner     | Encoding | Collate |  Ctype  |   Access
> privileges
> ------------+---------------+----------+---------+---------+-----------------------
>   fabmnet_ca | fabmnet_admin | UTF8     | C.UTF-8 | C.UTF-8 |
>   postgres   | postgres      | UTF8     | C.UTF-8 | C.UTF-8 |
>   template0  | postgres      | UTF8     | C.UTF-8 | C.UTF-8 |
> =c/postgres          +
>              |               |          |         |         |
> postgres=CTc/postgres
>   template1  | postgres      | UTF8     | C.UTF-8 | C.UTF-8 |
> =c/postgres          +
>              |               |          |         |         |
> postgres=CTc/postgres
> (4 rows)
>
> (base) postgres@pc:~$ psql -h localhost --cluster 11/fabmnet
> Password for user postgres:
> psql: FATAL:  password authentication failed for user "postgres"
> FATAL:  password authentication failed for user "postgres"

This failed because you did not specify a database or username, so by
default psql used the system user(postgres) as the database name and the
user name. I'm guesing you do not have a password set up for the
postgres user yet. Pretty sure if you left off the -h localhost you
would have connected as Ubuntu sets up trust authentication for postgres
user on local socket.

> (base) postgres@pc:~$ psql -h localhost --cluster 11/fabmnet -d
> fabmnet_ca -U fabmnet_admin
> Password for user fabmnet_admin:
> psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1))
> SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits:
> 256, compression: off)
> Type "help" for help.
> fabmnet_ca=>
>
>
> Now I have to fix the interface between fabric-ca and postgresql-11 on
> both sides. And I will let you know how it is going
>
> Marco
>
> Il giorno ven 27 set 2019 alle ore 13:34 Daniel Verite
> <daniel@manitou-mail.org <mailto:daniel@manitou-mail.org>> ha scritto:
>
>              Marco Ippolito wrote:
>
>      > (base) postgres@pc:~$ psql --cluster 11/fabmnet -h localhost
>      > Password for user postgres:
>      > psql: FATAL:  password authentication failed for user "postgres"
>      > FATAL:  password authentication failed for user "postgres"
>
>     Did you set a password for the postgres user in that newly created
>     cluster?
>     If not, try psql --cluster 11/fabmnet (without -h localhost),
>     it should connect you without a password,
>     then set a password with the \password command in psql,
>     then try again with -h localhost.
>
>
>     Best regards,
>     --
>     Daniel Vérité
>     PostgreSQL-powered mailer: http://www.manitou-mail.org
>     Twitter: @DanielVerite
>


--
Adrian Klaver
adrian.klaver@aklaver.com