Thread: BUG #5609: Exclusive Locks & Permission

The following bug has been logged online:

Bug reference:      5609
Logged by:          Rob Brucks
Email address:      rob.brucks@rackspace.com
PostgreSQL version: 8.3.7
Operating system:   Ubuntu 8.04
Description:        Exclusive Locks & Permission
Details:

If a user that does not have permission to alter a table issues an alter he
acquires an exclusive lock BEFORE permission is checked and an error is
issued.

This causes concurrency problems since the unprivileged user causes other
threads to wait while it attempts to acquire an exclusive lock, only to have
it ultimately fail.

The user's permissions need to be checked before requesting an exclusive
lock on the table for the alter.

To re-create:
psql -U postgres -d postgres
postgres=# CREATE ROLE nobody NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT
LOGIN;
postgres=# CREATE TABLE tab1 (a int);
postgres=# REVOKE ALL ON tab1

"Rob Brucks" <rob.brucks@rackspace.com> writes:
> The user's permissions need to be checked before requesting an exclusive
> lock on the table for the alter.

Unfortunately, that cure is as bad or worse than the disease.  It's not
possible to do a permissions check before acquiring any lock --- what if
somebody is changing the permissions under you, or even dropping or
renaming the table?  We could acquire sharelock, do the permissions
check, and then upgrade to exclusive lock; but lock upgrading has its
own unpleasant consequences, notably increased risk of deadlock.

So it's unlikely this is going to get changed.

            regards, tom lane

Sorry about that, I hit the submit button on accident before I finished typ=
ing out the issue.  But I guess there was enough there for you to understan=
d the problem.

Thanks for the quick response.

Is there any alternative I can use to prevent the exclusive lock (other tha=
n changing the app, we don't have access to the source code)?

Thanks,
Rob

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]=20
Sent: Monday, August 09, 2010 2:41 PM
To: Rob Brucks
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #5609: Exclusive Locks & Permission=20

"Rob Brucks" <rob.brucks@rackspace.com> writes:
> The user's permissions need to be checked before requesting an exclusive
> lock on the table for the alter.

Unfortunately, that cure is as bad or worse than the disease.  It's not
possible to do a permissions check before acquiring any lock --- what if
somebody is changing the permissions under you, or even dropping or
renaming the table?  We could acquire sharelock, do the permissions
check, and then upgrade to exclusive lock; but lock upgrading has its
own unpleasant consequences, notably increased risk of deadlock.

So it's unlikely this is going to get changed.

            regards, tom lane


Confidentiality Notice: This e-mail message (including any attached or
embedded documents) is intended for the exclusive and confidential use of t=
he
individual or entity to which this message is addressed, and unless otherwi=
se
expressly indicated, is confidential and privileged information of Rackspac=
e.
Any dissemination, distribution or copying of the enclosed material is proh=
ibited.
If you receive this transmission in error, please notify us immediately by =
e-mail
at abuse@rackspace.com, and delete the original message.
Your cooperation is appreciated.

Rob Brucks <rob.brucks@rackspace.com> writes:
> Is there any alternative I can use to prevent the exclusive lock (other than changing the app, we don't have access
tothe source code)? 

Not directly.  You might be able to do something with hiding the tables
in schemas that the unprivileged user hasn't got USAGE rights on, so
that he can't even name them.  But I suspect that having to qualify
table names with schema names is more code change than you want.

            regards, tom lane