Best practice? Web application: single PostgreSQL user vs. multipleusers

[Keith G. Murphy]

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

[John Sidney-Woollett]

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Keith G. Murphy]

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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[John Sidney-Woollett]

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

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

[Keith G. Murphy]

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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[John Sidney-Woollett]

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[scott.marlowe]

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.
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Tom Lane]

"Keith G. Murphy" <ke******@minds pring.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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Keith G. Murphy]

Tom Lane wrote:

"Keith G. Murphy" <ke******@minds pring.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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Bruno Wolff III]

On Tue, Jan 13, 2004 at 11:15:30 -0600,
"Keith G. Murphy" <ke******@minds pring.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.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)