"security definer" not being set when function replaced
От | Marty Scholes |
---|---|
Тема | "security definer" not being set when function replaced |
Дата | |
Msg-id | 40C51AB0.9060202@outputservices.com обсуждение исходный текст |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: