| From: | Marty Scholes <marty(at)outputservices(dot)com> |
|---|---|
| To: | plsql-general(at)postgresql(dot)org |
| Subject: | "security definer" not being set when function replaced |
| Date: | 2004-06-08 01:47:28 |
| Message-ID: | 40C51AB0.9060202@outputservices.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | 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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Klint Gore | 2004-06-08 02:56:37 | mailing list oddities |
| Previous Message | Chris Travers | 2004-06-08 00:44:39 | Re: CREATE DATABASE on the heap with PostgreSQL? |