Locking: Allow INSERT but not UPDATE

Question

I would like to lock a table: INSERT should be allowed, but UPDATE should be disabled.

I am using a current PostgreSQL version.

I read Advisory Locks and explicit locking. Maybe I am blind, but I could not find a suitable solution for my goal.

Here are details to my use case:

There is a table which is roughly like logging: Several processes add new rows to the table at the same time.

Once in a hour a script processes the data which was added.

Now I want to be sure that this script has a fixed working set. No modifications should be done to the rows which the script is working on. But it is ok if new rows get added while the scripts run. These rows get processes with the next call of the script.

Answer 1

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 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 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.

Answer 2

You could use pg_try_advisory_lock(). See Advisory Locks