Thread: Another Security Question: User-based Roles vs. Application Business Rules

Forgive me if this is a basic and trivial (i.e., stupid) question. I haven't
been using postgres very long, and I'm not an experienced database system
developer.

I noticed that there is a very powerful group-based security feature in
postgres. Very nice - I like it alot. So one way to implement security
constraints is to define appropriate groups, assign memobership of users
to those groups, and then assign group-based permissions to the assorted
database objects (e.g., tables). Fantastic!

However, ... this requires each entity accessing the databse to be
defined as a user. In the context of a web application, this paradigm
doesn't necessarily make sense since there may be many unknown users.
Somehow those users must be mapped to a "role." I suppose you can map
all unknown users into the user "guest" and then define guest privileges
appropriately.

Is this a good approach? Is there better way to do this?  Is there an
altnerate way to consider?
--
%  Randy Yates                  % "My Shangri-la has gone away, fading like
%% Fuquay-Varina, NC            %  the Beatles on 'Hey Jude'"
%%% 919-577-9882                %
%%%% <yates@ieee.org>           % 'Shangri-La', *A New World Record*, ELO
http://home.earthlink.net/~yatescr

I should have mentioned that it seems like the obvious alternative
is to perform security checking at the application layer, preventing
unauthorized access before even attempting to execute a query against
the database.

Comments?

--Randy


Randy Yates <yates@ieee.org> writes:

> Forgive me if this is a basic and trivial (i.e., stupid) question. I haven't
> been using postgres very long, and I'm not an experienced database system
> developer.
>
> I noticed that there is a very powerful group-based security feature in
> postgres. Very nice - I like it alot. So one way to implement security
> constraints is to define appropriate groups, assign memobership of users
> to those groups, and then assign group-based permissions to the assorted
> database objects (e.g., tables). Fantastic!
>
> However, ... this requires each entity accessing the databse to be
> defined as a user. In the context of a web application, this paradigm
> doesn't necessarily make sense since there may be many unknown users.
> Somehow those users must be mapped to a "role." I suppose you can map
> all unknown users into the user "guest" and then define guest privileges
> appropriately.
>
> Is this a good approach? Is there better way to do this?  Is there an
> altnerate way to consider?
> --
> %  Randy Yates                  % "My Shangri-la has gone away, fading like
> %% Fuquay-Varina, NC            %  the Beatles on 'Hey Jude'"
> %%% 919-577-9882                %
> %%%% <yates@ieee.org>           % 'Shangri-La', *A New World Record*, ELO
> http://home.earthlink.net/~yatescr

--
%  Randy Yates                  % "Watching all the days go by...
%% Fuquay-Varina, NC            %  Who are you and who am I?"
%%% 919-577-9882                % 'Mission (A World Record)',
%%%% <yates@ieee.org>           % *A New World Record*, ELO
http://home.earthlink.net/~yatescr

Presumably in the context of a web application, you've got control over
the contexts in which users exist and log in. People accessing publicly
accessible page, for instance, might connect as one user; people
accessing content via a login might connect as another.

Basically, for each role your web application creates in terms of types
of users, you can create a postgres user.

Often, it's as simple as creating a single postgres user that acts as a
proxy for the entire web application because, if you're the web
application designer as well, or can have authority over the
application in some way, you can know what sorts of permissions will be
required in the database.

-tfo

On Sep 7, 2004, at 11:39 PM, Randy Yates wrote:

> Forgive me if this is a basic and trivial (i.e., stupid) question. I
> haven't
> been using postgres very long, and I'm not an experienced database
> system
> developer.
>
> I noticed that there is a very powerful group-based security feature in
> postgres. Very nice - I like it alot. So one way to implement security
> constraints is to define appropriate groups, assign memobership of
> users
> to those groups, and then assign group-based permissions to the
> assorted
> database objects (e.g., tables). Fantastic!
>
> However, ... this requires each entity accessing the databse to be
> defined as a user. In the context of a web application, this paradigm
> doesn't necessarily make sense since there may be many unknown users.
> Somehow those users must be mapped to a "role." I suppose you can map
> all unknown users into the user "guest" and then define guest
> privileges
> appropriately.
>
> Is this a good approach? Is there better way to do this?  Is there an
> altnerate way to consider?
> --
> %  Randy Yates                  % "My Shangri-la has gone away, fading
> like
> %% Fuquay-Varina, NC            %  the Beatles on 'Hey Jude'"
> %%% 919-577-9882                %
> %%%% <yates@ieee.org>           % 'Shangri-La', *A New World Record*,
> ELO
> http://home.earthlink.net/~yatescr