"security definer" not being set when function replaced

[Marty Scholes]

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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster