How to prevent table fall?

Question

In PostgreSQL, how can I prevent someone (including superusers) from deleting a specific table?

EDIT : We had some kind of misunderstanding. Say there is a large common QA database. Sometimes people mistakenly launch destructive things, such as a hibernation scheme, and I look for ways to prevent such errors.

Answer 1

does anyone (including superusers) drop a specific table?

Trust your peers.

Answer 2

This can be done by writing C code that attaches to ProcessUtility_hook . If you have never done this, it will not be completely trivial, but it is possible.

Another option might be considered in sepgsql, but I have no experience with this.

Answer 3

Superuser is just that. If you do not want them to be able to give up things, do not make them superuser.

There is no need to allow users to work as superusers to a large extent. Of course, not automated tools such as schema migrations.

Your applications should be connected as users with the minimum necessary user rights. They do not have to own the tables on which they work, so they cannot make changes to the schema or leave them.

If you want to make changes to the schema, run the application with a user who owns the tables of interest but is not a superuser. The owner of a table can drop and modify tables, but only the tables that he owns.

If you really need to do something outside of the standard permission model, you need to write ProcessUtility_hook . See this related answer for a few details. Even then, the superuser will be able to get around it by downloading the extension that will miss your hook, you just slow it down a bit.

Do not run the application as superuser during production. Someday.

See the PostgreSQL documentation on permissions for more guidance on using the permission model.

Answer 4

I do not think you can do this. You might have super-superusers who can handle this at first. OR have backups constantly, so a higher member of the hierarchy will always have the opportunity to get a table.