Re: 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

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(at)gmx(dot)net

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(at)gmx(dot)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(at)telus(dot)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(at)gmx(dot)net