How to perform a GRANT in a stored procedure using a variable?

Question

As an alternative to Post-create hook for BLOB values in Hibernate I thought an in-database trigger would be a better solution, so I attempted to write the following trigger:

CREATE FUNCTION lo_default_grant() RETURNS trigger AS $$
DECLARE
    lo_oid lo := NEW[TG_ARGV[0]];
BEGIN
    IF TG_OP = 'INSERT' THEN
        GRANT SELECT ON LARGE OBJECT lo_oid TO reader_role;
    ELSIF OLD[TG_ARGV[0]] != NEW[TG_ARGV[0]] THEN
        GRANT SELECT ON LARGE OBJECT lo_oid TO reader_role;
    END IF;
END;
$$ LANGUAGE plpgsql;

Which would be used with a trigger such as:

CREATE TRIGGER t_grant AFTER INSERT OR UPDATE ON my_entity
  FOR EACH ROW EXECUTE PROCEDURE lo_default_grant(file);

However, I cannot work out the syntax for building the GRANT statement.

[2022-03-09 16:14:51] [42601] ERROR: syntax error at or near "lo_oid"
[2022-03-09 16:14:51] Position: 279

It it really not possible to use variables in the same way you can in other statements?

score 2 · Accepted Answer · answered Mar 09 '22 at 18:07

GRANT statements are not optimizable, so you cannot directly insert parameters into them. Nor can you subscript rows with dynamic field names.

However, PL/pgSQL has the EXECUTE statement, allowing you to construct a string containing the desired statement:

CREATE OR REPLACE FUNCTION lo_default_grant() RETURNS trigger AS $$
DECLARE
    old_lo oid;
    new_lo oid;
BEGIN
    EXECUTE format('SELECT $1.%I', TG_ARGV[0]) USING OLD INTO old_lo;
    EXECUTE format('SELECT $1.%I', TG_ARGV[0]) USING NEW INTO new_lo;
    IF TG_OP = 'INSERT' OR old_lo != new_lo THEN
        EXECUTE format('GRANT SELECT ON LARGE OBJECT %s TO reader_role', new_lo);
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

How to perform a GRANT in a stored procedure using a variable?

1 Answers1