Thread: Permission denied error - best way to fix?
Here's the error: pg_dump: SQL command failed pg_dump: Error message from server: ERROR: permission denied for relation pantryitems pg_dump: The command was: LOCK TABLE public.pantryitems IN ACCESS SHARE MODE Does the user need to be a superuser, or is there some way to GRANT this permission (if possible, I don't want this user to be able to modify the schema).. Mike
2011/2/8, Mike Christensen <mike@kitchenpc.com>: > Here's the error: > > pg_dump: SQL command failed > pg_dump: Error message from server: ERROR: permission denied for > relation pantryitems > pg_dump: The command was: LOCK TABLE public.pantryitems IN ACCESS SHARE MODE > > Does the user need to be a superuser, or is there some way to GRANT > this permission (if possible, I don't want this user to be able to > modify the schema).. > this lock need only SELECT privilege. ------------ pasman
On 02/08/2011 10:57 AM, Mike Christensen wrote: > Here's the error: > > pg_dump: SQL command failed > pg_dump: Error message from server: ERROR: permission denied for > relation pantryitems > pg_dump: The command was: LOCK TABLE public.pantryitems IN ACCESS SHARE MODE > > Does the user need to be a superuser, or is there some way to GRANT > this permission (if possible, I don't want this user to be able to > modify the schema).. > > Mike > pg_dump is an admin function so it will need to run as a role that can touch all requested objects and take action. For this particular case you could GRANT SELECT on that table to the restricted user. The trouble being is that this turns into a game of Whack-a-Mole fairly quickly. This is one of those situations where taken a long view on your permissions scheme will pay dividends. -- Adrian Klaver adrian.klaver@gmail.com
>> Here's the error: >> >> pg_dump: SQL command failed >> pg_dump: Error message from server: ERROR: permission denied for >> relation pantryitems >> pg_dump: The command was: LOCK TABLE public.pantryitems IN ACCESS SHARE >> MODE >> >> Does the user need to be a superuser, or is there some way to GRANT >> this permission (if possible, I don't want this user to be able to >> modify the schema).. >> >> Mike >> > > pg_dump is an admin function so it will need to run as a role that can touch > all requested objects and take action. For this particular case you could > GRANT SELECT on that table to the restricted user. The trouble being is that > this turns into a game of Whack-a-Mole fairly quickly. This is one of those > situations where taken a long view on your permissions scheme will pay > dividends. Hmm interesting, I wonder if this error is happening because pg_dump is being run from another program (RubyMine) and the process doesn't have whatever access it needs.
On 02/08/11 2:44 PM, Mike Christensen wrote: >>> Here's the error: >>> >>> pg_dump: SQL command failed >>> pg_dump: Error message from server: ERROR: permission denied for >>> relation pantryitems >>> pg_dump: The command was: LOCK TABLE public.pantryitems IN ACCESS SHARE >>> MODE >>> >>> Does the user need to be a superuser, or is there some way to GRANT >>> this permission (if possible, I don't want this user to be able to >>> modify the schema).. >>> >>> Mike >>> >> pg_dump is an admin function so it will need to run as a role that can touch >> all requested objects and take action. For this particular case you could >> GRANT SELECT on that table to the restricted user. The trouble being is that >> this turns into a game of Whack-a-Mole fairly quickly. This is one of those >> situations where taken a long view on your permissions scheme will pay >> dividends. > Hmm interesting, I wonder if this error is happening because pg_dump > is being run from another program (RubyMine) and the process doesn't > have whatever access it needs. its definately a postgres permissions error. I'd verify the pg user credentials being passed to pg_dump. if no user or anything is being specified, then its likely inheriting the unix username that the parent process runs as.
On Tuesday, February 08, 2011 2:44:51 pm Mike Christensen wrote: > >> Here's the error: > >> > >> pg_dump: SQL command failed > >> pg_dump: Error message from server: ERROR: permission denied for > >> relation pantryitems > >> pg_dump: The command was: LOCK TABLE public.pantryitems IN ACCESS SHARE > >> MODE > >> > >> Does the user need to be a superuser, or is there some way to GRANT > >> this permission (if possible, I don't want this user to be able to > >> modify the schema).. > >> > >> Mike > > > > pg_dump is an admin function so it will need to run as a role that can > > touch all requested objects and take action. For this particular case > > you could GRANT SELECT on that table to the restricted user. The trouble > > being is that this turns into a game of Whack-a-Mole fairly quickly. > > This is one of those situations where taken a long view on your > > permissions scheme will pay dividends. > > Hmm interesting, I wonder if this error is happening because pg_dump > is being run from another program (RubyMine) and the process doesn't > have whatever access it needs. Two possibilities, it is being run as the RubyMine user or as the user that is specified in the connection information RubyMine is using to pull and push data to the database. -- Adrian Klaver adrian.klaver@gmail.com