3

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.

satorg
  • 850
  • 6
  • 8

0 Answers0