"REVOKE ... ON DATABASE template1 ..." has no effect

From: Ralf Jung <post(at)ralfj(dot)de>
To: pgsql-bugs(at)postgresql(dot)org
Subject: "REVOKE ... ON DATABASE template1 ..." has no effect
Date: 2018-05-14 09:15:44
Message-ID: 97da163c-c3fd-8348-d8b4-176c776d9cae@ralfj.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hi all,

I am trying to properly harden my postgres installations with the goal of users
having no access whatsoever unless explicitly granted. (TBH I was quite shocked
to learn that this is something I need to even do, I am used to more secure
defaults.) So, following
<https://wiki.postgresql.org/wiki/Shared_Database_Hosting>, I ran these commands
in template1:

REVOKE ALL ON DATABASE template1 FROM public;
REVOKE ALL ON SCHEMA public FROM public;

The default access to the public schema was successfully revoked, but the
"REVOKE ... ON DATABASE" seems to have no effect: I continued doing "CREATE
DATABASE test" (as the postgres user), and then tried to connect to "test" with
my test user, which worked.

I would have expected a "REVOKE ALL ON DATABASE template1" to have the effect of
changing the default permissions for new databases. I am not even alone in this
expectation, see e.g. <https://dba.stackexchange.com/a/17792>. Because the
actual behavior differs from the expected behavior, I am reporting this as a bug.

This is using PostgreSQL 10.3 on Debian testing.

Kind regards,
Ralf

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message PG Bug reporting form 2018-05-14 09:22:41 BUG #15195: Creation two subscription with the same name in different databases
Previous Message Kyotaro HORIGUCHI 2018-05-14 06:59:13 Re: [HACKERS] [BUGS] Bug in Physical Replication Slots (at least 9.5)?