Wordeful!
Guillaume, Thanks. I"ll give a try for few weeks in the development and test databases before put in production. Regards,
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. |
- [GENERAL] Revoke "drop database" even for supe... Edson Richter
- Re: [GENERAL] Revoke "drop database" even... Albe Laurenz
- Re: [GENERAL] Revoke "drop database" ... Edson Richter
- Re: [GENERAL] Revoke "drop database" ... Andres Freund
- Re: [GENERAL] Revoke "drop database&qu... Tom Lane
- Re: [GENERAL] Revoke "drop databas... Edson Richter
- Re: [GENERAL] Revoke "drop database" even... Guillaume Lelarge
- Re: [GENERAL] Revoke "drop database" ... Edson Richter
- Re: [GENERAL] Revoke "drop database&qu... Guillaume Lelarge
- Re: [GENERAL] Revoke "drop database" ... Edson Richter
- Re: [GENERAL] Revoke "drop database&qu... Chris Angelico
- Re: [GENERAL] Revoke "drop databas... Edson Richter
- Re: [GENERAL] Revoke "drop da... Pavan Deolasee
- Re: [GENERAL] Revoke "dro... Guillaume Lelarge
- Re: [GENERAL] Revoke "... Edson Richter
- Re: [GENERAL] Revoke "... Guillaume Lelarge