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: | Raw Message | Whole Thread | 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? |