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

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