I noticed it on our PostgreSQL server (v12.7). There are two similar DBs with weirdly different access permissions:
postgres=> \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+---------+----------+-------------+-------------+-------------------------------------+-----------+------------+--------------------------------------------
postgres | skadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 8257 kB | pg_default | default administrative connection database
alpha | skadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | No Access | pg_default |
betta | skadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 5806 MB | pg_default |
Note that skadmin
is the owner for all of the DBs but has no access to alpha
only.
Of course this user is able to grant any access to any of these DBs:
postgres=> grant CONNECT on DATABASE alpha TO skadmin;
But it makes the privileges looking like
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+---------+----------+-------------+-------------+-------------------------------------+-----------+------------+--------------------------------------------
postgres | skadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 8257 kB | pg_default | default administrative connection database
alpha | skadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | skadmin=c/skadmin | 6971 MB | pg_default |
betta | skadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 5806 MB | pg_default |
If I revoke the CONNECT
privilege (or ALL
) on that DB from skadmin
then it won't be able to connect to there anymore (even though it is the owner).
So I'm wondering – what does make these two DBs behaving so differently? How come is it possible to connect to betta
without explicit privileges set whereas not possible to connect to alpha
without explicit privileges? These two DBs – alpha
and betta
are supposed to be identical in their schemas and access rights after all. Eventually I'd prefer not to have any explicit privileges granted to the owner at all.