Restrict Modifying Table Rows (UPDATE/DELETE) Irrespective of User Privileges

Do you have a table with critical information such as SIEM events or credit card transactions? Do you want to be able to block user access to UPDATE/DELETE rows in those tables? From version 12.0.0, you can do this by setting the tables as IMMUTABLE. This means you can only insert data and not modify existing data irrespective of your database roles.

Example

Let us create a sample table with credit card number details of a customer. You do not want the table rows to be modified by anybody in your organization as it is highly confidential.

dbame=> create table credit_card_info(card_number varchar(50), card_family varchar(30), credit_limit int, customer_id varchar(30));
CREATE TABLE
dbame=>
dbame=> insert into credit_card_info values ('8638-5407-3631-8196', 'Premium', 530000, 'CC67088');
OUTPUT
--------
1
(1 row)

dbame=> insert into credit_card_info values ('7106-4239-7093-1515', 'Gold', 18000, 'CC12076');
OUTPUT
--------
1
(1 row)

dbame=> insert into credit_card_info values ('6492-5655-8241-3530', 'Premium', 596000, 'CC97173');
OUTPUT
--------
1
(1 row)

dbame=> commit;
dbame => select * from credit_card_info;
card_number | card_family | credit_limit | customer_id
---------------------+-------------+--------------+-------------
8638-5407-3631-8196 | Premium | 530000 | CC67088
7106-4239-7093-1515 | Gold | 18000 | CC12076
6492-5655-8241-3530 | Premium | 596000 | CC97173
(3 rows)

How do I make this table immutable?

You can make the table immutable by using ALTER TABLE:

dbame=> ALTER TABLE credit_card_info SET IMMUTABLE ROWS;
ALTER TABLE

What happens if I try to update the table now?

As you can see in the following message, it fails with an error:

dbame=> UPDATE credit_card_info set card_family = 'Silver' where customer_id = 'CC67088';
ERROR 4367:  Permission denied for relation credit_card_info
dbame=>

How do I know the list of immutable tables in my database?

You can query the system table v_catalog.tables to get a list of tables that are marked immutable. Columns named immutable_rows_since_epoch and immutable_rows_since_timestamp have values set when a user marks the table as immutable.

dbame=>select * from tables where table_name = 'credit_card_info';
-[ RECORD 1 ]------------------+------------------------------
table_schema_id                | 45035996273705014
table_schema                   | public
table_id                       | 45035996274090780
table_name                     | credit_card_info
owner_id                       | 45035996273704962
owner_name                     | dbadmin
is_temp_table                  | f
is_system_table                | f
force_outer                    | 5
is_flextable                   | f
has_aggregate_projection       | f
system_table_creator           |
partition_expression           |
create_time                    | 2023-02-01 16:28:19.024067-05
table_definition               |
recover_priority               | -9223372036854775807
storage_mode                   | DIRECT
is_shared                      | t
partition_group_expression     |
active_partition_count         |
is_mergeout_enabled            | t
immutable_rows_since_timestamp | 2023-02-01 16:34:28.07001-05
immutable_rows_since_epoch     | 50