Thread: Revoke "drop database" even for superusers?

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.

Regards,

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

Yours,
Laurenz Albe

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

Yours,
Laurenz Albe

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

Andres Freund <andres@anarazel.de> writes:
> On 2012-11-09 12:42:37 +0100, Albe Laurenz wrote:
>> 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.

It seems pretty pointless in any case, since a superuser could just undo
the UPDATE and then drop the database.

If what you're trying to avoid is accidental, rather than malicious,
drops, here's something that would be a lot more useful: avoid using
superuser accounts as much as you possibly can.  There are not that
many things that you really need to use superuser privileges for.

            regards, tom lane

Em 09/11/2012 20:18, Tom Lane escreveu:
> Andres Freund <andres@anarazel.de> writes:
>> On 2012-11-09 12:42:37 +0100, Albe Laurenz wrote:
>>> 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.
> It seems pretty pointless in any case, since a superuser could just undo
> the UPDATE and then drop the database.
>
> If what you're trying to avoid is accidental, rather than malicious,
> drops, here's something that would be a lot more useful: avoid using
> superuser accounts as much as you possibly can.  There are not that
> many things that you really need to use superuser privileges for.
>
>             regards, tom lane
>
>
Thanks you.

I'll look documentation to create a user that has enough permission to
do everything needed, except dropping databases.

Regards,

Edson

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
takecare 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.


--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com

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.

On Fri, 2012-11-23 at 23:56 -0200, Edson Richter wrote:
> Wordeful!
>
> Guillaume, Thanks.
>
> I"ll give a try for few weeks in the development and test databases
> before put in production.
>

Make sure you test it thoroughly. As I said, it's more an example code,
than a production-ready code.

If you find any issues with it, please tell me so that I can fix the
code.


--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com

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
takecare 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

On Sun, Dec 2, 2012 at 10:20 AM, Edson Richter <edsonrichter@hotmail.com> wrote:
> 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.

The most common thing to do before running 'make' is to look for a
configure script:

$ ./configure
$ make

ChrisA

Em 01/12/2012 22:22, Chris Angelico escreveu:
> On Sun, Dec 2, 2012 at 10:20 AM, Edson Richter <edsonrichter@hotmail.com> wrote:
>> 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.
> The most common thing to do before running 'make' is to look for a
> configure script:
>
> $ ./configure
> $ make
>
> ChrisA
>
>
Yes, that was my first tought. But there is no configure script in the
folder.
Must be something else...

Edson

--
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee

On Sun, 2012-12-02 at 10:32 +0530, Pavan Deolasee wrote:
> On Sun, Dec 2, 2012 at 7:53 AM, Edson Richter <edsonrichter@hotmail.com>wrote:
>
> > Em 01/12/2012 22:22, Chris Angelico escreveu:
> >
> >  On Sun, Dec 2, 2012 at 10:20 AM, Edson Richter <edsonrichter@hotmail.com>
> >> wrote:
> >>
> >>> 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.
> >>>
> >> The most common thing to do before running 'make' is to look for a
> >> configure script:
> >>
> >> $ ./configure
> >> $ make
> >>
> >> ChrisA
> >>
> >>
> >>  Yes, that was my first tought. But there is no configure script in the
> > folder.
> > Must be something else...
> >
> >
> While I haven't looked at the code, the error message looks very similar to
> what you will see while building contrib modules. So try this:
>
> $ export USE_PGXS=1
> $ export PATH=$path_to_your_pg_config$:$PATH
>
> Set the PATH so that the correct pg_config command is used. It must come
> from the same installation that your server is running.
>
> $ make clean
> $ make
> $ make install
>

Yeah, you need to use the USE_PGXS environment variable. You also need
the pg_config tool and PostgreSQL header files which should be available
if you install the -devel package of your distribution.

What distribution are you on?


--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com

Em 02/12/2012 07:53, Guillaume Lelarge escreveu:
> On Sun, 2012-12-02 at 10:32 +0530, Pavan Deolasee wrote:
>> On Sun, Dec 2, 2012 at 7:53 AM, Edson Richter <edsonrichter@hotmail.com>wrote:
>>
>>> Em 01/12/2012 22:22, Chris Angelico escreveu:
>>>
>>>   On Sun, Dec 2, 2012 at 10:20 AM, Edson Richter <edsonrichter@hotmail.com>
>>>> wrote:
>>>>
>>>>> 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.
>>>>>
>>>> The most common thing to do before running 'make' is to look for a
>>>> configure script:
>>>>
>>>> $ ./configure
>>>> $ make
>>>>
>>>> ChrisA
>>>>
>>>>
>>>>   Yes, that was my first tought. But there is no configure script in the
>>> folder.
>>> Must be something else...
>>>
>>>
>> While I haven't looked at the code, the error message looks very similar to
>> what you will see while building contrib modules. So try this:
>>
>> $ export USE_PGXS=1
>> $ export PATH=$path_to_your_pg_config$:$PATH
>>
>> Set the PATH so that the correct pg_config command is used. It must come
>> from the same installation that your server is running.
>>
>> $ make clean
>> $ make
>> $ make install
>>
> Yeah, you need to use the USE_PGXS environment variable. You also need
> the pg_config tool and PostgreSQL header files which should be available
> if you install the -devel package of your distribution.
>
> What distribution are you on?
>
Centos 5.8, Centos 6.3 - both 64bit.

On Sun, 2012-12-02 at 08:09 -0200, Edson Richter wrote:
> Em 02/12/2012 07:53, Guillaume Lelarge escreveu:
> > On Sun, 2012-12-02 at 10:32 +0530, Pavan Deolasee wrote:
> >> On Sun, Dec 2, 2012 at 7:53 AM, Edson Richter <edsonrichter@hotmail.com>wrote:
> >>
> >>> Em 01/12/2012 22:22, Chris Angelico escreveu:
> >>>
> >>>   On Sun, Dec 2, 2012 at 10:20 AM, Edson Richter <edsonrichter@hotmail.com>
> >>>> wrote:
> >>>>
> >>>>> 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.
> >>>>>
> >>>> The most common thing to do before running 'make' is to look for a
> >>>> configure script:
> >>>>
> >>>> $ ./configure
> >>>> $ make
> >>>>
> >>>> ChrisA
> >>>>
> >>>>
> >>>>   Yes, that was my first tought. But there is no configure script in the
> >>> folder.
> >>> Must be something else...
> >>>
> >>>
> >> While I haven't looked at the code, the error message looks very similar to
> >> what you will see while building contrib modules. So try this:
> >>
> >> $ export USE_PGXS=1
> >> $ export PATH=$path_to_your_pg_config$:$PATH
> >>
> >> Set the PATH so that the correct pg_config command is used. It must come
> >> from the same installation that your server is running.
> >>
> >> $ make clean
> >> $ make
> >> $ make install
> >>
> > Yeah, you need to use the USE_PGXS environment variable. You also need
> > the pg_config tool and PostgreSQL header files which should be available
> > if you install the -devel package of your distribution.
> >
> > What distribution are you on?
> >
> Centos 5.8, Centos 6.3 - both 64bit.
>

OK, so you're using RPMs. So you need a package called
postgresql92-devel-9.2.1. For example, on a CentOS 5, with the 9.2
release, you'll need
http://yum.postgresql.org/9.2/redhat/rhel-5-x86_64/repoview/postgresql92-devel.html.


--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com