Thread: User Permissions
Hello, I have a function that I've written in plpgsql. In it I lock a table before selecting a value and, depending on the value, either update the record selected or insert a new one. It works perfectly when I run it as a super-user or as the owner of the table. However, as a standard user I am unable to lock the table. How do I either run the function at the owners level, or change the permissions on the table to allow the user to lock it? I am unable to lock the table as a standard user at any time, not just when running the function. Thanks in advance, Braum Meakes
I don't have an answer for your issue, just a question on your process: Why do you lock the table instead of using a transaction? The transaction would prevent the data from changing until you either applied your changes (insert or update) and commited the transaction or rolled back the transaction (due to an error or something). Mike -----Original Message----- From: Braum Meakes [mailto:braum@telus.net] Sent: Thursday, January 17, 2002 3:28 PM To: pgsql-sql@postgresql.org Subject: [SQL] User Permissions Hello, I have a function that I've written in plpgsql. In it I lock a table before selecting a value and, depending on the value, either update the record selected or insert a new one. It works perfectly when I run it as a super-user or as the owner of the table. However, as a standard user I am unable to lock the table. How do I either run the function at the owners level, or change the permissions on the table to allow the user to lock it? I am unable to lock the table as a standard user at any time, not just when running the function. Thanks in advance, Braum Meakes ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
That's got it. Thanks! At 06:12 PM 17/01/02 -0500, you wrote: >Braum Meakes writes: > > > I have a function that I've written in plpgsql. In it I lock a table > > before selecting a value and, depending on the value, either update the > > record selected or insert a new one. It works perfectly when I run it as a > > super-user or as the owner of the table. However, as a standard user I am > > unable to lock the table. How do I either run the function at the owners > > level, or change the permissions on the table to allow the user to lock it? > >Depending on what lock method you chose, you probably need to have UPDATE >permission on the table. So, as the owner of the table, execute > >GRANT UPDATE ON tablename TO yourusername; > >For even higher lock-levels you need to be the table owner, but you >probably don't want to use those anyway. > >-- >Peter Eisentraut peter_e@gmx.net
Braum Meakes writes: > I have a function that I've written in plpgsql. In it I lock a table > before selecting a value and, depending on the value, either update the > record selected or insert a new one. It works perfectly when I run it as a > super-user or as the owner of the table. However, as a standard user I am > unable to lock the table. How do I either run the function at the owners > level, or change the permissions on the table to allow the user to lock it? Depending on what lock method you chose, you probably need to have UPDATE permission on the table. So, as the owner of the table, execute GRANT UPDATE ON tablename TO yourusername; For even higher lock-levels you need to be the table owner, but you probably don't want to use those anyway. -- Peter Eisentraut peter_e@gmx.net
I thought SQL standard was that a function got its permissions from its owner, not its executer. cc young __________________________________________________ Do You Yahoo!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/
Braum Meakes <braum@telus.net> writes:
> I have a function that I've written in plpgsql. In it I lock a table
> before selecting a value and, depending on the value, either update the
> record selected or insert a new one. It works perfectly when I run it as a
> super-user or as the owner of the table. However, as a standard user I am
> unable to lock the table.
[ eyeballs code ] LOCK ... IN ACCESS SHARE MODE (ie, read lock)
requires SELECT privileges; all stronger forms of LOCK require UPDATE
and/or DELETE privileges. This seems to be undocumented :-(. Am
fixing the LOCK reference page now.
Per subsequent discussion, it's not real clear that you need LOCK at
all ... but the docs need to explain the privilege rules, since they're
not exactly obvious.
regards, tom lane
chester c young writes: > I thought SQL standard was that a function got its permissions from its > owner, not its executer. Functions can be defined both ways (at least in SQL). The default is implementation-defined. -- Peter Eisentraut peter_e@gmx.net
SHELTON,MICHAEL (Non-HP-Boise,ex1) wrote:
> I don't have an answer for your issue, just a question on your process:
>
> Why do you lock the table instead of using a transaction? The transaction
> would prevent the data from changing until you either applied your changes
> (insert or update) and commited the transaction or rolled back the
> transaction (due to an error or something).
He is most probably doing so to achieve the every so often desired IF_EXISTS_UPDATE_ELSE_INSERT functionality.
A function in PostgreSQL is allways guaranteed to be covered by a transaction. But that doesn't matter. If you
selecta key for update and based on if you found it decide to update or insert, you have a possible race
condition.
Doing
SELECT x FROM y WHERE z = 666 FOR UPDATE
will succeed in multiple transactions simultaneously as long as there is no row with z = 666. So the entire code
section of checking if the key exists until update or insert has to be covered by a mutex, and there is no other
mutex mechanism available than a table lock.
Even if it's not standard, I would be for a proprietary extension that actually implements some sort of
mutex for stored procedures.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com