Thread: "security definer" not being set when function replaced

"security definer" not being set when function replaced

From
Marty Scholes
Date:
I may have found a bug.

I have a table:

CREATE TABLE onlpcd_stat
(
         sel     BIGSERIAL PRIMARY KEY,
         user_id INTEGER NOT NULL REFERENCES onlpcd_user ON DELETE CASCADE,
         vars    TEXT,   /* string of Perl var hash */
         cre_ts  TIMESTAMP DEFAULT NOW()
);

Defined by user 'dba' and executed by the function:

-- return a selector for a state from onlpcd_stat
-- pass in user and var string
CREATE OR REPLACE FUNCTION
fn_onlpcd_get_sel(onlpcd_stat.user_id%TYPE, onlpcd_stat.vars%TYPE)
RETURNS onlpcd_stat.sel%TYPE AS '
DECLARE
         -- selector we will return
         r_sel   onlpcd_stat.sel%TYPE;
         -- user passed in vars
         v_uid ALIAS for $1;
         v_var ALIAS for $2;
BEGIN
         -- do not recycle selectors -- it helps
         -- avoid browser caching problems
         -- get next sel value
         SELECT INTO r_sel NEXTVAL(''onlpcd_stat_sel_seq'');

         -- make an entry
         INSERT INTO onlpcd_stat (sel, user_id, vars)
         VALUES (r_sel, v_uid, v_var);

         RETURN r_sel;
END;
' LANGUAGE plpgsql;
GRANT EXECUTE ON FUNCTION
fn_onlpcd_get_sel(onlpcd_stat.user_id%TYPE, onlpcd_stat.vars%TYPE)
TO nobody;

Also defined by 'dba', but run by the web server, 'nobody.'

When I type:

psql -U nobody -c "select fn_onlpcd_get_sel(2, 'test')"

It responds with:

ERROR:  permission denied for sequence onlpcd_stat_sel_seq
CONTEXT:  PL/pgSQL function "fn_onlpcd_get_sel" line 11 at select into
variables

So far, so good.

If I put "SECURITY DEFINER" in the definition and redefine it, I get the
same problem.

However, if I drop the function, then define it again, all is well.

Marty