Doing FOR UPDATE and JOIN when using ROW SHARE lock?

Question

According to PostgreSQL documentation, the ROW SHARE lock works like this:

The SELECT command acquires a lock of this mode on all tables on which one of the FOR UPDATE FOR NO KEY UPDATE, FOR SHARE, or FOR KEY SHARE options is specified...

So if I have a table that stores vehicle brands:

CREATE TABLE my_schema.vehicle_brand (
    k_vehicle_brand varchar(40) NOT NULL,
    CONSTRAINT vehicle_brand_pk PRIMARY KEY (k_vehicle_brand)
);

CREATE TABLE my_schema.vehicle (
    k_vehicle integer NOT NULL,
    k_vehicle_brand varchar(40) NOT NULL,
    license_plate varchar(7) NOT NULL,
    vehicle_color varchar(20) NOT NULL,
    CONSTRAINT vehicle_pk PRIMARY KEY (k_vehicle)
);

ALTER TABLE my_schema.vehicle ADD CONSTRAINT vehicle_brand_fk
FOREIGN KEY (k_vehicle_brand)
REFERENCES my_schema.vehicle_brand (k_vehicle_brand) MATCH FULL 
ON DELETE RESTRICT ON UPDATE CASCADE;

Then I create a role:

CREATE ROLE testing_role WITH
    LOGIN
    PASSWORD 'MYPASS';

GRANT USAGE
    ON SCHEMA my_schema
    TO testing_role;

GRANT SELECT 
    ON TABLE my_schema.vehicle_brand
    TO testing_role;

GRANT SELECT, INSERT, UPDATE, DELETE 
    ON TABLE my_schema.vehicle
    TO testing_role;

And then I want to run:

DO $$
DECLARE 
    license_plate VARCHAR;
BEGIN 
    LOCK TABLE my_schema.vehicle IN ROW SHARE MODE;

    SELECT V.license_plate INTO license_plate
    FROM my_schema.vehicle_brand B
        INNER JOIN my_schema.vehicle V ON B.k_vehicle_brand = V.k_vehicle_brand
    WHERE B.k_vehicle_brand = 'Audi'
    FOR UPDATE;

    RAISE NOTICE 'License Plate: %', license_plate;

END;
$$;

If I want testing_role to only SELECT the table vehicle_brand and when doing a SELECT with JOIN over other tables testing_role can INSERT, UPDATE or DELETE, is it possible to do a FOR UPDATE for just these other tables?

Answer 1

FOR UPDATE takes an optional OF clause which lists which tables have their rows locked. If you use the OF clause, then only listed tables need to have greater privileges than SELECT. For unlisted ones, SELECT priv is sufficient.