Surprising connection issue

[David Gasa i Castell <dgasacas7@xxxxxxxxx>]

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