Re: database session variables

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(at)sss(dot)pgh(dot)pa(dot)us>
To: "Mark Wilson" <mark(at)mediasculpt(dot)com>
Cc: <pgsql-general(at)postgresql(dot)org>
Sent: Wednesday, October 02, 2002 4:57 PM
Subject: Re: [GENERAL] database session variables

> "Mark Wilson" <mark(at)mediasculpt(dot)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
>
>