Thread: pg_dump permssion denied problem

"Murphy, Kevin" <MURPHYKE@email.chop.edu> writes:

> On 5/1/15, 11:10 AM, "Murphy, Kevin" <MURPHYKE@email.chop.edu> wrote:
>
>     Heres a variation on the theme of
http://postgresql.nabble.com/Issue-dumping-schema-using-readonly-user-td5837749.html
>     In a 9.3.4 database I dont own, I am able to create schemas, so I do, and I create various tables within a
schema. However, I am unable to use pg_dump (9.3.6) to 
>     dump the structure of my tables:
>
> Aha.  When I add `schema=myschema`, there is no problem.   It still seems interesting that the error is produced in
theprevious case even though I own 
> thingy_thingy_id_seq.

You probably have identically named objects in default schema (usually
public) that your userID doesn't have access to and thus pg_dump is
trying to read those items when you don't schema-qualify the dump.

> -Kevin
>
>     $ pg_dump --schema-only -h aserver adb
>
>     pg_dump: [archiver (db)] query failed: ERROR:  permission denied for relation thingy_thingy_id_seq
>     pg_dump: [archiver (db)] query was: SELECT sequence_name, start_value, increment_by, CASE WHEN increment_by > 0
ANDmax_value = 9223372036854775807 THEN NULL 
>     WHEN increment_by < 0 AND max_value = -1 THEN NULL      ELSE max_value END AS max_value, CASE WHEN increment_by >
0AND min_value = 1 THEN NULL 
>     WHEN increment_by < 0 AND min_value = -9223372036854775807 THEN NULL
>     ELSE min_value END AS min_value, cache_value, is_cycled FROM thingy_thingy_id_seq
>
>     But in psql, \dt and \ds show that I own all my tables and sequences, and of course I can run the SELECT
statementabove with no problem, as in the referenced 
>     thread.
>
>     I am reporting this just to provide another data point on this behavior.
>
>     Regards,
>     Kevin Murphy
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800