Best practice? Web application: single PostgreSQL user vs. multiple users

Keith G. Murphy said:

2) have the web server connecting to the database actually using the
user's account (possibly using LDAP authentication against PostgreSQL),
and controlling access to different database entities through GRANT, etc.

My experience with java web/app servers indicates that for most setups
using a pool of connections is preferable to using a single connection per
connected user - it scales much better.

What you could consider is one or more pools which map to the "roles" that
your (web) app supports. For example, if a user needs "minimal rights"
access to db resources, then your cgi (request handler) accesses the data
using a connection from the "minimal rights" connection pool. A user
needing "greater rights" would have the cgi access the database from the
"greater rights" pool.

Normally, I place the database functions/tables/objects into different
logical schemas, then I create one or more specific users (for the web/app
server only) which equates to a logical role, and I grant specific rights
on the different schema objects to those users.

Your mileage may vary.

John Sidney-Woollett

i have always tended towards building simple user authentication
structures into the application, rather than the database.

historically i've been a ms sql developer and have seen both
environments modelled in the microsoft sphere. doing everything in
the database is good if you're building a heavily database
orientated application (cos you get all the advantages of knowing
whos doing what in procedures and triggers and stuff) but can be
a headache if you're not.

the other main issue I've had in the past is migration and support.
you're relying on being able run your application in the same
sort of environment ongoing. this may not be a problem if you're
happy to tie yourself into LDAP / Postgres as a platform.

as for security the main concern (true in the windows world too) is
that you don't usually want to give users authenticated on the
network any sort of access to the database, which you have to do
if you're integrating security models.

at the same time LDAP provides a whole host of security benefits
that you must be careful not to ignore if you do it yourself.

personally i prefer the seperated application approach with mixtures
of security in the application layer using 1 & 2 way encryption, ssl
over http and that sort of thing.

-----Original Message-----
From: Keith G. Murphy [mailto:keithmur@mindspring.com]
Sent: 13 January 2004 16:13
To: pgsql-general
Subject: [GENERAL] Best practice? Web application: single PostgreSQL
user vs. multiple users

I'm trying to get a feel for what most people are doing or consider best
practice.

Given a mod_perl application talking to a PostgreSQL database on the
same host, where different users are logging onto the web server using
LDAP for authentication, do most people

1) have the web server connecting to the database using its own user
account (possibly through ident), and controlling access to different
database entities strictly through the application itself

2) have the web server connecting to the database actually using the
user's account (possibly using LDAP authentication against PostgreSQL),
and controlling access to different database entities through GRANT, etc.

Obviously, (2) leads to more database connections, and you still have to
have the application do some work in terms of which forms are available
to which users, etc. But I'm a little worried about whether it's best
security practice.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

________________________________________________________________________
This e-mail has been scanned for all viruses by Star Internet. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, visit:
http://www.star.net.uk
________________________________________________________________________

________________________________________________________________________
This e-mail has been scanned for all viruses by Star Internet. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, visit:
http://www.star.net.uk
________________________________________________________________________

John Sidney-Woollett wrote:

Keith G. Murphy said:

2) have the web server connecting to the database actually using the
user's account (possibly using LDAP authentication against PostgreSQL),
and controlling access to different database entities through GRANT, etc.

My experience with java web/app servers indicates that for most setups
using a pool of connections is preferable to using a single connection per
connected user - it scales much better.

What you could consider is one or more pools which map to the "roles" that
your (web) app supports. For example, if a user needs "minimal rights"
access to db resources, then your cgi (request handler) accesses the data
using a connection from the "minimal rights" connection pool. A user
needing "greater rights" would have the cgi access the database from the
"greater rights" pool.

That sounds like an excellent compromise. How do you typically handle
the mechanics of authentication from web server to PostgreSQL on the
connect, using this scheme?
--
Why waste time learning when ignorance is instantaneous?
-- Hobbes

Keith G. Murphy said:

That sounds like an excellent compromise. How do you typically handle
the mechanics of authentication from web server to PostgreSQL on the
connect, using this scheme?

Sorry but I can't help you out here, I'm too much of a newbie with
Postgres - I was hoping that someone else would answer your part 1! :)

John

John Sidney-Woollett wrote:

Keith G. Murphy said:

That sounds like an excellent compromise. How do you typically handle
the mechanics of authentication from web server to PostgreSQL on the
connect, using this scheme?

Sorry but I can't help you out here, I'm too much of a newbie with
Postgres - I was hoping that someone else would answer your part 1! :)

John

Perhaps I can answer my own question. I could use ident and a map that
lists the web server username as able to map to the different "role"
usernames. Unfortunately, that still would allow the web server account
to "fake" role names.

If the "real" PostgreSQL accounts do not coincide to the
browser-authenticated usernames, I don't see a good way to use PAM/LDAP
or another mechanism to require that PostgreSQL itself makes sure that
the given username and password are valid. Not saying that's a big
problem, but...

Hmmm, mightn't it be kind of nice if there were PAM or krb5 maps in
addition to ident maps?
--
Why waste time learning when ignorance is instantaneous?
-- Hobbes

Keith G. Murphy said:

Perhaps I can answer my own question. I could use ident and a map that
lists the web server username as able to map to the different "role"
usernames.

Someone else also mentioned and I personally agree that it's better to
authenticate in the application layer (using whatever technology takes
your fancy), and then use the webserver's generic/pooled connection to
communicate with the database.

Your user and role mapping info could be stored within the database, or
accessed from an LDAP server, or some such.

Unfortunately, that still would allow the web server account
to "fake" role names.

Make the application layer robust and secure and it may not be so much of
a problem.

John

On Tue, 13 Jan 2004, Keith G. Murphy wrote:

I'm trying to get a feel for what most people are doing or consider best
practice.

Given a mod_perl application talking to a PostgreSQL database on the
same host, where different users are logging onto the web server using
LDAP for authentication, do most people

1) have the web server connecting to the database using its own user
account (possibly through ident), and controlling access to different
database entities strictly through the application itself

2) have the web server connecting to the database actually using the
user's account (possibly using LDAP authentication against PostgreSQL),
and controlling access to different database entities through GRANT, etc.

Obviously, (2) leads to more database connections, and you still have to
have the application do some work in terms of which forms are available
to which users, etc. But I'm a little worried about whether it's best
security practice.

I do 1. different language (PHP) same basic thing though. All security
is handled by ACLS I build myself in Postgresql and interrogate via my own
application.

"Keith G. Murphy" <keithmur@mindspring.com> writes:

Hmmm, mightn't it be kind of nice if there were PAM or krb5 maps in
addition to ident maps?

ISTM the whole point of PAM is that you plug in your desired security
policy outside of the application. You shouldn't be asking for more
security frammishes from Postgres, you should be off coding a PAM module
that does things exactly the way you want.

regards, tom lane

Tom Lane wrote:

"Keith G. Murphy" <keithmur@mindspring.com> writes:

Hmmm, mightn't it be kind of nice if there were PAM or krb5 maps in
addition to ident maps?

ISTM the whole point of PAM is that you plug in your desired security
policy outside of the application. You shouldn't be asking for more
security frammishes from Postgres, you should be off coding a PAM module
that does things exactly the way you want.

I believe I see what you mean. Given the original premise, I imagine
you could have the PAM module do something like:

(1) Authenticate via LDAP using the user's username and password

(2) Look up the "role" name (real PostgreSQL username) via LDAP, using
the username

(3) Tell PostsgreSQL that the user is authenticated under role name.

I really hadn't thought much about how the PAM module might work.
--
Why waste time learning when ignorance is instantaneous?
-- Hobbes

On Tue, Jan 13, 2004 at 11:15:30 -0600,
"Keith G. Murphy" <keithmur@mindspring.com> wrote:

Perhaps I can answer my own question. I could use ident and a map that
lists the web server username as able to map to the different "role"
usernames. Unfortunately, that still would allow the web server account
to "fake" role names.

If you can't trust the web server account then you probably want to use
a system where cgi-bin programs are run as different users.

If you have untrusted users who can supply their own cgi-bin programs
then using a common uid which all cgi-bin programs run under isn't
secure.

If the "real" PostgreSQL accounts do not coincide to the
browser-authenticated usernames, I don't see a good way to use PAM/LDAP
or another mechanism to require that PostgreSQL itself makes sure that
the given username and password are valid. Not saying that's a big
problem, but...

I don't think using information received from the browser to authenticate
versus the postgres server works when you can't be assured that the
cgi-bin program doing the checking is trustworthy.

On Tue, 13 Jan 2004, Keith G. Murphy wrote:

John Sidney-Woollett wrote:

Keith G. Murphy said:

2) have the web server connecting to the database actually using the
user's account (possibly using LDAP authentication against PostgreSQL),
and controlling access to different database entities through GRANT, etc.

My experience with java web/app servers indicates that for most setups
using a pool of connections is preferable to using a single connection per
connected user - it scales much better.

What you could consider is one or more pools which map to the "roles" that
your (web) app supports. For example, if a user needs "minimal rights"
access to db resources, then your cgi (request handler) accesses the data
using a connection from the "minimal rights" connection pool. A user
needing "greater rights" would have the cgi access the database from the
"greater rights" pool.

That sounds like an excellent compromise. How do you typically handle
the mechanics of authentication from web server to PostgreSQL on the
connect, using this scheme?

I create individual databases for unrelated projects (like say, phonebook
and sales_projections and then connect to each database as a different
artificial user often named for the database. Then I usually wrap that in
an include file I just add at the top of each page that connects and has
the password (on systems using password authentication) or that connects
without a password if I'm on a system using trust.

Then, any access by users is handled by ACLs I just build in a table in
that database.

We authenticate with auth_ldap, so we always know the user's name / groups
etc...

On Tue, 13 Jan 2004, Keith G. Murphy wrote:

John Sidney-Woollett wrote:

Keith G. Murphy said:

2) have the web server connecting to the database actually using the
user's account (possibly using LDAP authentication against PostgreSQL),
and controlling access to different database entities through GRANT, etc.

My experience with java web/app servers indicates that for most setups
using a pool of connections is preferable to using a single connection per
connected user - it scales much better.

What you could consider is one or more pools which map to the "roles" that
your (web) app supports. For example, if a user needs "minimal rights"
access to db resources, then your cgi (request handler) accesses the data
using a connection from the "minimal rights" connection pool. A user
needing "greater rights" would have the cgi access the database from the
"greater rights" pool.

That sounds like an excellent compromise. How do you typically handle
the mechanics of authentication from web server to PostgreSQL on the
connect, using this scheme?

Just an addition, we do all our groups in LDAP too. Generally ACLs point
back to groups, not users. that way if billy bob moves from finance to HR
we just change his group memberships, not all the ACLs in all the
databases.

On Tue, 13 Jan 2004, Keith G. Murphy wrote:

I'm trying to get a feel for what most people are doing or consider best
practice.

Given a mod_perl application talking to a PostgreSQL database on the
same host, where different users are logging onto the web server using
LDAP for authentication, do most people

1) have the web server connecting to the database using its own user
account (possibly through ident), and controlling access to different
database entities strictly through the application itself

2) have the web server connecting to the database actually using the
user's account (possibly using LDAP authentication against PostgreSQL),
and controlling access to different database entities through GRANT, etc.

Obviously, (2) leads to more database connections, and you still have to
have the application do some work in terms of which forms are available
to which users, etc. But I'm a little worried about whether it's best
security practice.

I do #1- most connections are not persistent though I have done those
before as well. Security-wise I'm been reviewing the pros and cons
of this and so far I really can't make much of an argument for #2.
Just the opposite in fact. The one thing I will be doing though,
for more security, is create a special webuser for that client.

---
Keith C. Perry
Director of Networks & Applications
Visions Communications Support Network, Inc.
netadmin@vcsn.com
http://vcsn.com
---

scott.marlowe wrote:

On Tue, 13 Jan 2004, Keith G. Murphy wrote:

I'm trying to get a feel for what most people are doing or consider best
practice.

Given a mod_perl application talking to a PostgreSQL database on the
same host, where different users are logging onto the web server using
LDAP for authentication, do most people

1) have the web server connecting to the database using its own user
account (possibly through ident), and controlling access to different
database entities strictly through the application itself

2) have the web server connecting to the database actually using the
user's account (possibly using LDAP authentication against PostgreSQL),
and controlling access to different database entities through GRANT, etc.

Obviously, (2) leads to more database connections, and you still have to
have the application do some work in terms of which forms are available
to which users, etc. But I'm a little worried about whether it's best
security practice.

I do 1. different language (PHP) same basic thing though. All security
is handled by ACLS I build myself in Postgresql and interrogate via my own
application.

Thanks to all for the discussion so far. I have been doing option 1 so
far as well, but was a bit uncomfortable allowing the web server account
to have complete control over the database, and wondered what the
alternatives might be.

At some point, I may try rolling my own PAM module (as Tom Lane
suggested) that uses the user's browser-authenticated username and
password to map to a PostgreSQL username that constitutes a "role"
(assuming that's possible). The benefit I can see to such a scheme is
that it means anyone who might manage to run a process under the web
server's account would still have to have the appropriate username and
password to do anything to the database. And the limited number of
"role" PostgreSQL usernames would mean fewer connections than using the
original usernames, as John Sidney-Woollett pointed out.

Keith Murphy wrote:

At some point, I may try rolling my own PAM module (as Tom Lane
suggested) that uses the user's browser-authenticated username and
password to map to a PostgreSQL username that constitutes a "role"
(assuming that's possible).

One option is to add an extra layer of indirection: the web server
interacts with a "transaction server" through eg: XML-RPC or CORBA.

The list of transactions ("interactions") you can perform is controlled,
each transaction can be logged, and each transaction handler can have
its own access rights to the postgresql database.

The transaction server can be hardened by only allowing access from the
web server. This does mean that anyone breaking into your web server can
potentially alter data by interacting with the transaction server - but
only to the extent allowed by the existing transactions. They can't make
wholesale changes to your database such as "select * from
credit_card_details; truncate invoices; drop table accounts_receivable;"
etc.

Regards
Alex Satrapa

On Tuesday 13 January 2004 22:13, Keith G. Murphy wrote:

John Sidney-Woollett wrote:

What you could consider is one or more pools which map to the "roles"
that your (web) app supports. For example, if a user needs "minimal
rights" access to db resources, then your cgi (request handler) accesses
the data using a connection from the "minimal rights" connection pool. A
user needing "greater rights" would have the cgi access the database from
the "greater rights" pool.

That sounds like an excellent compromise. How do you typically handle
the mechanics of authentication from web server to PostgreSQL on the
connect, using this scheme?

Umm.. I doubt role specific pooling is required. You can use set session
authorization to switch authorization of any connection.

Of course you need to connet to database as super user though. That is real
downside. If your webapp. breaks, the database can not provide any security.

Shridhar

Hi Keith and others,

Personally I am rather humble regarding my ability to write unassailable
programs from a security perspective, so I tend to use individual database
accounts for individual users. I know that under certain environments this
may not scale well and connection pooling may be required, but generally I
am not comfortable with that approach.

Basically my logic is that the most important security is where the
information is stored. For this reason I try to "push security back"
towards the database manager as far as possible. Then everythign else can
be given as few permissions as possible (for example, the web application
itself has no access to the database apart from the user). Usernames and
passwords can be stored separately in order to reduce the cost of compromise
(f. ex. HERMES stores the username in a cookie but the password as a session
variable), etc. These strategies are simply not possible under the
connection pooling scenario.

For me it comes down to the following question: What is the cost of
enforcing security yourself? If there is a security flaw in PostgreSQL,
there is NOTHING that will keep you safe, but why multiply single points of
security failure?

However you have another problem in the scenario you describe-- that is that
the actual authentication occurs via LDAP. This changes the assumptions and
security environment a bit. If I had complete control over such an
environment, I would do one of the following things:

1: Use Kerberos to authenticate and LDAP to store profiles. LDAP and
PostgreSQL authentication are now handled by Kerberos and you can pass the
authentication token via the web app in mod_perl (not sure how to do it in
PHP though). For many browsers, this would allow for single signon and
transparent logins.

2: Have multiple accounts for different roles and store these in the LDAP
user profiles. This creates a large number of headaches (are the role
fields properly secured, for example).

3: Finally you could have a user profiles table which contained the allowed
accounts to use.

Best Wishes,
Chris Travers

I do #1- most connections are not persistent though I have done those
before as well. Security-wise I'm been reviewing the pros and cons
of this and so far I really can't make much of an argument for #2.
Just the opposite in fact. The one thing I will be doing though,
for more security, is create a special webuser for that client.

My concern with connection pooling is that the application itself must run
with permission to do anything that any user may do in the application. If
you have a way of using postgresql native accounts, you can actually force
the application to have no rights to the database at all unless the
credentials are supplied by a user. The application then has NO rights to
anything that the user doesn't, and this may allow for better security.

The real drawback for multiple accounts is that each account needs to be
maintained. In environments where this is an issue, I usually create
functions to do this so that my information does not get out of sync.
However, I can imagine this being a concern, security wise.

Best Wishes,
Chris Travers

Quoting Chris Travers <chris@travelamericas.com>:

I do #1- most connections are not persistent though I have done those
before as well. Security-wise I'm been reviewing the pros and cons
of this and so far I really can't make much of an argument for #2.
Just the opposite in fact. The one thing I will be doing though,
for more security, is create a special webuser for that client.

My concern with connection pooling is that the application itself must run
with permission to do anything that any user may do in the application. If
you have a way of using postgresql native accounts, you can actually force
the application to have no rights to the database at all unless the
credentials are supplied by a user. The application then has NO rights to
anything that the user doesn't, and this may allow for better security.

I think we might be talking about two different things- unless you are equating
persistant connections to connection pooling. Perhaps that is correct but let
me example a little more what I was talking about. It might be a little
off-topic but I want to be clear in the archives.

My particular environment is Linux, Apache, mod_perl and PostgreSQL. The user
and group that the web server runs as has no permissions to anything. When a
client's app need a DB, we create an account in PG for them and grant
permissions to the web user for the objects it needs access to.

In the mod_perl script, if I do persistent, the first thing that is done is a
check for a previous connection to **that** client's database (via a global
variable). If not (or if that connection is "busy") a new one is created. The
maximum number of connections would be equal to the maximum number of Apache
listeners. That's assuming you don't use other application logic to control
it. For those who aren't familiar with mod_perl, the benefit is that not
only does your script run as an object in the Apache server but the database
connection objects will persist. When you don't do the persistent connections,
the difference is that your application object in Apache will have to open the
database connection everytime. For local connections to PostgreSQL, I really
haven't seen that much of a difference but in my "test" case, I'm
pretty sure I wasn't implementing the persistent-ness of the script correctly
and the application was not being hit with any significant load.

I thought connection pooling was more generic- any connection from the web
server/application business logic could be reused for any db connection. Please
correct me if I'm wrong here.

Depending on the application, the
authorization/authentication would be wrapped in an SSL transport (HTTPS instead
of HTTP). After that each connection is "tracked" (via the Apache
cookie-tracking and and pgAuth module I wrote) by always checking the validity
of the client's browser cookie. The authorization pairs for the client's
application are stored in a user table in their database and the cookie gets
linked to that and is valid only for that browser session (i.e. as long as the
browser instance is open and you did not "log out"). No direct information
about the database is revealed to the client interface (web browser) at all.

The real drawback for multiple accounts is that each account needs to be
maintained. In environments where this is an issue, I usually create
functions to do this so that my information does not get out of sync.
However, I can imagine this being a concern, security wise.

Its the pros and cons of PG having its own accounts. Definitely a security
benefit, which generally means a headache somewhere else- in this case double
account unless, like you said, have way to keep things in sync. For something
secure, I don't mind the headache... Advil is my friend :)

Best Wishes,
Chris Travers

--
Keith C. Perry, MS E.E.
Director of Networks & Applications
VCSN, Inc.
http://vcsn.com

____________________________________
This email account is being host by:
VCSN, Inc : http://vcsn.com