DB Authentication Design

On 12 January 2014 15:30, François Beausoleil <francois@teksol.info> wrote:

Hi all,

I'm thinking that all apps that connect to the database should have their
own user. For example, the web application process is one user, then a
report builder process should have another user, and a different process
that imports data should have his own too, and so on. Would you generally
agree with that?

I'm thinking that by having different users, PGbouncer can create
different pools, and better allow me to control concurrency.

Thanks!
François

Hi
I think the main idea should be that each user has different credentials,
so for example a user for building reports shouldn't be allowed to change
the database. Otherwise you'd end with a bunch of users, and from the
security perpective, there wouldn't be much difference between this and
having just one user.

regards,
Szymon

On 12 Jan 2014 22:31, "François Beausoleil" <francois@teksol.info> wrote:

Hi all,

I'm thinking that all apps that connect to the database should have their

own user. For example, the web application process is one user, then a
report builder process should have another user, and a different process
that imports data should have his own too, and so on. Would you generally
agree with that?

Should be a good security design given you restrict access and ability for
each usrer. E.g. reporting user will not need update privileges, you can
have an additional application admin user who will be used for applying db
patches (only that user should have alter and create privileges).

I'm thinking that by having different users, PGbouncer can create

different pools, and better allow me to control concurrency.

You can restrict this and also restrict other resources e.g. work_mem(
probably reportjng user will need higher than others).

Regards
Sameer

PS: Sent from my Mobile device. Pls ignore typo n abb

On Sun, Jan 12, 2014 at 6:30 AM, François Beausoleil
<francois@teksol.info>wrote:

Hi all,

I'm thinking that all apps that connect to the database should have their
own user. For example, the web application process is one user, then a
report builder process should have another user, and a different process
that imports data should have his own too, and so on. Would you generally
agree with that?

I'm thinking that by having different users, PGbouncer can create
different pools, and better allow me to control concurrency.

It really depends on what you are doing, what your security model is, what
your concurrency constraints are, etc. What you are describing is a fairly
typical approach and it sacrifices some real security possibilities for
connection pooling possibilities. The fundamental question is whether the
security of your application's user should be tied to the database
connection.

The other option is to have a different user for each user, and assign
permissions to the user instead of to the application. You can use roles
to manage groups for each application.

For the latter (per-user permissions), your advantages are:

1. You can push security responsibility back to the database which is
probably better tested than your code will ever be.

2. You can re-use access across applications, allowing for common security
enforcement.

3. You don't have to trust your applications from your database.

4. It significantly mitigates the impact of sql injection attacks because
top-level attacks don't bypass permission barriers (this is not necessarily
true with any functions running as security definer though).

The above are why we use the per-user approach in LedgerSMB.

For per-application approaches, the pros are different:

1. Since security is not tied to the database connection, you can pool a
connection over several users (which is what you are talking about).

2. You can still have some mitigation of sql injection even if you can't
have the same degree, by assigning rights per application instead of per
user.

3. In a web application environment, you have a lot more freedom over
authentication in every hop. If you go with per-user permissions, for web
apps you are talking about, effectively, HTTP basic with MD5 or other
password auth between the web and db servers, or Kerberos auth on every
link, or some more complex scenarios where auth is handled by a different
connection and/or role. But if you go with per-application permissions,
you can use HTTP digest or other through between the browser and the
server, and anything you want between the web and db servers. This is
because you have less of a need to re-use credentials in that setup.

For the kind of work *I* do, the per-user approach works better, but
without knowing a lot about your project I can't offer real recommendations.
--
Best Wishes,
Chris Travers

Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor
lock-in.
http://www.efficito.com/learn_more.shtml

Hello Chris,

Le 2014-01-12 à 23:24, Chris Travers a écrit :

On Sun, Jan 12, 2014 at 6:30 AM, François Beausoleil <francois@teksol.info> wrote:
Hi all,

I'm thinking that all apps that connect to the database should have their own user. For example, the web application process is one user, then a report builder process should have another user, and a different process that imports data should have his own too, and so on. Would you generally agree with that?

I'm thinking that by having different users, PGbouncer can create different pools, and better allow me to control concurrency.

It really depends on what you are doing, what your security model is, what your concurrency constraints are, etc. What you are describing is a fairly typical approach and it sacrifices some real security possibilities for connection pooling possibilities. The fundamental question is whether the security of your application's user should be tied to the database connection.

This database cluster is not exposed to the outside world. What I really need is a way to control the number of simultaneous execution of queries. Your "per application" approach is a better name for what I described.

I also have web-facing applications, in which case the per-user approach sounds good.

Thanks!
François