PostgreSQL: read-only table

Question

My question is related to this one: how to lock a table for writing

I found the simple solution, but I am not sure is it safe for side-effects.

So:

update pg_class set relkind = 'm' where relname = '<table_name>';

(surely it should be more complex to take in account the table schema)

However in my simple tests it makes the trick:

create table t(i int); insert into t values(1);
update pg_class set relkind = 'm' where relname = 't';
insert into t values(1);
-- ERROR:  cannot change materialized view "t"
select * from t;
-- i 
-- ---
-- 1
-- (1 row)

So, my question (totally theoretical for now) is: Does something could to going wrong with this solution?

Answer 1

Yes, things can go wrong.

Postgres would never allow this state through DDL commands, and its behaviour is now basically undefined.

For one thing, every materialised view is expected to have an associated definition, and so pg_dump now crashes, complaining that the definition of view "t" appears to be empty (length zero).

Your "materialised view" may also have column defaults, constraints, triggers, and many other things which would never be permitted via DDL, which might cause their own set of problems.

If you want to make a table read-only, set the appropriate permissions, or reject any changes in a trigger.