Sure, you can use row-level locks to write-lock all rows currently in the table with:
SELECT * FROM tbl FOR UPDATE;
Or similar.
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 UPDATE
, too?
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 GRANT
and REVOKE
, rather than locking.
Something like:
GRANT INSERT ON TABLE public.tbl TO public;
REVOKE UPDATE ON TABLE public.tbl FROM public;
Or:
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 GRANT
ed 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 GRANT
:
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. GRANT
/ 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
.
select * from the_table for update