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.