Thread: database session variables
Hi all,
I am working on a system architecture that requires accessing session variables specific to the database connection. This is useful because you can emulate many users (with different privileges) with a single database logon. Currently it seems like only user, current_user and session_user are supported.
What I would like is the ability to define and set my own session variables (the same way you can use package variables in oracle). Failing that, I need some way to uniquely identify the database session (e.g. a session_id function similar to session_user).
I would really like to use PostgreSQL but won't be able to if there is no way of accessing the session 'id'.
Is this currently possible, or are there any plans to add such to PostgreSQL in the future?
Cheers,
Mark
On Wed, Oct 02, 2002 at 10:27:10AM +1200, Mark Wilson wrote: > Hi all, > > I am working on a system architecture that requires accessing session > variables specific to the database connection. This is useful because you > can emulate many users (with different privileges) with a single database > logon. Currently it seems like only user, current_user and session_user > are supported. Well, you can get the process ID of the backend process. This would be different for every connection. What other database allow you to store session specific variables? -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > There are 10 kinds of people in the world, those that can do binary > arithmetic and those that can't.
Thanks Martijin, It sounds like the process_id is the 'correct' name for what I was calling session_id, and is what I want. To answer your question, Oracle lets you have session specific variables, via package variables. Now the next step is how to get access to process_id. I am currently running PostgreSQL version 7.2.1. "select procpid from pg_stat_activity;" seems to return information for all sessions (or 'processes'). Is backend_pid() or pg_stat_get_backend_pid() the correct call? Neither of them seem to exist in my database. If they are correct, how do I install them? Thanks in advance, Mark ----- Original Message ----- From: "Martijn van Oosterhout" <kleptog@svana.org> To: "Mark Wilson" <mark@mediasculpt.com> Cc: <pgsql-general@postgresql.org> Sent: Wednesday, October 02, 2002 11:49 AM Subject: Re: [GENERAL] database session variables > On Wed, Oct 02, 2002 at 10:27:10AM +1200, Mark Wilson wrote: > > Hi all, > > > > I am working on a system architecture that requires accessing session > > variables specific to the database connection. This is useful because you > > can emulate many users (with different privileges) with a single database > > logon. Currently it seems like only user, current_user and session_user > > are supported. > > Well, you can get the process ID of the backend process. This would be > different for every connection. > > What other database allow you to store session specific variables? > -- > Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > > There are 10 kinds of people in the world, those that can do binary > > arithmetic and those that can't. > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > >
On Wed, Oct 02, 2002 at 01:48:46PM +1200, Mark Wilson wrote: > Thanks Martijin, > > It sounds like the process_id is the 'correct' name for what I was calling > session_id, and is what I want. To answer your question, Oracle lets you > have session specific variables, via package variables. Hmm, the client can get it using $conn->backendPID in perl or PQbackendPID in C. I don't know how you do it from the backend itself. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > There are 10 kinds of people in the world, those that can do binary > arithmetic and those that can't.
On Wed, Oct 02, 2002 at 01:48:46PM +1200, Mark Wilson wrote: > Thanks Martijin, > > It sounds like the process_id is the 'correct' name for what I was calling > session_id, and is what I want. To answer your question, Oracle lets you > have session specific variables, via package variables. > > Now the next step is how to get access to process_id. I am currently > running PostgreSQL version 7.2.1. > > "select procpid from pg_stat_activity;" seems to return information for all > sessions (or 'processes'). > > Is backend_pid() or pg_stat_get_backend_pid() the correct call? Neither of > them seem to exist in my database. If they are correct, how do I install > them? Take a look at contrib/miscutil - the C module there gives you the SQL function backend_pid(). Cheers, Steve
Martijn van Oosterhout wrote: > On Wed, Oct 02, 2002 at 01:48:46PM +1200, Mark Wilson wrote: > >>Thanks Martijin, >> >>It sounds like the process_id is the 'correct' name for what I was calling >>session_id, and is what I want. To answer your question, Oracle lets you >>have session specific variables, via package variables. > > > Hmm, the client can get it using $conn->backendPID in perl or PQbackendPID > in C. I don't know how you do it from the backend itself. Why not write a little server-side C function to return the result of getpid()? Mike Mascari mascarm@mascari.com
It is in the Statistics Collector section of the 7.2.1 documentation http://www.postgresql.org/idocs/index.php?monitoring-stats.html But I'm a bit of a newbie at PostgreSQL and don't know how to install this library in the database or access it if it is already installed. Do you know how? Cheers, Mark ----- Original Message ----- From: "Martijn van Oosterhout" <kleptog@svana.org> To: "Mark Wilson" <mark@mediasculpt.com> Cc: <pgsql-general@postgresql.org> Sent: Wednesday, October 02, 2002 2:06 PM Subject: Re: [GENERAL] database session variables > On Wed, Oct 02, 2002 at 01:48:46PM +1200, Mark Wilson wrote: > > Thanks Martijin, > > > > It sounds like the process_id is the 'correct' name for what I was calling > > session_id, and is what I want. To answer your question, Oracle lets you > > have session specific variables, via package variables. > > Hmm, the client can get it using $conn->backendPID in perl or PQbackendPID > in C. I don't know how you do it from the backend itself. > -- > Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > > There are 10 kinds of people in the world, those that can do binary > > arithmetic and those that can't. > >
Thanks Steve, I'll try that. <snip> > > Take a look at contrib/miscutil - the C module there gives you the > SQL function backend_pid(). > <snip>
Hello, I'm wondering if it's possible to create a slave server where the data is continuously replicated? Kind Regards, Yuri
Hi Yuri, There are a couple of approaches in existence already: Free/Open Source ++++++++++++++++ PostgreSQL Replicator http://pgreplicator.sourceforge.net Usogres http://usogres.good-day.net The pgreplication Project http://gborg.postgresql.org/project/pgreplication/projdisplay.php Commercial ++++++++++ eRserver http://www.pgsql.com Of these, the eRserver approach works well (have been doing stuff with it recently in master to multi-slave async configurations), and the pgreplication project should have some interesting stuff ready for testing pretty soon. Hope that helps. :-) Regards and best wishes, Justin Clift Gyorgy Molnar wrote: > > Hello, > > I'm wondering if it's possible to create a slave server where the data is > continuously replicated? > > Kind Regards, > Yuri > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi
Steve Atkins wrote: > On Wed, Oct 02, 2002 at 01:48:46PM +1200, Mark Wilson wrote: > > Thanks Martijin, > > > > It sounds like the process_id is the 'correct' name for what I was calling > > session_id, and is what I want. To answer your question, Oracle lets you > > have session specific variables, via package variables. > > > > Now the next step is how to get access to process_id. I am currently > > running PostgreSQL version 7.2.1. > > > > "select procpid from pg_stat_activity;" seems to return information for all > > sessions (or 'processes'). > > > > Is backend_pid() or pg_stat_get_backend_pid() the correct call? Neither of > > them seem to exist in my database. If they are correct, how do I install > > them? > > Take a look at contrib/miscutil - the C module there gives you the > SQL function backend_pid(). backend_pid() is new in 7.3beta. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
"Mark Wilson" <mark@mediasculpt.com> writes:
> I would really like to use PostgreSQL but won't be able to if there is no w=
> ay of accessing the session 'id'.'
psql has session-local variables (prefixed by colons). Any other client
interface I can think of also has the ability to keep client-side
variables. I'm really not clear what you are after that's not
implementable as a client-side feature ...
regards, tom lane
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'.
Bring on user bob.
When a web page starts up, it grabs a session (web_user) and calls
p_set_user('bob'). This *effectively* will make bob the user connected,
even though the user bob may not even exist in the database as an actual
user.
Bob can only access the views and procedures that have been granted to Bob.
In addition, the views limit the data bob has to only his own organisation.
User Mike belongs to a different organisation, and sees a completely
different set of data from the views.
This might seem like I'm asking for too much from the database, but:
1. It vastly simplifies the application logic (php) code
2. It guarantees that different sections execute the same functionality in
the same way (because they call the same stored procedures)
3. SQL is much more efficiently written. Having an algorithm in a stored
procedure also cuts down on the number of database calls that are made.
Complex sql is written by database developers rather than application
developers (a bigger performance bottleneck than you might think).
4. More complicated user relationships can be used. For example, you can
have both Bob from McDonalds and Bob from KFC in your database (if they
select their organisation as well at the logon screen).
5. If someone breaches application layer security then all they can do is
manipulate data using the API. Other client's data isn't visible.
6. I have already implemented this in oracle already, so I know that it can
be done in at least one database.
I am trying to find out if this is possible in an open source database, and
if PostgreSQL can do this then I'll use it. As I said in previous mails,
just backend_pid() is enough for a hoagy workaround version of this
architecture.
Sorry if this is confusing,
Mark
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Mark Wilson" <mark@mediasculpt.com>
Cc: <pgsql-general@postgresql.org>
Sent: Wednesday, October 02, 2002 4:57 PM
Subject: Re: [GENERAL] database session variables
> "Mark Wilson" <mark@mediasculpt.com> writes:
> > I would really like to use PostgreSQL but won't be able to if there is
no w=
> > ay of accessing the session 'id'.'
>
> psql has session-local variables (prefixed by colons). Any other client
> interface I can think of also has the ability to keep client-side
> variables. I'm really not clear what you are after that's not
> implementable as a client-side feature ...
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
>
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
Mike, Thanks heaps for your help here. I'll try that (although I'm a bit of a freebsd/c newbie). My system didn't have postgres.h, so I'm using postgres_fe.h instead. In addition, I don't have fmgr.h. I downloaded version 1.18 of it from http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/include/fmgr.h?s ortby=date&only_with_tag=REL7_2_STABLE, but the first gcc line produced a number of compiler errors and warnings, starting with: fmgr.h:30: syntax error before `*' fmgr.h:30: `Datum' declared as function returning a function line 30 of fmgr.h is typedef Datum (*PGFunction) (FunctionCallInfo fcinfo); Do you know what is causing this? (gcc version:gcc-2.95.3) Thanks in advance, Mark ----- Original Message ----- From: "Mike Mascari" <mascarm@mascari.com> To: "Mark Wilson" <mark@mediasculpt.com> Cc: <pgsql-general@postgresql.org> Sent: Thursday, October 03, 2002 10:46 AM Subject: Re: [GENERAL] 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@mascari.com > > > > > >
Mark Wilson wrote: > Mike, Thanks heaps for your help here. I'll try that (although I'm a bit of > a freebsd/c newbie). > > My system didn't have postgres.h, so I'm using postgres_fe.h instead. In > addition, I don't have fmgr.h. I downloaded version 1.18 of it from > http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/include/fmgr.h?s > ortby=date&only_with_tag=REL7_2_STABLE, but the first gcc line produced a > number of compiler errors and warnings, starting with: > fmgr.h:30: syntax error before `*' > fmgr.h:30: `Datum' declared as function returning a function > > line 30 of fmgr.h is > typedef Datum (*PGFunction) (FunctionCallInfo fcinfo); > > Do you know what is causing this? (gcc version:gcc-2.95.3) To write server-side C functions you need to be able to include all of the server headers that the C interface requires. When installing PostgreSQL, one normally either does a: ./configure gmake gmake install However, to do server-side development, you also have to do: gmake install-all-headers or, if you are using binary packages you need to install the appropriate package that contains the server-side development headers. For RPMS, the package is: postgresql-devel You should then have all the headers necessary for server-side C development. Hope that helps, Mike Mascari mascarm@mascari.com
On 2 Oct 2002 at 0:12, Gyorgy Molnar wrote: > I'm wondering if it's possible to create a slave server where the data is > continuously replicated? You might want to try this.. http://usogres.good-day.net/ Never had a chance to use it So I would like to hear about it's performance under load.. There are some pdfs floating around which details available options for replication under postgresql. I am attaching one.. See if you find it useful.. Bye Shridhar -- The Beatles: Paul McCartney's old back-up band.