Sure, you can use row-level locks to write-lock all rows currently in the table with:
SELECT * FROM tbl FOR UPDATE;
But that prevents all writes, including
DELETE, not only
UPDATE - for the duration of the lock. And there is the remaining question about rows inserted after that: shouldn't those be protected from
The way you phrased it ("
INSERT should be allowed, but
UPDATE should be disabled."), it sounds like a more general matter of privilege management with
REVOKE, rather than locking.
GRANT INSERT ON TABLE public.tbl TO public;
REVOKE UPDATE ON TABLE public.tbl FROM public;
GRANT INSERT ON TABLE public.tbl TO specific_role;
REVOKE UPDATE ON TABLE public.tbl FROM specific_role;
The actual commands depend on who has been
GRANTed privileges in the first place, and who shall be prevented to update now.
Don't forget the special case of the table owner. The manual on
There is no need to grant privileges to the owner of an object
(usually the user that created it), as the owner has all privileges by
default. (The owner could, however, choose to revoke some of their own
privileges for safety.)
While locks only last for the duration of a transaction, the effect is permanent - until changed explicitly. Also, locks are expensive.
REVOKE is almost cost-free.
You need the necessary privileges. Either be the owner of the table, be member in the role owning the table, be a superuser, or you have been granted the same privilege
WITH GRANT OPTION.