Postgres Pro
Downloads
Home   >   mailing lists

Thread: Permission denied: LOCK TABLE test.__proc IN ACCESS SHARE MODE

Permission denied: LOCK TABLE test.__proc IN ACCESS SHARE MODE

From
Date:

When I run pg_dump I get this error:

pg_dump: SQL command failed
pg_dump: Error message from server: ERROR:  permission denied for schema test
pg_dump: The command was: LOCK TABLE test.__proc IN ACCESS SHARE MODE

I can't find out what transaction is responsible for this lock.  When
I look at the pg_locks table, I see only two locks, and they both seem
to be due to the fact that I am interacting with psql at the moment,
and this interaction is in no way directed to the database I'm trying
to dump.

Is there a way for me to find out the transaction responsible for the
lock that's causing the error message?  If so, how can I kill this
transaction?


kj

Re: Permission denied: LOCK TABLE test.__proc IN ACCESS SHARE MODE

From
Alvaro Herrera
Date:
kynn@panix.com wrote:

> pg_dump: SQL command failed
> pg_dump: Error message from server: ERROR:  permission denied for schema test
> pg_dump: The command was: LOCK TABLE test.__proc IN ACCESS SHARE MODE
>
> I can't find out what transaction is responsible for this lock.  When
> I look at the pg_locks table, I see only two locks, and they both seem
> to be due to the fact that I am interacting with psql at the moment,
> and this interaction is in no way directed to the database I'm trying
> to dump.

You are assuming that the problem is that there is a lock conflict.
This is not the case; you'd not get an error message (unless the command
had specified NOWAIT).

The problem here seems to be that you are trying to dump a schema or
table that the dumping user has no access to.  You may either grant
those permissions, or create the dump with another user.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Re: Permission denied: LOCK TABLE test.__proc IN ACCESS SHARE MODE

From
Tom Lane
Date:
<kynn@panix.com> writes:
> When I run pg_dump I get this error:

> pg_dump: SQL command failed
> pg_dump: Error message from server: ERROR:  permission denied for schema test
> pg_dump: The command was: LOCK TABLE test.__proc IN ACCESS SHARE MODE

> I can't find out what transaction is responsible for this lock.

What lock?  The error is "permission denied".  It looks like whatever
user you're running pg_dump as doesn't have USAGE permission on that
schema.

            regards, tom lane