1

Possible Duplicate:
postgresql database owner can't access database - “No relations found.”

The core problem: I have a Django website that's complaining about relations (tables) not existing when I connect to it after attempting to run a SQL script generated by pg_dump MY_DATABASE_NAME -cOx -E UTF8 > MY_SCRIPT_NAME.sql

Before running the script I'd could connect and all was right with the world.

After running the script I live in a world of pain that I've attempted to alleviate by examining and altering ownership of MY_DATABASE_NAME and tables using the various methods described in Modify OWNER on all tables simultaneously in PostgreSQL

It would seem that I can only see relations via \dt on MY_DATABASE_NAME only after having first logged in as the postgres user on my server like so: sudo su - postgres.

When I SSH and run psql MY_DATABASE_NAME -U MY_NON_POSTGRES_USER without switching to the postgres user, \dt' results in ano relations found` message.

At first I presumed permissions or ownership was the problem. I logged-in as my server's postgres user and changed owner of MY_DATABASE_NAME and all of its tables to MY_NON_POSTGRES_USER.

Same results as before. Relations show up for my postgres user, but logging in as another role results in a 'no relations found message', and my website complains that 'relation some_table doesn't exist'

Why is this happening to me?

Community
  • 1
  • 1
mkelley33
  • 5,323
  • 10
  • 47
  • 71
  • Seems to be the same as http://stackoverflow.com/questions/7758533 – A.H. Oct 23 '11 at 14:21
  • @A.H. I agree. I'm surprised that one didn't show up at the top of the list that gets auto-generated when I typed my question. I'll flag it. I'm just happy I was able to figure it out :) Thank you! – mkelley33 Oct 23 '11 at 14:36
  • @A.H. would you mind voting to close this question. I voted to close it but it needs 4 more votes! – mkelley33 Oct 23 '11 at 14:37
  • Okkaay, if you ask yourself - here you are. – A.H. Oct 23 '11 at 15:04

1 Answers1

5

Wow. I really just composed a TL;DR description of my question and realized immediately afterwards my folly:

Although I'd connected to MY_DATABASE_NAME and given ownership to the correct user for all tables and the database, I'd forgotten to:

REVOKE ALL ON SCHEMA public FROM MY_NON_POSTGRES_USER;

GRANT ALL ON SCHEMA public TO MY_NON_POSTGRES_USER;

No more pain. All is right with the world again.

mkelley33
  • 5,323
  • 10
  • 47
  • 71