Обсуждение: Philosophical question

Hi,

I asked elsewhere about the best way to store db credentials within a
user-session of a web-app.

It appeared that it was for everybody but me evident that instead of
heaving a db-role+passwd for every user of an application it was better
to have just 1 set of db-credentials for the application and recreate a
user management within the app instead using the existing user handling
of the dbms.

That way the app checks the user's password as a md5 in some table and
remembers "user is logged in" for later. The actual queries would be
done with a common set of real db credentials.

Pro:  Noone could bypass the app and use e.g. pgAdmin to access the DB
instead of the app.

Con:  A bug in the app could give anyone the access level of the app's
credentials which might offer admin rights if such power is needed at
least for some users.


What's your opinion?

On 14/12/2011 8:32 PM, Andreas wrote:
> Hi,
>
> I asked elsewhere about the best way to store db credentials within a
> user-session of a web-app.
>
Where? Link?

> It appeared that it was for everybody but me evident that instead of
> heaving a db-role+passwd for every user of an application it was
> better to have just 1 set of db-credentials for the application and
> recreate a user management within the app instead using the existing
> user handling of the dbms.
I usually prefer a hybrid, where the app logs in with a particular role
with limited rights then does a SET ROLE to the app user it's currently
operating as. Related to:


http://stackoverflow.com/questions/8432636/in-postgresql-are-partitions-or-multiple-databases-more-efficient/8439618#8439618

--
Craig Ringer

On Wed, Dec 14, 2011 at 4:32 AM, Andreas <maps.on@gmx.net> wrote:
> Hi,
>
> I asked elsewhere about the best way to store db credentials within a
> user-session of a web-app.
>
> It appeared that it was for everybody but me evident that instead of heaving
> a db-role+passwd for every user of an application it was better to have just
> 1 set of db-credentials for the application and recreate a user management
> within the app instead using the existing user handling of the dbms.

I prefer the db-role + password for a number of reasons including the
ability to link into various auth options of PostgreSQL.

Also it fits with my security strategy of drawing as narrow of a
security perimeter as possible and thus making things more defensible.

>
> That way the app checks the user's password as a md5 in some table and
> remembers "user is logged in" for later. The actual queries would be done
> with a common set of real db credentials.
>
> Pro:  Noone could bypass the app and use e.g. pgAdmin to access the DB
> instead of the app.

On the other hand, you can prevent this using the pg_hba.conf, and if
some user wants that permission, and has a legitimate reason for it,
the permissions might be already set up to a reasonably safe level
depending on what exactly is needed.

>
> Con:  A bug in the app could give anyone the access level of the app's
> credentials which might offer admin rights if such power is needed at least
> for some users.

Yes, the standard approach means the *application* has access to the
the sum of all permissions needed.  This is true on hybrid methods
too, although hybrid methods have the benefit of not having all such
access at once.

I prefer to let PostgreSQL manage the user/passwords of my web apps.

Best Wishes,
Chris Travers

Andreas wrote:
> I asked elsewhere about the best way to store db credentials within a
> user-session of a web-app.
> 
> It appeared that it was for everybody but me evident that instead of
> heaving a db-role+passwd for every user of an application it was better
> to have just 1 set of db-credentials for the application and recreate a
> user management within the app instead using the existing user handling
> of the dbms.
> 
> That way the app checks the user's password as a md5 in some table and
> remembers "user is logged in" for later. The actual queries would be
> done with a common set of real db credentials.
> 
> Pro:  Noone could bypass the app and use e.g. pgAdmin to access the DB
> instead of the app.
> 
> Con:  A bug in the app could give anyone the access level of the app's
> credentials which might offer admin rights if such power is needed at
> least for some users.
> 
> 
> What's your opinion?

You forgot the most important pro:

If the web application server uses a single database user, you can
use connection pooling, i.e. reuse connections instead of maintaining
one connection per database user. This will boost performance.

True, you could have a connection pool and use ALTER SESSION AUTHORIZATION
to become a certain database user for one request, but that means
that the application server login user must be a superuser, which is
a terrible idea.

Yours,
Laurenz Albe

Am 14.12.2011 14:28, schrieb Craig Ringer:
> On 14/12/2011 8:32 PM, Andreas wrote:
>> Hi,
>>
>> I asked elsewhere about the best way to store db credentials within a
>> user-session of a web-app.
>>
> Where? Link?

Well, it was on the general list of php.net.
I read your link and understood your not a particular fan of PHP.
I'm not exactly dogmatic about PHP either. It's just the first approach
to the web-app topic for me. One has to start somewhere.   :-}

I'll need to let some specific external users access our PG DB that up
until now uses a MS-Access frontend.
PHP seemed to be the easiest approach without having to mess around with
ASP or JAVA and all this.

>> It appeared that it was for everybody but me evident that instead of
>> heaving a db-role+passwd for every user of an application it was
>> better to have just 1 set of db-credentials for the application and
>> recreate a user management within the app instead using the existing
>> user handling of the dbms.
> I usually prefer a hybrid, where the app logs in with a particular
> role with limited rights then does a SET ROLE to the app user it's
> currently operating as. Related to:
>
>
http://stackoverflow.com/questions/8432636/in-postgresql-are-partitions-or-multiple-databases-more-efficient/8439618#8439618

>

I wasn't aware of the possibility to switch roles, yet.
I'll explore this in more detail.


Thanks

On Thu, Dec 15, 2011 at 4:16 AM, Andreas <maps.on@gmx.net> wrote:
> Well, it was on the general list of php.net.
> I read your link and understood your not a particular fan of PHP.
> I'm not exactly dogmatic about PHP either. It's just the first approach to
> the web-app topic for me. One has to start somewhere.   :-}
>

The biggest problem with PHP, imho, is actually that it's so easy to
use. Anyone can get a WYSIWYG editor, save as HTML, and have a web
page... and then all you need to do is rename it to ".php" and put
some special tags in it, and look! You have a dynamic web page and
it's so awesome! At least, it is until you try to go further, and you
start adding mess on top of mess on top of mess.

There are a few more fundamental issues with the language, but mainly,
it gets the blame for myriad bad PHP programmers.

I prefer Pike. It's designed for writing servers, performance is
pretty decent, it's a high level language, and it has great database
support (including Postgres-specific features, some of which are quite
handy).

Chris Angelico

On Wed, 14 Dec 2011, Andreas wrote:

> Hi,
>
> I asked elsewhere about the best way to store db credentials within a
> user-session of a web-app.

You might give this a read:

http://database-programmer.blogspot.com/2009/02/comprehensive-database-security-model.html

It goes through how using a DB user per app user would work.

I don't necessarily agree with it, but it's good reading.

-David

 Onsdag 14. desember 2011 22.21.04 skrev Chris Angelico :
> The biggest problem with PHP, imho, is actually that it's so easy to
> use. Anyone can get a WYSIWYG editor, save as HTML, and have a web
> page... and then all you need to do is rename it to ".php" and put
> some special tags in it, and look! You have a dynamic web page and
> it's so awesome! At least, it is until you try to go further, and you
> start adding mess on top of mess on top of mess.

In my opinion, that's a pretty elitistic view. Certainly, that's one way of
writing PHP, but it isn't the only one. Quite a few of us have started with
something like what you've outlined here, but have long ago moved on to more
maintainable coding practices.

The good thing about PHP is the low threshold, and you can start using it
doing exactly what you outlined in your first paragraph. But somebody coming to
PHP from any old procedural language, will soon find that PHP lends itself well
to building function upon function, until you can really write the code you
need to express anything you want.

> There are a few more fundamental issues with the language, but mainly,
> it gets the blame for myriad bad PHP programmers.

Yes there's a lot of bad programmers out there. Most of them code in Java or
Visual Basic.

> I prefer Pike. It's designed for writing servers, performance is
> pretty decent, it's a high level language, and it has great database
> support (including Postgres-specific features, some of which are quite
> handy).

I'd like to look at this Pike. I don't think that my Web host supports it, but
it might still be a fun experience.

regards, Leif

The Yggdrasil project:
http://code.google.com/p/yggdrasil-genealogy/

This is somewhat of a diversion but....

On Wed, Dec 14, 2011 at 4:25 PM, Leif Biberg Kristensen
<leif@solumslekt.org> wrote:
>  Onsdag 14. desember 2011 22.21.04 skrev Chris Angelico :
>> The biggest problem with PHP, imho, is actually that it's so easy to
>> use. Anyone can get a WYSIWYG editor, save as HTML, and have a web
>> page... and then all you need to do is rename it to ".php" and put
>> some special tags in it, and look! You have a dynamic web page and
>> it's so awesome! At least, it is until you try to go further, and you
>> start adding mess on top of mess on top of mess.
>
> In my opinion, that's a pretty elitistic view. Certainly, that's one way of
> writing PHP, but it isn't the only one. Quite a few of us have started with
> something like what you've outlined here, but have long ago moved on to more
> maintainable coding practices.
>
> The good thing about PHP is the low threshold, and you can start using it
> doing exactly what you outlined in your first paragraph. But somebody coming to
> PHP from any old procedural language, will soon find that PHP lends itself well
> to building function upon function, until you can really write the code you
> need to express anything you want.

I take a different view.

I actually think PHP is not a bad preprocessor for SGML documents.
The fact that it pretty much sucks for everything else doesn't
undermine that.  This is one of the reasons why I think PL/PHP really
should sit in a very tiny niche--- it could be very useful when
combined with the xml data type, but I can't think of any other really
defensible applications.

If you look at PHP solely as an SGML preprocessor, it's just fine.
However the further you get from this role, the more it breaks down.
If there is a big criticism of PHP to be had, its that it breaks down
too gracefully leading to programs written in the language which would
be far better written in other languages.  I can think of a few I have
written which would have been far better off written in Perl or
sed......

Hope this helps,
Chris Travers

On 12/15/2011 01:16 AM, Andreas wrote:
> Am 14.12.2011 14:28, schrieb Craig Ringer:
>> On 14/12/2011 8:32 PM, Andreas wrote:
>>> Hi,
>>>
>>> I asked elsewhere about the best way to store db credentials within
>>> a user-session of a web-app.
>>>
>> Where? Link?
>
> Well, it was on the general list of php.net.
> I read your link and understood your not a particular fan of PHP.
I'm not, but all I was referring to there is that PHP's persistent
connections are a very basic tool that for decent performance must be
used with a connection pool. That can be either a pool built in to the
database for those DBs that support it or via a proxy like PgPool or
PgBouncer for DBs like Pg that don't.

If that gave the impression I really dislike the language it wasn't
intended. The runtime isn't as sophisticated, sure, but that has
advantages as well as downsides.

PHP is really handy for quickly putting together basic webapps and has a
lot going for it, especially if you use the more recent versions, enable
the security features, and ALWAYS USE PARAMETERISED STATEMENTS in SQL
rather than string concatenation.

> I'll need to let some specific external users access our PG DB that up
> until now uses a MS-Access frontend.
> PHP seemed to be the easiest approach without having to mess around
> with ASP or JAVA and all this.

Yep, it probably is. The only other thing I'd maybe want to play with is
JBoss Seam Forge - however it's pretty immature and rather under-documented.

--
Craig Ringer

Em 14/12/2011 22:25, Leif Biberg Kristensen escreveu:
>   Onsdag 14. desember 2011 22.21.04 skrev Chris Angelico :
>> The biggest problem with PHP, imho, is actually that it's so easy to
>> use. Anyone can get a WYSIWYG editor, save as HTML, and have a web
>> page... and then all you need to do is rename it to ".php" and put
>> some special tags in it, and look! You have a dynamic web page and
>> it's so awesome! At least, it is until you try to go further, and you
>> start adding mess on top of mess on top of mess.
You can have the same with JSP (Java Server Pages). I really dislike it.
You will be most by using OOP, a good design and a real, planned
architecture.
> In my opinion, that's a pretty elitistic view. Certainly, that's one way of
> writing PHP, but it isn't the only one. Quite a few of us have started with
> something like what you've outlined here, but have long ago moved on to more
> maintainable coding practices.
>
> The good thing about PHP is the low threshold, and you can start using it
> doing exactly what you outlined in your first paragraph. But somebody coming to
> PHP from any old procedural language, will soon find that PHP lends itself well
> to building function upon function, until you can really write the code you
> need to express anything you want.
>
>> There are a few more fundamental issues with the language, but mainly,
>> it gets the blame for myriad bad PHP programmers.
> Yes there's a lot of bad programmers out there. Most of them code in Java or
> Visual Basic.
Comments like these seems FUD for me. You can't judge people by the
language of choice, but by the bad programming habits (IT: I do program
in Java, as well in C, Cobol, Visual Basic, C#, few functional languages
and some scripting languages). I can see that most bad programmers use
VB or Java because these are the most used languages in the whole
world... But I believe that there are more bad Javascript programmers
(just check how many beauty-and-crap-never-work-as-planned-sites exists
all around the world), only Javascript is not counted as a real
programming language (who knows why?).

You will find bad programmers on any language, including some languages
that do not exists yet. Is the same if I say that bad DBA uses database
X instead PostgreSQL. This is not true, because we can have bad DBA
using PostgreSQL.

FUD apart, I would add that by using the Java Platform (not the language
- you can choose the language you want to use among hundreds JVM
options), you can leverage authorization and authentication to well
known and proven platform (and APIs) for user identification, that can
easily rely on Database of choice, LDAP, Kerberos, NT-Auth, Linux auth
among others (you can even extend and create a completely new one).

Once the user has been authenticated, you can easly use JAAS API to get
trusted user name (the one that has passed the authentication method of
choice) to leverage your decision on how to connect to database (either
by using generic database user, or by using set session authorization, etc).

If you write your own provider, you can customize the code in order to
have the database connection available directly by the custom JAAS
provider. So you will have the best of any world you judge better for
your needs.

Regards,

Edson.
>> I prefer Pike. It's designed for writing servers, performance is
>> pretty decent, it's a high level language, and it has great database
>> support (including Postgres-specific features, some of which are quite
>> handy).
> I'd like to look at this Pike. I don't think that my Web host supports it, but
> it might still be a fun experience.
>
> regards, Leif
>
> The Yggdrasil project:
> http://code.google.com/p/yggdrasil-genealogy/
>

On Thu, Dec 15, 2011 at 11:25 AM, Leif Biberg Kristensen
<leif@solumslekt.org> wrote:
>  Onsdag 14. desember 2011 22.21.04 skrev Chris Angelico :
>> The biggest problem with PHP, imho, is actually that it's so easy to
>> use. Anyone can get a WYSIWYG editor, save as HTML, and have a web
>> page... and then all you need to do is rename it to ".php" and put
>> some special tags in it, and look! You have a dynamic web page and
>> it's so awesome! At least, it is until you try to go further, and you
>> start adding mess on top of mess on top of mess.
>
> In my opinion, that's a pretty elitistic view. Certainly, that's one way of
> writing PHP, but it isn't the only one. Quite a few of us have started with
> something like what you've outlined here, but have long ago moved on to more
> maintainable coding practices.

Yes, many people have started badly and grown from there. The trouble
is that there are people - and, I think, quite a large number - who
start badly and stay there because they're able to do what they want.
And some of them are professional programmers, and that's one reason
why I see a lot of bad PHP and Javascript code around.

> The good thing about PHP is the low threshold, and you can start using it
> doing exactly what you outlined in your first paragraph. But somebody coming to
> PHP from any old procedural language, will soon find that PHP lends itself well
> to building function upon function, until you can really write the code you
> need to express anything you want.

Yes, it's good to have a low threshold. Yes, if you have programming
expertise, you can express yourself in PHP. Doesn't change the fact
that people who do NOT have programming expertise can get the idea
that they can code, based on an example copied and pasted from the web
and then modified a bit - and the fact that this happens in publishing
environments, not in the safety of someone's private/personal
experimentation.

[Inserting a quote from Edson Richter <richter@simkorp.com.br>]
> But I believe that there are more bad Javascript programmers (just
> check how many beauty-and-crap-never-work-as-planned-sites exists
> all around the world), only Javascript is not counted as a real
> programming language (who knows why?).

This is the same principle at work. In my opinion, Javascript is a
perfectly valid language (okay, apart from the naming mess -
Javascript, JavaScript, ECMAScript, etc, etc, etc - fold all of them
together into the notional entity that most people think of, and it's
a fine language). It runs into the same problem as PHP does, with the
additional low-ness of barrier to entry that you don't even need to
have a web server that supports it. Good language, too many bad coders
using it.

[Back to Leif Biberg Kristensen <leif@solumslekt.org>]
>> There are a few more fundamental issues with the language, but mainly,
>> it gets the blame for myriad bad PHP programmers.
>
> Yes there's a lot of bad programmers out there. Most of them code in Java or
> Visual Basic.

Yep, Java and VB get bad programmers too. So does C. My point is,
though, that the _extremely_ low barrier to entry means that PHP gets
a particular class of novice programmer and puts tools in their hands
that some never bother to master, resulting in horrendous PHP code
that's a nightmare to handle. I was stating that the biggest problem
with PHP is bad PHP programmers, not bad language features. Although
this above statement of mine is a little ambiguous; what I meant is
that I do have some fundamental issues with the language (eg its
scoping rules are a bit odd, and adorning variable names feels like
syntactic salt, and it has acquired a lot of cruft eg
register_globals), but that these issues are not really all that
significant compared to the likelihood that a random piece of PHP code
will be bad.

>> I prefer Pike. It's designed for writing servers, performance is
>> pretty decent, it's a high level language, and it has great database
>> support (including Postgres-specific features, some of which are quite
>> handy).
>
> I'd like to look at this Pike. I don't think that my Web host supports it, but
> it might still be a fun experience.

It's extremely unlikely that a cheap web host will support it; Pike's
designed for writing servers, not writing dynamic web pages. One of
Pike's killer apps is a web server called Roxen, which (far as I know)
will do everything that most people want Apache to do; but the
language's primary derivation is from the world of MUDs. I have a
server that's been running for a year and a half, and a Pike process
that's been running on it ever since I fired up the server; it never
needs to shut down, even to bring new code online.

You can download a stable release from http://pike.ida.liu.se/ or get
the latest source code via git. As a language, Pike has a lot going
for it, but unfortunately the docs are somewhat lacking in places
(particularly the GUI facilities - yes, a language designed for
servers has GTK bindings).

Chris Angelico

A practice I like that I've seen done for a federal-government scale database
program is to have each person using the application to login to the database
using their own temporary database user.  How it works is that the database has
a users table similar to as if the application was managing users itself, and
when the person logs in they are using credentials defined in that table.  What
actually happens for login is that there is a special database user which only
has privileges to execute a single stored procedure, and the application's login
screen talks to the database with that special user and invokes the procedure,
giving the person-provided user and pass as procedure arguments.  The stored
procedure checks the database table, and if the credentials are accepted, the
procedure then generates a new database user and password and gives these back
to the application, which then turns around and logs in as the temporary user in
order to do all the normal work of the person.  This generated user only has the
privileges that the person needs.  This approach seems to have security benefits
of some kinds. -- Darren Duncan

Darren Duncan wrote:
> A practice I like that I've seen done for a federal-government scale
> database program is to have each person using the application to login
> to the database using their own temporary database user.  How it works
> is that the database has a users table similar to as if the application
> was managing users itself, and when the person logs in they are using
> credentials defined in that table.  What actually happens for login is
> that there is a special database user which only has privileges to
> execute a single stored procedure, and the application's login screen
> talks to the database with that special user and invokes the procedure,
> giving the person-provided user and pass as procedure arguments.  The
> stored procedure checks the database table, and if the credentials are
> accepted, the procedure then generates a new database user and password
> and gives these back to the application, which then turns around and
> logs in as the temporary user in order to do all the normal work of the
> person.  This generated user only has the privileges that the person
> needs.  This approach seems to have security benefits of some kinds. --
> Darren Duncan

I should also clarify that this was a decade ago and the context was a desktop
application which maintained an open connection for the person's work day.
Although I think the same technique was also used there for web applications
afterwards, that used the same database, if I don't mis-remember.  I believe
this was an Oracle 8 DBMS. -- Darren Duncan

On Wed, Dec 14, 2011 at 8:14 PM, Chris Angelico <rosuav@gmail.com> wrote:

>> In my opinion, that's a pretty elitistic view. Certainly, that's one way of
>> writing PHP, but it isn't the only one. Quite a few of us have started with
>> something like what you've outlined here, but have long ago moved on to more
>> maintainable coding practices.
>
> Yes, many people have started badly and grown from there. The trouble
> is that there are people - and, I think, quite a large number - who
> start badly and stay there because they're able to do what they want.
> And some of them are professional programmers, and that's one reason
> why I see a lot of bad PHP and Javascript code around.

That's not a problem with PHP.  I could name projects in Perl where a
single developer started programming badly and 12 years later has
barely improved.........  I argue the same for Python but haven't come
across such projects yet.

>
>> The good thing about PHP is the low threshold, and you can start using it
>> doing exactly what you outlined in your first paragraph. But somebody coming to
>> PHP from any old procedural language, will soon find that PHP lends itself well
>> to building function upon function, until you can really write the code you
>> need to express anything you want.
>
> Yes, it's good to have a low threshold. Yes, if you have programming
> expertise, you can express yourself in PHP. Doesn't change the fact
> that people who do NOT have programming expertise can get the idea
> that they can code, based on an example copied and pasted from the web
> and then modified a bit - and the fact that this happens in publishing
> environments, not in the safety of someone's private/personal
> experimentation.

To be fair, isn't that an argument against whatever the popular
programming language is of the day?  I mean, we can all remember
Matt's Scripting Archive and Perl.....

>
> [Inserting a quote from Edson Richter <richter@simkorp.com.br>]
>> But I believe that there are more bad Javascript programmers (just
>> check how many beauty-and-crap-never-work-as-planned-sites exists
>> all around the world), only Javascript is not counted as a real
>> programming language (who knows why?).
>
> This is the same principle at work. In my opinion, Javascript is a
> perfectly valid language (okay, apart from the naming mess -
> Javascript, JavaScript, ECMAScript, etc, etc, etc - fold all of them
> together into the notional entity that most people think of, and it's
> a fine language). It runs into the same problem as PHP does, with the
> additional low-ness of barrier to entry that you don't even need to
> have a web server that supports it. Good language, too many bad coders
> using it.

Well, Javascript and PHP actually have two fundamentally different
issues associated with them.

PHP is lexically bound to the task of preprocessing SGML files, which
is why all programming logic is typically contained within SGML PI
tags.  It's sort an upside-down templating system.  When you combine
this feature with the low barrier to entry things can get especially
bad, specially as people start to try to do things like write GTK
programs in the language (something it is most definitely not designed
for).  Additionally you have the fact that a lot of PHP documentation
included examples which were subject to SQL injection exploits and the
like......

Javascript is a nice light-weight automation language but until
recently (and arguably even today!) it's use was largely limited to
client-side scripting for web pages.  Here you have a low barrier to
entry combined with a niche where the barrier to entry probably should
be reasonably low in the area of light-weight scripting, and so you
run into problems when people start doing heavier-weight scripting.  I
the issues here are lower and the language is starting to spread out
to other areas (NoSQL, node.js, and the like).

There is a lot of bad Perl code out there too though.
>
> [Back to Leif Biberg Kristensen <leif@solumslekt.org>]
>>> There are a few more fundamental issues with the language, but mainly,
>>> it gets the blame for myriad bad PHP programmers.
>>
>> Yes there's a lot of bad programmers out there. Most of them code in Java or
>> Visual Basic.
>
> Yep, Java and VB get bad programmers too. So does C. My point is,
> though, that the _extremely_ low barrier to entry means that PHP gets
> a particular class of novice programmer and puts tools in their hands
> that some never bother to master, resulting in horrendous PHP code
> that's a nightmare to handle. I was stating that the biggest problem
> with PHP is bad PHP programmers, not bad language features. Although
> this above statement of mine is a little ambiguous; what I meant is
> that I do have some fundamental issues with the language (eg its
> scoping rules are a bit odd, and adorning variable names feels like
> syntactic salt, and it has acquired a lot of cruft eg
> register_globals), but that these issues are not really all that
> significant compared to the likelihood that a random piece of PHP code
> will be bad.

I know of 200k line Perl projects though which could easily serve as:

1)  A textbook on how not to program generally
2)  A textbook on how not to program Perl in particular
3)  A textbook on how not to write secure code.....

So the problem is hardly limited to PHP.

Best Wishes,
Chris Travers

On Thu, Dec 15, 2011 at 4:23 PM, Chris Travers <chris.travers@gmail.com> wrote:
> So the problem is hardly limited to PHP.

Oh, it definitely is not. Really, it's a problem with human beings who
think they're programmers. It just seems to occur more frequently in
some languages than others.

ChrisA

Am 15.12.2011 05:47, schrieb Darren Duncan:
> Darren Duncan wrote:
>> A practice I like that I've seen done for a federal-government scale
>> database program is to have each person using the application to
>> login to the database using their own temporary database user.  How
>> it works is that the database has a users table similar to as if the
>> application was managing users itself, and when the person logs in
>> they are using credentials defined in that table.  What actually
>> happens for login is that there is a special database user which only
>> has privileges to execute a single stored procedure, and the
>> application's login screen talks to the database with that special
>> user and invokes the procedure, giving the person-provided user and
>> pass as procedure arguments.  The stored procedure checks the
>> database table, and if the credentials are accepted, the procedure
>> then generates a new database user and password and gives these back
>> to the application, which then turns around and logs in as the
>> temporary user in order to do all the normal work of the person.
>> This generated user only has the privileges that the person needs.
>> This approach seems to have security benefits of some kinds. --
>> Darren Duncan
>
> I should also clarify that this was a decade ago and the context was a
> desktop application which maintained an open connection for the
> person's work day. Although I think the same technique was also used
> there for web applications afterwards, that used the same database, if
> I don't mis-remember.  I believe this was an Oracle 8 DBMS. -- Darren
> Duncan
>
I see.
How did they do a "garbage collection" of those temporary users.
A  desktop app does somehow know when it comes to it's end and could
remove the temp user
... provided it didn't crash.

On the other hand a web app might logically be running over a lot of
pages but AFAIK in reality it's processing terminates after every page.
0)  ...
1)  app connects to DB and fetches some stuff
2)  app processes stuff and generates some html form
3)  app disconnects from DB
4)  app sends the form to browser and browser displays that form
5)  app terminates while storing some status stuff e.g. in a session
6)  browser waits for input and some click event to restart the app
7)  browser sends back the data and restarts the app

One could drop the temp-user everytime after step 3 so the DB stays
uncluttered but the on every reconnect there has to be generated a new
temp-user and that looks inefficient.

As is the nature of web apps the user might deside that after step 6) he
rather goes to step xxx) and does some comparative anatomic design
studies in a particularly more interesting corner of the web.
So the web app won't reach step 7 and somehow the dangling temp-user
should be removed.

This needed external scripting probaply triggered by cron every X minutes?

On Thu, Dec 15, 2011 at 1:49 AM, Andreas <maps.on@gmx.net> wrote:
> Am 15.12.2011 05:47, schrieb Darren Duncan:
>
>> Darren Duncan wrote:
>>>
>>> A practice I like that I've seen done for a federal-government scale
>>> database program is to have each person using the application to login to
>>> the database using their own temporary database user.  How it works is that
>>> the database has a users table similar to as if the application was managing
>>> users itself, and when the person logs in they are using credentials defined
>>> in that table.  What actually happens for login is that there is a special
>>> database user which only has privileges to execute a single stored
>>> procedure, and the application's login screen talks to the database with
>>> that special user and invokes the procedure, giving the person-provided user
>>> and pass as procedure arguments.  The stored procedure checks the database
>>> table, and if the credentials are accepted, the procedure then generates a
>>> new database user and password and gives these back to the application,
>>> which then turns around and logs in as the temporary user in order to do all
>>> the normal work of the person.  This generated user only has the privileges
>>> that the person needs.  This approach seems to have security benefits of
>>> some kinds. -- Darren Duncan
>>
>>
>> I should also clarify that this was a decade ago and the context was a
>> desktop application which maintained an open connection for the person's
>> work day. Although I think the same technique was also used there for web
>> applications afterwards, that used the same database, if I don't
>> mis-remember.  I believe this was an Oracle 8 DBMS. -- Darren Duncan
>>
> I see.
> How did they do a "garbage collection" of those temporary users.
> A  desktop app does somehow know when it comes to it's end and could remove
> the temp user
> ... provided it didn't crash.
>
> On the other hand a web app might logically be running over a lot of pages
> but AFAIK in reality it's processing terminates after every page.
> 0)  ...
> 1)  app connects to DB and fetches some stuff
> 2)  app processes stuff and generates some html form
> 3)  app disconnects from DB
> 4)  app sends the form to browser and browser displays that form
> 5)  app terminates while storing some status stuff e.g. in a session
> 6)  browser waits for input and some click event to restart the app
> 7)  browser sends back the data and restarts the app
>
> One could drop the temp-user everytime after step 3 so the DB stays
> uncluttered but the on every reconnect there has to be generated a new
> temp-user and that looks inefficient.

LedgerSMB has to deal with a similar issue regarding discretionary
locks on transactions and open form id's tied to users sessions.  In a
web app this can be problematic.

So what we do is have everything set up so when the user's session is
deleted all locking info is also deleted.  On each page request, when
we check the session table (which is usually less than one page in
size), we check for old sessions and delete them.  If there are no
stale sessions, this is pretty painless.  If there are stale sessions,
and the db is large, then this takes the page takes a bit longer to
load but this is a rare case.

Best Wishes,
Chris Travers

On Thu, Dec 15, 2011 at 1:58 AM, Bèrto ëd Sèra <berto.d.sera@gmail.com> wrote:

> My main problem with PHP is that it has loads of issues upon changing
> versions, there are a lot of applications based on it, and sooner or later
> you bump into having one application in need for one version and another
> being unable to run under it. And you obviously need them both. Gentoo will
> slot versions, so that you can run a number of them, but this is not a
> guarantee that you shall be able to slot minor versions, currently you can
> have any combination of:
>         (5.2)   [M]5.2.17
>         (5.3)   5.3.8 ~5.3.9_rc1 ~5.3.9_rc2
>         (5.4)   ~5.4.0_rc3
>
> This is large bouquet, but it does not mean that APC cache (for example)
> will work with all those versions, we had a number of issues with this.

I ran into a similar problem recently where Ubuntu refuses to upgrade
to the latest bug fix, and the bugs that remain unfixed are killer,
i.e. backends sig-11 dying upon trying to use memcache etc.  Way back
in the day when I was building web servers on apache 1.2, postgres
6.5, and php 4.0.x, I was used to having to download source tarballs
and compiling everything by hand.  I don't expect to have to do that
in 2011, but that's what I was looking at.  OK, I was building my own
packages now for a farm but still, way more pain than I should have to
deal with.

Debian / Ubuntu will release an updated pg pacakge days after a new
postgresql minor version comes out, but php?  It can be months or
years before a new minor version gets the go ahead.  and like you
said, the fact that changing minor versions seems to break a lot of
stuff is the reason why.

On Fri, Dec 16, 2011 at 3:28 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> Debian / Ubuntu will release an updated pg pacakge days after a new
> postgresql minor version comes out, but php?  It can be months or
> years before a new minor version gets the go ahead

The beauty of modern open source project management is that, when you
don't like your packager's policies, you can just grab the latest
source package (or clone source-control) and build your own, without
much difficulty.

ChrisA