Re: Revoke "drop database" even for superusers?

Wordeful!

Guillaume, Thanks.

I"ll give a try for few weeks in the development and test databases
before put in production.

Regards,

*Edson Carlos Ericksson Richter*
/SimKorp Informática Ltda/
Fone: (51) 3366-7964
Celular: (51)9318-9766/(51) 8585-0796
Embedded Image

Em 23/11/2012 19:18, Guillaume Lelarge escreveu:
> On Fri, 2012-11-09 at 09:19 -0200, 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 can also use the hook system to add this feature to PostgreSQL
> (without changing PostgreSQL code). With the code available on
> https://github.com/gleu/Hooks-in-PostgreSQL/tree/master/examples/deny_drop, you can have a shared library that will take care of denying the drop of a database.
>
> Once compiled and intalled, you need to change the postgresql.conf file
> with this new setting:
>
> shared_preload_libraries = 'deny_drop'
>
> After you restart PostgreSQL, it should work like this:
>
> $ psql postgres
> psql (9.2.1)
> Type "help" for help.
>
> postgres=# create database tryme;
> CREATE DATABASE
> postgres=# drop database tryme;
> ERROR: cannot drop a database!
> postgres=# set deny_drop.iknowwhatiamdoing to true;
> SET
> postgres=# drop database tryme;
> DROP DATABASE
> postgres=# \q
>
> AFAICT, this code has never been used in production, but it's so simple
> I don't think you risk anything using it.
>
> Anyway, it's still better to actually use the user's permissions to deny
> him to drop databases. But this little shared library may still be
> usefull.
>
>