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