Re: database session variables - Mailing list pgsql-general
From | Mark Wilson |
---|---|
Subject | Re: database session variables |
Date | |
Msg-id | 004f01c26a60$e2044310$3301a8c0@merl Whole thread Raw |
In response to | database session variables ("Mark Wilson" <mark@mediasculpt.com>) |
List | pgsql-general |
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 > >
pgsql-general by date: