[Catalyst] how to authenticate using database users

[Adam Witney]

On 19 Mar 2009, at 01:34, bill hauck wrote:

>
> --- On Wed, 3/18/09, Adam Witney <awitney at sgul.ac.uk> wrote:
>
>> From: Adam Witney <awitney at sgul.ac.uk>
>> Subject: Re: [Catalyst] how to authenticate using database users
>> To: "The elegant MVC web framework" <catalyst at lists.scsys.co.uk>
>> Date: Wednesday, March 18, 2009, 4:33 PM
>> On 18 Mar 2009, at 16:24, Michael Reddick wrote:
>>
>>>
>>>
>>> On Wed, Mar 18, 2009 at 10:51 AM, Adam Witney
>> <awitney at sgul.ac.uk> wrote:
>>>
>>> Hi,
>>>
>>> Our database uses actual database users rather than a
>> table containing usernames and password.
>>>
>>>
>>> Why are you doing that?
>>
>>
>> The database has a row level security model implemented
>> such that row access is controlled by the database login.
>> This is because several applications can be used to interact
>> with it.
>>
>> thanks
>>
>> adam
>
> Adam, can you tell us what database you're using?  Also, what other  
> types of apps (web, fat client, etc.)?  I don't have any solution,  
> but I'm facing a somewhat similar issue ...
>
> I'm looking to replace a custom application written in FileMaker,  
> which allows you to create permission levels down to the field  
> level.  I'll need to replicate this in whatever replaces the  
> original application.
>
> Anyone have any hints on how to track what users (or roles) are able  
> to edit what fields?  I'm thinking of implementing it similar to the  
> way MySQL's permission tables are setup.  A permission table for  
> each data table with a column for each column in the data table and  
> a column for the user / role.  The values would be similar to Unix's  
> permissions (1,2,4) to indicate if they could read, write, modify.
>
> Anyone want to tell me it's crap and offer a better way?  Please ...

Hi Bill,

This is using PostgreSQL. The other apps are fat clients that can pull  
data from external sources, it was therefore necessary to have the  
security levels built into the database.

Our database uses PostgreSQL's array type to store the read/write  
permissions for each row... it works well, but it probably depends on  
your scale, our tables only have around 40k rows at this point.

cheers

adam