Securing a db app - RFC

[BARTKO Zoltan]

Hello folks,

First a question and then the rest:

Does the PostgreSQL log contain the stored function calls with all parameters? Or is this something that could be set?

I would appreciate anyone wiser than me to comment on the following:

I am making an app for PostgreSQL (the server). The clients are connecting through the same single user. There is a model of the user organization inside (position hierarchy), each person (virtual user) assigned a position, positions have privileges assigned.

If I want to access a function (like do this or that with data), I use a stored function and pass the id# of the user plus all the necessary things. First, I check if the person is authorized to carry out the operation. if so, the operation is performed.

There are users, who are administrators. Thus, they are allowed to do anything.

My problem is the following: I can do a check for whether the person requesting the operation is logged in (that means no dirty hacks with my name if I am not logged in). But then anybody can find out the id# of an administrator and use that to identify himself when asked for.

I thought about using electronic signatures or something similar. That would mean sending the key of the users around when calling the stored functions.

does the DB log contain the function calls with the parameters of the function too? If not, then by using an SSL connection I could prevent crackers from accessing the data.

Please feel free to comment, object, etc.

Thanks

Zoltan

[Shridhar Daithankar]

On Wednesday 02 June 2004 02:04, BARTKO Zoltan wrote:

I would appreciate anyone wiser than me to comment on the following:

I am making an app for PostgreSQL (the server). The clients are connecting
through the same single user. There is a model of the user organization
inside (position hierarchy), each person (virtual user) assigned a
position, positions have privileges assigned.

If I want to access a function (like do this or that with data), I use a
stored function and pass the id# of the user plus all the necessary things.
First, I check if the person is authorized to carry out the operation. if
so, the operation is performed.

There are users, who are administrators. Thus, they are allowed to do
anything.

My problem is the following: I can do a check for whether the person
requesting the operation is logged in (that means no dirty hacks with my
name if I am not logged in). But then anybody can find out the id# of an
administrator and use that to identify himself when asked for.
You can probably use set session authorization. Here are some brief steps.

1. Convert all your users as postgresql database users
2. Always login from app. to postgresql as superuser
3. While doing anything on behalf of a user, use set session authorization to
switch identity.
4. Grant appropriate permissions on postgresql object to each user/group
5. Let postgresql handle the dirty work of access checking.

Of course you have to make sure that the user does not issue a set session
authorization in any ways. It might be made to happen if you don't escape
user supplied data appropriately..
does the DB log contain the function calls with the parameters of the
function too? If not, then by using an SSL connection I could prevent
crackers from accessing the data.

Turn on statement logging and see if that suffices your purpose.

HTH

Shridhar

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

[btober]

On Wednesday 02 June 2004 02:04, BARTKO Zoltan wrote:

I would appreciate anyone wiser than me to comment on the following:

I am making an app for PostgreSQL (the server). The clients are
connecting through the same single user. ...

If I want to access a function (like do this or that with data), I
use a stored function and pass the id# of the user plus all the
necessary things. First, I check if the person is authorized to carry
out the operation. if so, the operation is performed.

There are users, who are administrators. Thus, they are allowed to do
anything.

You can probably use set session authorization. Here are some brief
steps.

1. Convert all your users as postgresql database users

If he's going to do this, why bother with hard-coding a single user id
and password in the application -- why not have the user log in as their
defined Postgresql user, and let the data base handle all the security
and permission issues?
--Berend Tober


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

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

[Shridhar Daithankar]

On Wednesday 02 June 2004 17:58, bt****@computer.org wrote:

You can probably use set session authorization. Here are some brief
steps.

1. Convert all your users as postgresql database users

If he's going to do this, why bother with hard-coding a single user id
and password in the application -- why not have the user log in as their
defined Postgresql user, and let the data base handle all the security
and permission issues?

In that case he can not use connection pooling. Thats all. Otherwise there is
no need for single user id.

Shridhar

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

[BARTKO Zoltan]

Berend, Shridhar, et al.,

see lower

----- Original Message -----
From: <bt****@computer.org>
To: <sh******@frodo.hserus.net>
Cc: <ba***********@pobox.sk>; <pg***********@postgresql.org>
Sent: Wednesday, June 02, 2004 2:28 PM
Subject: Re: [GENERAL] Securing a db app - RFC

On Wednesday 02 June 2004 02:04, BARTKO Zoltan wrote:

I would appreciate anyone wiser than me to comment on the following:

I am making an app for PostgreSQL (the server). The clients are
connecting through the same single user. ...

If I want to access a function (like do this or that with data), I
use a stored function and pass the id# of the user plus all the
necessary things. First, I check if the person is authorized to carry
out the operation. if so, the operation is performed.

There are users, who are administrators. Thus, they are allowed to do
anything.

You can probably use set session authorization. Here are some brief
steps.

1. Convert all your users as postgresql database users

If he's going to do this, why bother with hard-coding a single user id
and password in the application -- why not have the user log in as their
defined Postgresql user, and let the data base handle all the security
and permission issues?

Now my problem is that I have audit trails in the DB. I need to make it so
that the admin would just revive a deleted user any time the he wishes to do
so. Tell me if my comprehension is limited.

Thanks

Zoltan

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend