Re: Making "SECURITY DEFINER" procedures..

SS> I made bogus gen_random_string and password functions and a bogus
SS> ManageUser table and couldn't reproduce an error like the above with a
SS> different user (one who can't read/write to the tables used), so I'd guess
SS> it's from something that isn't being shown. Are there any foreign keys
SS> to ManageSession?

GRANT ALL ON SCHEMA public TO "data";

CREATE SCHEMA data AUTHORIZATION "data";

\connect - "data"
SET search_path = data;
..
Create table ManageUser
(
id Serial NOT NULL,
Name Varchar(20) NOT NULL UNIQUE ,
Passwd bytea,
Birthday timestamp(0) NOT NULL Default now(),
ManageGroupId integer NOT NULL, -- FOREIGN KEY
TO ManageGroup
OnlyCustomerId integer NULL , --
FOREIGN KEY TO Customer
memo Varchar(500) NULL ,
primary key (id)
);
..
Create table ManageSession
(
id Serial NOT NULL,
UserId integer NOT NULL, --
FOREIGN KEY TO ManageUser table
Key bytea NOT NULL,
Birthday timestamp(0) NOT NULL Default now(),
primary key (id)
);
Alter table ManageSession add foreign key (UserId) references ManageUser (id)
on update cascade on delete cascade ;
..
set search_path=public;
CREATE OR REPLACE FUNCTION new_session (character varying, character varying)
RETURNS character varying
AS 'DECLARE
u_login ALIAS FOR $1;
u_passwd ALIAS FOR $2;
u_id INTEGER;
u_key VARCHAR;
dbg VARCHAR;
BEGIN
select current_user into dbg;
raise notice ''current user is %'', dbg;
DELETE FROM data.ManageSession WHERE
(now() - Birthday) > CAST(''10 min'' AS INTERVAL);
raise notice ''after delete'';
SELECT id INTO u_id FROM data.ManageUser WHERE Name = u_login AND
Passwd = password(u_passwd);
raise notice ''after select - %'',u_id;
IF u_id IS NOT NULL THEN
u_key := gen_random_string(20);
INSERT INTO data.ManageSession(UserId,Key) VALUES (u_id,
password(u_key));
raise notice ''after insert - %'',u_key;
ELSE
RAISE EXCEPTION ''Wrong login or password'';
END IF;
raise notice ''before return - %'',u_key;
RETURN u_key;
END;
' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
...

So thera are several foreign constraints. But:

$psql -U rcbilling billing
billing=> select new_session('sergey','password');
NOTICE: current user is data
NOTICE: after delete
NOTICE: after select - 8
NOTICE: after insert - KMp7ciFzJAL10Xxqft9O
NOTICE: before return - KMp7ciFzJAL10Xxqft9O
ERROR: data: permission denied

$psql -U data billing
billing=> select new_session('sergey','password');
NOTICE: current user is data
NOTICE: after delete
NOTICE: after select - 8
NOTICE: after insert - n7c1gAqPB0WuFwCEapy4
NOTICE: before return - n7c1gAqPB0WuFwCEapy4
new_session
----------------------
n7c1gAqPB0WuFwCEapy4
(1 row)

--
With Best Regards,
Sergey Holod