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