Re: Best way to manage users - Mailing list pgsql-novice

Charly,

I have to disagree.  What you are describing is a workaround that allows you
to use the security features of the db to control access to a view that you
created - not a built-in feature.  It's still a workable solution to the
problem.  Thanks for providing another way to attack this issue.

Kevin




-----Original Message-----
From: Charley Tiggs [mailto:ctiggs@xpressdocs.com]
Sent: Thursday, January 05, 2006 8:51 AM
To: Kevin Crenshaw
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Best way to manage users

#4 is not quite correct.

If you need to control column or row level access, simply create a
view that enforces those limits.  With proper structure, that view
can even use a permissions based paradigm where you pass in a value
to the view and it returns only those rows that are available to the
specific web user, giving only the columns you wish the user to be
able to view.

Charley

On Jan 5, 2006, at 7:32 AM, Kevin Crenshaw wrote:

> I considered that point as well, however, I still believe it is
> better not
> to have my user accounts handled by the db - because:
>
> 1. Fewer database user accounts means fewer vectors for entry into
> the db.
> 2. Controlling access to the data via your app gives you more
> control over
> what the end user can see and what they can do with the data.
> 3. You can still use the Postgres' built in access controls to
> limit what
> your user can do in the db as an added layer of security.
>  - When I say 'your user' I mean the user you set up to give your
> web app
> access to the database and not the individual web app users
> contained within
> the database.
> 4. Correct me if I'm wrong, but I don't think that Postgres allows
> access
> control at the column or row level, just at the table level.
> Controlling
> access via your app will give you access control down to whatever
> level you
> need.
> 5. As another poster mentioned, controlling access via your app
> allows you
> the ability to move to other rdbms' more easily if you choose to do
> so at a
> later date.
>
> Anyway, that's my $.02.
>
> Kevin
>
>
>
>
> -----Original Message-----
> From: pgsql-novice-owner@postgresql.org
> [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Roland Giesler
> Sent: Thursday, January 05, 2006 5:50 AM
> To: pgsql-novice@postgresql.org
> Subject: Re: [NOVICE] Best way to manage users
>
> Kevin Crenshaw wrote:
>> I think that the best solution - given the discussion thus
>> far -  is to have a separate pg user that the web app will
>> use to access the database, and create a 'users' table in the
>> db to store the web app usernames and passwords etc...
>
> Well, using postgres users to authenticate web users has the
> advantage that
> one can set up access priviledges in the database and in doing so
> limit the
> bypassing of access security from other apps or the likes of PgAdmin.
> Defining group roles and simply adding or removing users to a
> group, makes
> the process relatively simple.  Without this, one would have to
> define all
> these things (table and column level access) manually and test for
> it in
> your app, which makes things quite a bit more complex.  Thinking
> this all
> through, it seems that using PG users is till a good option if you
> need to
> have different user profiles in an app.
>
> Comments?
>
> Roland
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org