How to make it impossible for a postgres user to delete databases?

Question

What yould be the sql statement (that the user postgres will execute) so it will be impossible for the postgres user user1 to delete (drop) databases?

Or can I add a rule into some config file?

Answer 1

In PostgreSQL, only the owner of a database can drop a database. (Superusers can drop databases, but that's a different issue.) So changing the owner is the most direct way to prevent user1 from dropping any databases.

Fix that with ALTER DATABASE.

ALTER DATABASE name OWNER TO new_owner

To alter the owner, you must own the database and also be a direct or indirect member of the new owning role, and you must have the CREATEDB privilege. (Note that superusers have all these privileges automatically.)

To give specific permissions back to user1, use GRANT. You're probably looking for something like

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA schema_name TO user1;

But read the documentation for GRANT first. You might need WITH GRANT OPTION, permissions on sequences, something less than ALL PRIVILEGES, and so on.

Answer 2

Postgresql rules to stop a kind of event from happening on an object:

\dn                                        --shows your schema name 
SELECT session_user, current_user;         --shows your user 

--create a regular table with some data in it:
drop table if exists mytable; 
CREATE TABLE mytable( index integer, foo character varying(10)); 
insert into mytable values (3, 'yatta'); 
insert into mytable values (9, 'phobos'); 
select * from mytable; 
┌───────┬────────┐ 
│ index │  foo   │ 
├───────┼────────┤ 
│     3 │ yatta  │ 
│     9 │ phobos │ 
└───────┴────────┘ 
--Make a rule to stop deletes on a table:
CREATE or replace RULE mytable_del_protect AS ON DELETE TO mytable 
DO INSTEAD NOTHING; 

--Try to do a delete on the table
delete from mytable where index = 3;    --delete succeeds and affects 0 rows
select * from mytable;    --row you deleted is still there 
 
--Clear the rule: 
drop RULE mytable_del_protect on mytable; 
--once again try to delete
delete from mytable where index = 3;     --delete succeeds, affects 1 row
┌───────┬────────┐ 
│ index │  foo   │ 
├───────┼────────┤ 
│     9 │ phobos │ 
└───────┴────────┘ 

List postgresql Rules:

Your rule information is encoded in pg_rewrite pg_class and pg_namespace:

select n.nspname as rule_schema, c.relname as rule_table, 
   case r.ev_type 
     when '1' then 'SELECT' 
     when '2' then 'UPDATE' 
     when '3' then 'INSERT' 
     when '4' then 'DELETE' 
     else 'UNKNOWN' 
   end as rule_event 
from pg_rewrite r join pg_class c on r.ev_class = c.oid 
  left join pg_namespace n on n.oid = c.relnamespace 
  where c.relname = 'mytable'

┌─────────────┬────────────┬────────────┐ 
│ rule_schema │ rule_table │ rule_event │ 
├─────────────┼────────────┼────────────┤ 
│ public      │ mytable    │ DELETE     │ 
└─────────────┴────────────┴────────────┘ 

Instead of a Rule doing nothing on prevented delete, you could call a procedure. You can create a rule to do anything, on any kind of event on any kind of database object.