"security definer" not being set when function replaced

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

Browse pgsql-general by date

  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?