Re: Revoke "drop database" even for superusers?

On 2012-11-09 12:42:37 +0100, Albe Laurenz wrote:
> Edson Richter wrote:
> > I've a bunch of databases that cannot be dropped in any case.
> >
> > I was wondering if it is possible to revoke "drop database" permission
> for all users, in order that
> > even superuser, if he wishes to drop a database, he will need first to
> "grant drop database" first.
> >
> > I know there is already a safety that does not allow dropping
> databases in use - I just want to make
> > even harder.
>
> You cannot play it over permissions, but what about the following trick:
>
> postgres=# CREATE DATABASE persist;
> CREATE DATABASE
> postgres=# UPDATE pg_database SET datistemplate=TRUE WHERE
> datname='persist';
> UPDATE 1
> postgres=# DROP DATABASE persist;
> ERROR: cannot drop a template database

Not a good idea, autovacuum will use a freeze_min_age of 0 in that
case which will make it heaps more expensive. Also it allows everyone to
copy that database not only its owner/superuser.
I think there might be even more unexpected consequences of playing that
trick.

Greetings,

Andres Freund