From: Tom Lane <tgl@sss.pgh.pa.us>
To: Dag Gullberg <dag.gullberg@telia.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Concurrency problem
Date: Sun, 03 Oct 2004 13:15:50 -0400
Message-ID: <3318.1096823750@sss.pgh.pa.us> (raw)
In-Reply-To: <1096801131.5238.32.camel@hemma>
References: <1096801131.5238.32.camel@hemma>
Dag Gullberg <dag.gullberg@telia.com> writes:
> Warning: pg_query(): Query failed: ERROR: tuple concurrently updated
> CONTEXT: PL/pgSQL function "get_rights" line 5 at SQL statement in
> /home/site/PHP/db_func.php on line 301
> Code of get_rights:
> GRANT SELECT ON c.users TO
> adm,w3;
> SELECT rights INTO result
> FROM c.users
> WHERE usr_id=uid;
> REVOKE ALL ON c.users FROM w3,adm;
> RETURN result;
Do you have a bunch of clients doing this in parallel? If so, the
problem is probably coming from two instances of the function trying to
update the catalog entry for c.users at the same time. The approach
is fundamentally broken anyhow, because there is nothing stopping
client 2 from revoking the rights in between client 1 doing his GRANT
and his SELECT.
The right way to do what you seem to want (allow only this function to
access the table) is not to flap the rights settings back and forth
like that. Grant SELECT rights to a specific userid and make the
function be owned by that userid and be labeled SECURITY DEFINER.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, dag.gullberg@telia.com
Subject: Re: Concurrency problem
In-Reply-To: <3318.1096823750@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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