Revoke "drop database" even for superusers?

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

Thanks, this is exactly what I was looking for.
One more question: changing this attribute will present is no side effects?

Thanks,

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

Em 09/11/2012 09:42, Albe Laurenz escreveu:

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

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

Thanks,
Pavan

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general