pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
User Permissions
8+ messages / 6 participants
[nested] [flat]

* User Permissions
@ 2002-01-17 22:27 Braum Meakes <braum@telus.net>
  2002-01-17 23:12 ` Re: User Permissions Peter Eisentraut <peter_e@gmx.net>
  2002-01-17 23:20 ` Re: User Permissions Braum Meakes <braum@telus.net>
  2002-01-18 00:21 ` Re: User Permissions Tom Lane <tgl@sss.pgh.pa.us>
  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 22:27 User Permissions Braum Meakes <braum@telus.net>
@ 2002-01-17 23:12 ` Peter Eisentraut <peter_e@gmx.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 22:27 User Permissions Braum Meakes <braum@telus.net>
@ 2002-01-17 23:20 ` Braum Meakes <braum@telus.net>
  2002-01-17 23:42   ` Re: User Permissions chester c young <chestercyoung@yahoo.com>
  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 22:27 User Permissions Braum Meakes <braum@telus.net>
  2002-01-17 23:20 ` Re: User Permissions Braum Meakes <braum@telus.net>
@ 2002-01-17 23:42   ` chester c young <chestercyoung@yahoo.com>
  2002-01-18 00:29     ` Re: User Permissions Peter Eisentraut <peter_e@gmx.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-17 22:27 User Permissions Braum Meakes <braum@telus.net>
  2002-01-17 23:20 ` Re: User Permissions Braum Meakes <braum@telus.net>
  2002-01-17 23:42   ` Re: User Permissions chester c young <chestercyoung@yahoo.com>
@ 2002-01-18 00:29     ` Peter Eisentraut <peter_e@gmx.net>
  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-17 22:27 User Permissions Braum Meakes <braum@telus.net>
@ 2002-01-18 00:21 ` Tom Lane <tgl@sss.pgh.pa.us>
  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-17 23:00 SHELTON,MICHAEL (Non-HP-Boise,ex1) <michael_shelton@non.hp.com>
  2002-01-18 04:15 ` Re: User Permissions Jan Wieck <janwieck@yahoo.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: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>
  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