How to prevent table from being dropped?

Question

In PostgreSQL, how can I prevent anyone (including superusers) from dropping some specific table?

EDIT: Whoa, did we have some misunderstanding here. Let's say there is a big, shared QA database. Sometimes people run destructive things like hibernate-generated schema on it by mistake, and I'm looking for ways to prevent such mistakes.

@GilbertLeBlanc The NSA isn't a company and this isn't evil. — daiuto, Jun 27 '13 at 17:39
The only way to prevent "running hibernate generated schema" is to revoke any DDL privilege from the "regular" users and have a dedicated user to create and manage objects in the database. Only the "DBA" (or whoever is responsible for managing the objects) should then connect as the "object owner" and run DDL statements. A lot of our customers have limited access to the DB that way (the "application user" can only run DML, nothing more). This is not an uncommon setup in large companies. — , Jun 27 '13 at 17:46
It's not surprising that we had a "big misunderstanding" when you wrote a one-line, detail-free question. Effort in tends to be proportional to quality-of-response out. — Craig Ringer, Jul 02 '13 at 07:43
@CraigRinger Let's say it's focus on what's really important, avoiding distractions and showing respect to the readers. I value their time by not writing garbage like: "So we have this QA environment, and that DB config file, and this Ant script that runs hbm2ddl on this database, and the other day Johnny didn't notice that he was pointing at the wrong database..." - fingers hurt, and noone wants to read that. — Konrad Garus, Jul 02 '13 at 08:41

score 3 · Answer 1 · answered Jun 27 '13 at 10:24

3

anyone (including superusers) from dropping some specific table?

Trust your peers.

answered Jun 27 '13 at 10:24

ta.speot.is

26,914
8
68
96

Or add a super-superuser. But wait, how to prevent this user from dropping a table then...? – Uooo Jun 27 '13 at 10:32
@w4rumy: With a super-super-super user. It's supers all the way up. :-) – Gilbert Le Blanc Jun 27 '13 at 12:20

score 3 · Answer 2 · answered Jun 27 '13 at 12:58

3

You can do that by writing some C code that attaches to ProcessUtility_hook. If you have never done that sort of thing, it won't be exactly trivial, but it's possible.

Another option might be looking into sepgsql, but I don't have any experience with that.

answered Jun 27 '13 at 12:58

Peter Eisentraut

35,221
12
85
90

A prime example of the benefits of an open source database. – Kuberchaun Jun 27 '13 at 14:08
Writing a ProcessUtility_hook isn't actually too hard, but there are differences between the PostgreSQL 9.2 and 9.3 definitions that mean you'll need separate extensions. Here's a basic example: https://github.com/ringerc/scrapcode/tree/master/postgresql/example_processutility_hook and here's a ProcessUtility hook that actually does something useful: https://github.com/ringerc/postgres/blob/bdr-reject-unsafe-commands/contrib/bdr/bdr_commandfilter.c – Craig Ringer Jun 28 '13 at 03:40

score 2 · Answer 3 · edited May 23 '17 at 11:52

A superuser is precisely that. If you don't want them to be able to drop things, don't make them a superuser.

There's no need to let users run as superusers pretty much ever. Certainly not automated tools like schema migrations.

Your applications should connect as users with the minimum required user rights. They should not own the tables that they operate on, so they can't make schema changes to them or drop them.

When you want to make schema changes, run the application with a user that does have ownership of the tables of interest, but is not a superuser. The table owner can drop and modify tables, but only the tables it owns.

If you really, truly need to do something beyond the standard permissions model you will need to write a ProcessUtility_hook. See this related answer for a few details on that. Even then a superuser might be able to get around it by loading an extension that skips your hook, you'll just slow them down a bit.

Don't run an application as a superuser in production. Ever.

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

score 1 · Answer 4 · edited Jun 27 '13 at 11:50

1

I don't think you can do that. You could perhaps have super super users who are going to manage the dropping of everything first. OR have backups constantly, so the higher member of the hierarchy will always have the possibility of retrieving the table.

edited Jun 27 '13 at 11:50

answered Jun 27 '13 at 10:25

LTKD

214
2
13

score 1 · Answer 5 · answered Jun 01 '21 at 23:22

I don't know what the real original intention of this question is... but a lot of people seem to have hypothetical answers like trusting your peers or using least permissions models appropriately. Personally, this misses the point altogether and instead answers with something everyone probably knew already, which isn't particularly helpful.

So let me attempt a question + answer of my own: How do you put in safety locks to prevent yourself or others from accidentally doing something you shouldn't? If you think that this "should never happen" then I think your imagination is too narrow. Or perhaps you are more perfect than me (and possibly a lot of other people).

For the rest of us, here is a solution that works for me. It is just a little lock that is put wherever you want it to - using event triggers. Obviously, this would be implemented by a super-user of some sort. But the point of it is that it has no bearing on permissions because it is error-based not permission based.

Obviously, you shouldn't implement this sort of thing if production behavior is dependent on it. It only makes sense to use in situations where it makes sense to use. Don't use it to replace what should be solved using permissions and don't use it to undermine your team. Use common sense - individual results may vary.


CREATE SCHEMA testst;

CREATE OR REPLACE FUNCTION manual_override_required() RETURNS event_trigger AS
$$
DECLARE
    obj record;
BEGIN
FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects()
LOOP
    RAISE INFO 'object_oid: %, object_type: %', obj.objid, obj.object_type;
    RAISE info '%', obj.object_name;
    IF obj.object_type = 'schema' and obj.object_name = 'testst' THEN
        RAISE EXCEPTION 'You have attempted to DROP something which has been hyper-locked and requires manual override to proceed.';
    END IF;
END LOOP;
END
$$
LANGUAGE plpgsql
;


DROP EVENT TRIGGER IF EXISTS lock_schema;
CREATE EVENT TRIGGER lock_schema
   ON sql_drop
EXECUTE FUNCTION manual_override_required();

DROP SCHEMA testst;

-- produces error: "ERROR: You have attempted to DROP something which has been admin-locked and requires manual override to proceed."


-- To override the admin-lock (you have the permission to do this, it just requires two turns of a key and positive confirmation):

ALTER EVENT TRIGGER lock_schema DISABLE;


DROP SCHEMA testst;
-- now it works!

An example of how I use this is in automation workflows. I have to switch between dev and prod environments a dozen times a day and I can (i.e. have) easily lost track of which is which despite the giant flags and banners I've put up to remind myself. Dropping certain schemas should be a rare and special event in Prod (thus the benefit of a active-confirmation approach) whereas in dev I rebuild them all the time. If I maintain the same permission structures in Dev as in Prod (which I do) then I wouldn't be able to solve this.

How to prevent table from being dropped?

5 Answers5

Linked