Re: database session variables

Mark Wilson wrote:
> Ok,
>
> Perhaps I'd better explain a bit more about what I am wanting to do with the
> session variables.
>
> I want to create a separation layer between business logic and application
> logic, by putting business logic in the database. To do this, all
> application layer access will be limited to views and stored procedures. In
> addition, I want each database connection to be selected from a common pool
> (e.g., all user 'web_user'). So every database connection will be made by
> user 'web_user'.

Again, server-side C will allow you to do what you want. You'd
actually be setting an environmental variable in the backend and
referencing it later...

C Source:
---------

#include "postgres.h"
#include "fmgr.h"

/* Routine to set the session id. Might want to change to
actually authenticate the user here with a password parameter */

PG_FUNCTION_INFO_V1(setwebuser);

Datum setwebuser(PG_FUNCTION_ARGS) {

char *buffer;
int len;

text *t = (text *) PG_GETARG_TEXT_P(0);
len = VARSIZE(t) - VARHDRSZ;
buffer = (char *) malloc(len + 1);
memcpy(buffer, VARDATA(t), len);
buffer[len] = 0;

if (setenv("WEBUSER", buffer, 1) != 0) {
free(buffer);
elog(ERROR, "Unable to set session id");
}
free(buffer);

PG_RETURN_INT32(1);

};

/* Routine to get the session webuser id */

PG_FUNCTION_INFO_V1(getwebuser);

Datum getwebuser(PG_FUNCTION_ARGS) {

text *t;
char *result;
int len;

result = getenv("WEBUSER");
if (result == NULL) {
elog(ERROR, "Session id not set");
}

len = strlen(result) + VARHDRSZ;
t = (text *) palloc(len);
VARATT_SIZEP(t) = len;
memcpy(VARDATA(t), result, len - VARHDRSZ);

PG_RETURN_TEXT_P(t);

}

Compile:
-------
gcc -c test.c -I/usr/include/pgsql/server
gcc -shared -o test.so test.o

Create the functions:
--------------------

CREATE OR REPLACE FUNCTION setwebuser(text) RETURNS int4
AS '/tmp/test.so'
LANGUAGE 'C' WITH (isStrict);

CREATE OR REPLACE FUNCTION getwebuser() RETURNS text
AS '/tmp/test.so'
LANGUAGE 'C' WITH (isStrict);

Example:
-------

create table salaries (
key integer not null,
salary float8 not null,
owner text not null
);

create view v_salaries as
select * from salaries where owner = getwebuser();

insert into salaries values (1, 10000, 'Mike');
insert into salaries values (2, 20000, 'Joe');

select setwebuser('Mike');

test=# select * from v_salaries;
key | salary | owner
-----+--------+-------
1 | 10000 | Mike

HTH,

Mike Mascari
mascarm(at)mascari(dot)com