From: | David Gasa i Castell <dgasacas7(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Surprising connection issue |
Date: | 2020-07-14 15:25:24 |
Message-ID: | CANh8eme9wJh8MoZCwN7uFTUaPtZOwcx7Tg50bo8iopP4jn7+ZQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Marc Millas | 2020-07-14 15:26:33 | Re: some random() clarification needed |
Previous Message | David G. Johnston | 2020-07-14 15:24:36 | Re: some random() clarification needed |