pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUser Permissions
8+ messages / 6 participants
[nested] [flat]
* User Permissions
@ 2002-01-17 22:27 Braum Meakes <braum@telus.net>
0 siblings, 3 replies; 8+ messages in thread
From: Braum Meakes @ 2002-01-17 22:27 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: User Permissions
@ 2002-01-17 23:00 SHELTON,MICHAEL (Non-HP-Boise,ex1) <michael_shelton@non.hp.com>
0 siblings, 1 reply; 8+ messages in thread
From: SHELTON,MICHAEL (Non-HP-Boise,ex1) @ 2002-01-17 23:00 UTC (permalink / raw)
To: 'Braum Meakes' <braum@telus.net>; pgsql-sql
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)
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: User Permissions
@ 2002-01-17 23:12 Peter Eisentraut <peter_e@gmx.net>
parent: Braum Meakes <braum@telus.net>
2 siblings, 0 replies; 8+ messages in thread
From: Peter Eisentraut @ 2002-01-17 23:12 UTC (permalink / raw)
To: Braum Meakes <braum@telus.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: User Permissions
@ 2002-01-17 23:20 Braum Meakes <braum@telus.net>
parent: Braum Meakes <braum@telus.net>
2 siblings, 1 reply; 8+ messages in thread
From: Braum Meakes @ 2002-01-17 23:20 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: User Permissions
@ 2002-01-17 23:42 chester c young <chestercyoung@yahoo.com>
parent: Braum Meakes <braum@telus.net>
0 siblings, 1 reply; 8+ messages in thread
From: chester c young @ 2002-01-17 23:42 UTC (permalink / raw)
To: pgsql-sql
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/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: User Permissions
@ 2002-01-18 00:21 Tom Lane <tgl@sss.pgh.pa.us>
parent: Braum Meakes <braum@telus.net>
2 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 2002-01-18 00:21 UTC (permalink / raw)
To: Braum Meakes <braum@telus.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: User Permissions
@ 2002-01-18 00:29 Peter Eisentraut <peter_e@gmx.net>
parent: chester c young <chestercyoung@yahoo.com>
0 siblings, 0 replies; 8+ messages in thread
From: Peter Eisentraut @ 2002-01-18 00:29 UTC (permalink / raw)
To: chester c young <chestercyoung@yahoo.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: User Permissions
@ 2002-01-18 04:15 Jan Wieck <janwieck@yahoo.com>
parent: SHELTON,MICHAEL (Non-HP-Boise,ex1) <michael_shelton@non.hp.com>
0 siblings, 0 replies; 8+ messages in thread
From: Jan Wieck @ 2002-01-18 04:15 UTC (permalink / raw)
To: SHELTON,MICHAEL (Non-HP-Boise,ex1) <michael_shelton@non.hp.com>; +Cc: 'Braum Meakes' <braum@telus.net>; pgsql-sql
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 select a
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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2002-01-18 04:15 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-01-17 22:27 User Permissions Braum Meakes <braum@telus.net>
2002-01-17 23:12 ` Peter Eisentraut <peter_e@gmx.net>
2002-01-17 23:20 ` Braum Meakes <braum@telus.net>
2002-01-17 23:42 ` chester c young <chestercyoung@yahoo.com>
2002-01-18 00:29 ` Peter Eisentraut <peter_e@gmx.net>
2002-01-18 00:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-01-17 23:00 Re: User Permissions SHELTON,MICHAEL (Non-HP-Boise,ex1) <michael_shelton@non.hp.com>
2002-01-18 04:15 ` Jan Wieck <janwieck@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox