Re: Revoke "drop database" even for superusers?

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.
>
>
Can you give me a quick intro on how to compile this module for PostgreSQL?
I'm complete noob in C development for Linux, and I'm using CentOS 5.8
and CentOS 6.3, both 64 bit.
I already have C compiler installed, kernel sources, etc.

I've put both files in ~/deny_drop folder, and executed "make":

# LANG=C make
Makefile:13: ../../src/Makefile.global: No such file or directory
Makefile:14: /contrib/contrib-global.mk: No such file or directory
make: *** No rule to make target `/contrib/contrib-global.mk'. Stop.

Regards,

Edson