Choosing permissions¶
There are six. You pick the ones the table genuinely needs, and everything else is refused.
| Permission | What it allows |
|---|---|
insert |
Adding rows, by any route — INSERT, COPY, MERGE |
insertonce |
One insert, ever |
update |
Changing existing rows |
delete |
Removing rows |
truncate |
Emptying the table with TRUNCATE |
drop |
Dropping the table |
Spacing and capitals do not matter. 'INSERT, Update' means the same as 'insert,update'.
Common combinations¶
Append-only — the usual choice for audit trails and event logs:
Append-only, but tidy-able — rows can be removed once old enough:
Normal working table that cannot be dropped or wiped — useful for reference data:
Write once and never again:
About insertonce¶
The first insert into the empty table succeeds. Every insert after that fails, for the life of the table.
CREATE TABLE opening_balance (account text, amount numeric)
USING vault WITH (permissions = 'insertonce');
INSERT INTO opening_balance VALUES ('4501', 1000.00); -- fine
INSERT INTO opening_balance VALUES ('4502', 2000.00); -- refused
Two details worth knowing:
- The first statement can insert many rows.
INSERT INTO t VALUES (1),(2),(3)is one insert, not three. - A rolled-back insert does not use up your one chance. If the transaction aborts, the table is still empty and the next attempt works.
insert and insertonce cannot both be given — insertonce is a variant of insert, not an addition to it.
About drop¶
Think carefully about this one.
If you do not grant drop, the table can never be removed. Not by you, not by your DBA, not by a superuser, not with CASCADE, not by dropping the schema around it. The only way to get rid of it is to drop the entire database.
That is the intended behaviour — a table that can be dropped on a whim is not really protected. But it does mean you should be deliberate about it.
If you want the protection but also a way out, grant drop. Data still cannot be edited, but the table as a whole can be removed by somebody who means to.
Combinations that are refused¶
Two combinations fail when you create the table:
insert with insertonce — pick one.
truncate with retention — TRUNCATE empties the whole table in one go without looking at retention at all, so a table granting both would claim a retention period that a single statement could ignore. If you need rows removable before their deadline, grant delete instead.