Re: database session variables - Mailing list pgsql-general

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@mascari.com