Re: Upgraded, now permission denied.

>Well, that's what I'd expect to see --- but the prompt indicates that you
>did this in the "postgres" database, which is not where you're having the
>problem. We need to look at the public schema within the mypcc database.

Oops. Here's the mypcc database.

psql mypcc mypcc
mypcc=> select * from pg_namespace where nspname = 'public';
nspname | nspowner | nspacl
---------+----------+------------------------
public | 10 | {postgres=UC/postgres}
(1 row)

mypcc=> \dt
No relations found.

Hmm. However,

mypcc=> \dt *.* Returns all tables, including
public | uiddate | table | postgres

psql mypcc postgres
mypcc=# select * from uiddate where dateonline >= '2014-06-11';
uid | dateonline | uiddateonline | server
-----+------------+---------------+--------
(0 rows)

So it works for the postgres user. I changed the uiddate table owner to
the user 'mypcc' expecting the query to work, and it didn't.

psql mypcc mypcc
mypcc=> select * from uiddate where dateonline >= '2014-01-01';
ERROR: relation "uiddate" does not exist

On Thu, Jun 12, 2014 at 3:11 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> [ please keep the list cc'd ]
>
> Jason Whitener <jwhitene(at)pcc(dot)edu> writes:
> > postgres=# select * from pg_namespace where nspname = 'public';
> > nspname | nspowner | nspacl
> > ---------+----------+-------------------------------------
> > public | 10 | {postgres=UC/postgres,=UC/postgres}
> > (1 row)
>
> Well, that's what I'd expect to see --- but the prompt indicates that you
> did this in the "postgres" database, which is not where you're having the
> problem. We need to look at the public schema within the mypcc database.
>
> > The only grant/revokes I could find were near the top of the dump file,
> and
> > appears to just be on databases:
> > REVOKE ALL ON DATABASE mypcc FROM PUBLIC;
>
> Hm... that would have the effect of preventing connections to mypcc,
> except by the database owner and superusers. However, since you're
> not complaining that you can't connect, that doesn't seem to be
> your problem.
>
> > Does any of the above suggest that I am missing privileges to run queries
> > on the database mypcc using the user mypcc?
>
> The error message you quoted was quite clearly complaining about
> permissions on a schema, not a database. Those are entirely separate
> concepts in Postgres (although not in some other DBMSes, which perhaps
> is contributing to your confusion).
>
> regards, tom lane
>