Thread

Re: [HACKERS] PL/pgSQL - for discussion (session global variables)

Andreas Zeugswetter <andreas.zeugswetter@telecom.at> — 1998-03-13T08:58:17Z

>    Someone gave a hint about global variables existing during  a
>    session.   What  is  a  session  than?  One  transaction? The
>    backends lifetime?   

That was me. A session is one client connection to the database from
the connect to the disconnect (the lifetime of one postgres process).
The same user connecting a second time will get a new global session 
variable pool. 

> And should global variables be visible by more than one function? 
Yes, if the variable is only seen by one function it is not global but
static ?

> In that case we need something like packages of functions that share globals. 
Of course this would be elegant, but since it is hard to implement and the commercial dbms's
dont't do that I guess we could also do without it (only one default package).
But I think all of this is specified in SQL92 under "session variables" (or was it another standard, I forgot).

I currently use this for a Web perl CGI application that uses the same user
on a given IP address for all connections within 30 minutes without a password.
Basically I have 2 procedures loginip and myuser:

create dba procedure "dns".loginip(name char(16), pwd char(16), ip char(15))
define global authuser char(16) default USER;
if name <> authuser then
        let authuser = (select uname from passwd where uname=name and fromip=ip
                        and lastlogin > current year to minute - 30 units minute);
        if authuser is null then
                let authuser = (select uname from passwd where uname=name and passwd=pwd);
        end if;
end if;
if authuser is null then
        let authuser = USER;
        raise exception -952,-61,'You entered an invalid login name or password.';
else
        update passwd set lastlogin=current year to minute, fromip=ip where uname=name;
end if;
end procedure;
grant execute on "dns".loginip to "nobody" as "dns";

create procedure "dns".myuser()
returning char(16);
define global authuser char(16) default USER;
return authuser;
end procedure;
grant execute on "dns".myuser to public as "dns"; 

I then use myuser() to do authentication with instead triggers checking if the
current myuser() is allowed to change certain values (domains).

One example:

create dba procedure "dns".checkadmin()
define auth integer;
let auth = (select count(*) from passwd where uname = myuser() and admin > 0);
if auth < 1 then
        raise exception -273,0,'You are not user administrator.';
end if;
end procedure;
grant execute on "dns".checkadmin to public as "dns";

create trigger "dns".passwd_utr
update on passwd
referencing old as o new as n
for each row
when (o.uname <> n.uname or o.uname <> myuser())
        ( execute procedure checkadmin() ); 

Andreas