473,836 Members | 1,560 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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

Nov 22 '05
20 6649
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_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

Nov 22 '05 #11
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.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

Nov 22 '05 #12
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.
ne******@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

Nov 22 '05 #13
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.

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

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

Nov 22 '05 #14
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 "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 ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

Nov 22 '05 #15
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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 22 '05 #16
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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

Nov 22 '05 #17
> 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
---------------------------(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

Nov 22 '05 #18
Quoting Chris Travers <ch***@travelam ericas.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

---------------------------(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

Nov 22 '05 #19
Mensaje citado por "scott.marl owe" <sc***********@ ihs.com>:
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
[snip]
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.


In the case of PHP there are very nice ACL stuff in PEAR, like PEAR::Auth.

I personally do all the auth stuff by myself (table with users, table with
function, table with permissions, etc.), just like Scott. :-)

--
select 'mmarques' || '@' || 'unl.edu.ar' AS email;
---------------------------------------------------------
Martín Marqués | Programador, DBA
Centro de Telemática | Administrador
Universidad Nacional
del Litoral
---------------------------------------------------------

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

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

Nov 22 '05 #20

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
9292
by: DrUg13 | last post by:
In java, this seems so easy. You need a new object Object test = new Object() gives me exactly what I want. could someone please help me understand the different ways to do the same thing in C++. I find my self sometimes, trying Object app = Object(); Object *app = Object(); Object app = new Object();
136
9479
by: Matt Kruse | last post by:
http://www.JavascriptToolbox.com/bestpractices/ I started writing this up as a guide for some people who were looking for general tips on how to do things the 'right way' with Javascript. Their code was littered with document.all and eval, for example, and I wanted to create a practical list of best practices that they could easily put to use. The above URL is version 1.0 (draft) that resulted. IMO, it is not a replacement for the FAQ,...
6
1565
by: RS | last post by:
Hi, What's the best practice to save user preferences for a .NET application . For example if the user does not want anymore to see Tip-Of-The-Day (TOTD), the user can tick a checkbox on the window showing the tip (TOTD). That tick will be recorded so that next time the user logs in, the application will suppress the display of TOTD. Now if I expand this simple example, say the user is so expert at what he does with the application he...
3
1821
by: Phil Campaigne | last post by:
I am developing a java/postgresql application using ant and junit. I want to deploy tested builds along with matching tables with test data in them. What is the best way to deploy the tables and data to postgresql to match a war file? thanks, Phil
4
2633
by: Collin Peters | last post by:
I have searched the Internet... but haven't found much relating to this. I am wondering on what the best practices are for migrating a developmemnt database to a release database. Here is the simplest example of my situation (real world would be more complex). Say you have two versions of your application. A release version and a development version. After a month of developing you are ready to release a new version. There have...
3
2329
by: Michael Glaesemann | last post by:
Hello all, Recently I've been thinking about different methods of managing users that log into a PostgreSQL-backed application. The users I'm thinking of are not necessarily DBAs: they're application users that really shouldn't even be aware that they are being served by the world's most advanced open source database server. I appreciate any thoughts or feedback people may have, as I'm trying to decide which is the most appropriate way...
5
2538
by: BK | last post by:
We've got a fairly large scale development process under way in .NET 2003. We are about a month away from go-live for phase 1, second phase is rather short and all work should be completed in the next 2 months. Looking back on problems encountered, we want to learn from this project. FWIW, we are nearly on time with the original time line (only off by about a month), and we actually added more functionality than the original specs...
3
3171
by: Venkat | last post by:
Hi, We have a windows application developed in c# and SQL Server 2005. Our application need to execute more than one command (ExecuteReader and ExecuteScalar) at a single time. Till now we have we use with only one connection created during start up of application and will be displose/closed when user logs out of application. With the single database connection we have problems when trying to execute more than command at a particular...
2
2171
by: sabbadin12 | last post by:
Hi, I'm going to work on an application that uses a postgreSQL database so that it can uses SQLServer 2005. I think I solved most problems on the programming side, but I still have some doubts on the DB side regarding how to handle the creation of the db schema on sqlserver and how to handle the every day dba work. 1) should I try to use an ER tool like Embarcadero and have its logical model be the master copy ? (i did some tests, it...
0
9816
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9668
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
10588
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10254
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9371
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
6978
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5647
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4448
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3112
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.