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]

Re: Best practice? Web application: single PostgreSQL

Keith G. Murphy said:[color=blue]
> 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.[/color]

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]

Re: Best practice? Web application: single PostgreSQL

John Sidney-Woollett wrote:
[color=blue]
> Keith G. Murphy said:
>[color=green]
>>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.[/color]
>
>
> 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.
>[/color]
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 majordomo@postg resql.org)

[John Sidney-Woollett]

Re: Best practice? Web application: single PostgreSQL

Keith G. Murphy said:[color=blue]
> 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?[/color]

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]

Re: Best practice? Web application: single PostgreSQL

John Sidney-Woollett wrote:
[color=blue]
> Keith G. Murphy said:
>[color=green]
>>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?[/color]
>
>
> 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
>[/color]
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 majordomo@postg resql.org)

[John Sidney-Woollett]

Re: Best practice? Web application: single PostgreSQL

Keith G. Murphy said:[color=blue]
> 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.[/color]

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.
[color=blue]
> Unfortunately, that still would allow the web server account
> to "fake" role names.[/color]

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]

Re: Best practice? Web application: single PostgreSQL

On Tue, 13 Jan 2004, Keith G. Murphy wrote:
[color=blue]
> 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.[/color]

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]

Re: Best practice? Web application: single PostgreSQL

"Keith G. Murphy" <keithmur@minds pring.com> writes:[color=blue]
> Hmmm, mightn't it be kind of nice if there were PAM or krb5 maps in
> addition to ident maps?[/color]

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 majordomo@postg resql.org)

[Keith G. Murphy]

Re: Best practice? Web application: single PostgreSQL

Tom Lane wrote:
[color=blue]
> "Keith G. Murphy" <keithmur@minds pring.com> writes:
>[color=green]
>>Hmmm, mightn't it be kind of nice if there were PAM or krb5 maps in
>>addition to ident maps?[/color]
>
>
> 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.
>[/color]

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 majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

[Bruno Wolff III]

Re: Best practice? Web application: single PostgreSQL

On Tue, Jan 13, 2004 at 11:15:30 -0600,
"Keith G. Murphy" <keithmur@minds pring.com> wrote:[color=blue]
> 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.[/color]

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.
[color=blue]
> 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...[/color]

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 majordomo@postg resql.org)

[scott.marlowe]

Re: Best practice? Web application: single PostgreSQL

On Tue, 13 Jan 2004, Keith G. Murphy wrote:
[color=blue]
> John Sidney-Woollett wrote:
>[color=green]
> > Keith G. Murphy said:
> >[color=darkred]
> >>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.[/color]
> >
> >
> > 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.
> >[/color]
> 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?[/color]

I create individual databases for unrelated projects (like say, phonebook
and sales_projectio ns 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...


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

[scott.marlowe]

Re: Best practice? Web application: single PostgreSQL

On Tue, 13 Jan 2004, Keith G. Murphy wrote:
[color=blue]
> John Sidney-Woollett wrote:
>[color=green]
> > Keith G. Murphy said:
> >[color=darkred]
> >>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.[/color]
> >
> >
> > 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.
> >[/color]
> 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?[/color]

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.


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org

[netadmin@vcsn.com]

Re: Best practice? Web application: single PostgreSQL

On Tue, 13 Jan 2004, Keith G. Murphy wrote:
[color=blue]
> 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.[/color]

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.c om

http://vcsn.com

---

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Keith Murphy]

Re: Best practice? Web application: single PostgreSQL

scott.marlowe wrote:
[color=blue]
> On Tue, 13 Jan 2004, Keith G. Murphy wrote:
>
>[color=green]
>>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.[/color]
>
>
> 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.
>[/color]
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.



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

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

[Alex Satrapa]

Re: Best practice? Web application: single PostgreSQL

Keith Murphy wrote:[color=blue]
> 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).[/color]

One option is to add an extra layer of indirection: the web server
interacts with a "transactio n 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_det ails; truncate invoices; drop table accounts_receiv able;"
etc.

Regards
Alex Satrapa


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly