Re: "Locking rows"

On 8/12/19 1:07 PM, stan wrote:
> On Mon, Aug 12, 2019 at 12:14:25PM -0700, Adrian Klaver wrote:
>> On 8/12/19 10:51 AM, stan wrote:
>>> I have a customer requirement/desire. The system is (among other things)
>>> essentially a employee time sheet. The manager wants for an employee to not
>>> be able to modify a given row in the table they enter time into once it is
>>> committed. I personally see issues with this, but I am willing to try to give
>>> him what he wants. If it creates issues we can sort them out, once he sees
>>> the issues.
>>>
>>> The only way I see to do this is to add a column (call it lock). I will
>>> then set this up as a default entry with a value of TRUE. The form the
>>> employee uses to enter this will, of course, not display this column. The
>>> I will create a function that on UPDATE, checks for the presence of the 1 in
>>> this row, and rejects the update. Then I will give the manager a form where
>>> he can set this flag to FALSE.
>>>
>>> Looks ugly, and convulsed to me.
>>>
>>> Is here a more "database native" way to handle this?
>>
>> Depends on who is doing the database record changes.
>>
>> In other words are there defined roles:
>>
>> https://www.postgresql.org/docs/11/sql-createrole.html
>>
>> for the object(table) and the entity working with the table?
>>
> OK, maybe I could set up the "user" role, such that it does not have the
> UPDATE permission on this table, just the INSERT one? Of course the "admin"
> role would have the UPDATE attribute for this table.

'user' should also have SELECT if you want them to see their records.

>
> Make sense?

Yes.

>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com