Surprising connection issue

Hi guys,

I don't know if what I'm going to explain you could be regarded as a
regular behaviour related issue... but I'm so surprised.

I'm working under the latest stable PostgreSQL version 12.3.

$ sudo /usr/lib/postgresql/12/bin/postgres --version
postgres (PostgreSQL) 12.3 (Debian 12.3-1.pgdg100+1)

And the contents of my pg_hba.conf is as follows,

$ sudo cat /etc/postgresql/12/main/pg_hba.conf | egrep -v
'^[[:space:]]*(#.*)?$' -
local all postgres peer
local all all peer
hostssl all all 127.0.0.1/32 scram-sha-256
hostssl all all ::1/128
scram-sha-256
local replication all peer
host replication all 127.0.0.1/32 md5
host replication all ::1/128 md5

(Obviously the 'password_encryption' variable is correctly set to
scram-sha-256)

Once connected, I created a one new user (user1) with superuser grant and a
new one other (user2) as described below,

$ psql
psql (12.3 (Debian 12.3-1.pgdg100+1))
Type "help" for help.

postgres=# CREATE USER user1 WITH SUPERUSER;
CREATE ROLE
postgres=# \password user1
Enter new password:
Enter it again:
postgres=# CREATE USER user2;
CREATE ROLE
postgres=# \password user2
Enter new password:
Enter it again:

Once done, I created a new db1 database... putting the user user1 as the
owner of it.

postgres=# CREATE DATABASE db1 OWNER user1;
CREATE DATABASE

postgres=# \du
List of roles
Role name | Attributes |
Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
user1 | Superuser | {}
user2 | | {}

postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access
privileges
-----------+----------+----------+-------------+-------------+-----------------------
db1 | user1 | UTF8 | ca_ES.UTF-8 | ca_ES.UTF-8 |
postgres | postgres | UTF8 | ca_ES.UTF-8 | ca_ES.UTF-8 |
template0 | postgres | UTF8 | ca_ES.UTF-8 | ca_ES.UTF-8 | =c/postgres
+
| | | | |
postgres=CTc/postgres
template1 | postgres | UTF8 | ca_ES.UTF-8 | ca_ES.UTF-8 | =c/postgres
+
| | | | |
postgres=CTc/postgres
(4 rows)

postgres=# \q

After that, I'm tried to make a connection to database db1 as a user user1.

$ psql -h localhost -d db1 -U user1
Password for user user1:
psql (12.3 (Debian 12.3-1.pgdg100+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits:
256, compression: off)
Type "help" for help.

db1=# \q

And my surprise went when I see the connection done while there is no user
granted to connect the database...

Ok -I thought- maybe because user1 is a superuser... or even maybe because
user1 is in fact the owner of the database db1.

So, I decided to try it again with another user (user2) !

$ psql -h localhost -d db1 -U user2
Password for user user2:
psql (12.3 (Debian 12.3-1.pgdg100+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits:
256, compression: off)
Type "help" for help.

db1=> \q
$

And honestly I don't know if I'm right now in front of a bug... or there is
some reason that explains all of this ?

--
David Gasa i Castell

Linux User #488832