Thread: Upgraded, now permission denied.

Jason Whitener <jwhitene@pcc.edu> writes:
> Queries that used to run on 8.1 now complain about relations not found,
> like:
>         Select * from uiddate where dateonline >= '#hourago#'
> returns:  ERROR: relation "uiddate" does not exist Position: 15

> The database name is 'mypcc'.  The schema is 'public'.  I can visibly see
> the table 'uiddate' in the PgAdmin gui.

> If I do a query like:
> SELECT * FROM public.uiddate where dateonline >= '2014-06-11 14:33:14';
> It returns:
> ERROR:  permission denied for schema public

I suspect these are both symptoms of the same problem, namely that
the "public" schema lacks public access permissions.  I'm not sure
exactly how it got that way --- might be interesting to look at

    select * from pg_namespace where nspname = 'public';

and if you still have the pg_dump file from 8.1, look to see if
there are any grant/revoke commands for schema public in it.

But in any case, the fix is probably

    grant all on schema public to public;

            regards, tom lane

On Jun 11, 2014 8:00 PM, "Jason Whitener" <jwhitene@pcc.edu> wrote:
>
> I did a pg_dumpall on PostgreSQL 8.1.23 and then imported that data to 8.4.20 following this guide:  http://www.postgresql.org/docs/8.4/static/install-upgrading.html
>
> Queries that used to run on 8.1 now complain about relations not found, like:
>
>         Select * from uiddate where dateonline >= '#hourago#'
>
> returns:  ERROR: relation "uiddate" does not exist Position: 15
>
> The database name is 'mypcc'.  The schema is 'public'.  I can visibly see the table 'uiddate' in the PgAdmin gui.  
>
> If I do a query like:
> SELECT * FROM public.uiddate where dateonline >= '2014-06-11 14:33:14';
>
> It returns:
> ERROR:  permission denied for schema public
>
> I checked on permissions: 
> SELECT datname as "Relation", datacl as "Access permissions" FROM pg_database WHERE datname = 'mypcc';  Relation | Access permissions
> ----------+--------------------
>  mypcc    | {mypcc=CTc/mypcc}
> (1 row)
>
> This is my first upgrade, so I guess I must be missing something simple. 
>
> Visibly in the PgAdmin gui, it looks like the data import went fine.  I see all my tables and users.  
>
> This query is being initiated by an app running on the same host. I made sure that the 8.4 pg_hba.conf lines for localhost matched what I had on the 8.1 server.  
>
> host    all         all         127.0.0.1/32          password
>
> Both servers respond identically to nslookup localhost.  
>
> I'm not sure what to try next.  
>
>

Did you get any errors during the restore? If so,  that could give us some more insight to the cause. Also,  if possible make sure you do the dump with the 8.4 pg_dump to ensure it works best restoring to the new version.

Keith

[ please keep the list cc'd ]

Jason Whitener <jwhitene@pcc.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

Jason Whitener <jwhitene@pcc.edu> writes:
>> 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)

Ah-hah, as I rather suspected: public access to the "public" schema has
been revoked.  (There's some info in the GRANT man page about reading
ACL displays, if you're not sure how to interpret the above.)

> mypcc=> \dt
> No relations found.

The reason for these odd behaviors is that unreadable schemas get dropped
out of your search_path (rather than throwing errors).  So the tables are
there, but unless you're superuser you can't get at them.

I'd suggest a "GRANT ALL ON SCHEMA PUBLIC TO PUBLIC".

It's still unclear how you got those permissions revoked, but it seems
pretty clear that you didn't really want to do that.

            regards, tom lane