Thread: Securing a db app - RFC
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
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
> 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
On Wednesday 02 June 2004 17:58, btober@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
Berend, Shridhar, et al., see lower ----- Original Message ----- From: <btober@computer.org> To: <shridhar@frodo.hserus.net> Cc: <bartko.zoltan@pobox.sk>; <pgsql-general@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