Re: Making "SECURITY DEFINER" procedures.. - Mailing list pgsql-sql

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)
NOTNULL 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
ManageUsertable       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(''10min'' 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'';
ENDIF;
 
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