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

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

Ralf Jung <post@ralfj.de> writes:
> I would have expected a "REVOKE ALL ON DATABASE template1" to have the effect of
> changing the default permissions for new databases.

This is not a bug, and I don't think it's a reasonable expectation either.
There's certainly plenty of reasons why you might wish to lock people out
of template1, but that doesn't equate to supposing that people should be
locked out of every new database.  Nor do we copy most other
database-level attributes when cloning a database (the exceptions are
things that affect the database contents, such as encoding).

There might be an argument for extending ALTER DEFAULT PRIVILEGES so that
it can control the initial default privileges for new databases.  That's
certainly a feature request not a bug though.

            regards, tom lane

On 15/05/18 03:43, Tom Lane wrote:
> Ralf Jung <post@ralfj.de> writes:
>> I would have expected a "REVOKE ALL ON DATABASE template1" to have the effect of
>> changing the default permissions for new databases.
> This is not a bug, and I don't think it's a reasonable expectation either.
> There's certainly plenty of reasons why you might wish to lock people out
> of template1, but that doesn't equate to supposing that people should be
> locked out of every new database.  Nor do we copy most other
> database-level attributes when cloning a database (the exceptions are
> things that affect the database contents, such as encoding).
>
> There might be an argument for extending ALTER DEFAULT PRIVILEGES so that
> it can control the initial default privileges for new databases.  That's
> certainly a feature request not a bug though.
>
>             regards, tom lane
>
Would definitely agree with Tom on both points!


Cheers,
Gavin

Hi Tom,

thanks for your fast response!

On 14.05.2018 17:43, Tom Lane wrote:
> Ralf Jung <post@ralfj.de> writes:
>> I would have expected a "REVOKE ALL ON DATABASE template1" to have the effect of
>> changing the default permissions for new databases.
> 
> This is not a bug, and I don't think it's a reasonable expectation either.
> There's certainly plenty of reasons why you might wish to lock people out
> of template1, but that doesn't equate to supposing that people should be
> locked out of every new database.  Nor do we copy most other
> database-level attributes when cloning a database (the exceptions are
> things that affect the database contents, such as encoding).

Being entirely new to PostgreSQL, I don't really understand why e.g. schema
properties of the template are preserved but database properties are not.  I
thought that's the entire point of this template DB.  I did not even think of
this "REVOKE ALL" as "lock people out of the template"; just like "REVOKE ALL ON
SCHEMA public" (executed in DB template1) conceptually doesn't mean to me "lock
people out of the public schema in this template DB", it means "lock people out
of the public schema of all DBs created in the future".

But then I also don't even know what other database properties there are, so
this misunderstanding probably stems from my lack of experience.

> There might be an argument for extending ALTER DEFAULT PRIVILEGES so that
> it can control the initial default privileges for new databases.  That's
> certainly a feature request not a bug though.

I haven't yet fully understood the interaction between defining default
privileges in template1 vs. defining them with "ALTER DEFAULT PRIVILEGES", but
any way to make sure that new databases are properly locked down would be useful.

Kind regards,
Ralf