Stephen Frost <sfrost@snowman.net> writes:
> IOW, yeah, you're right, the role doesn't really matter. One thing that
> occurs to me when I last ran into a problem with this- it was painful to
> debug because the "permission denied" error didn't indicate which schema
> the table it was trying to access was in. I wonder if we could fix
> that.
Hm, are you sure you actually got a "permission denied" error? Because
AFAICS such a message would name the schema:
regression=> select * from s1.t1;
ERROR: permission denied for schema s1
The part that is a bit nasty is if you try to put a schema in your
search_path that you don't have permissions for. My recollection is
that we just silently drop it out of the effective search path, so:
regression=> set search_path = s1, public;
SET
regression=> select current_schemas(false);
current_schemas
-----------------
{public}
(1 row)
regression=> select * from t1;
ERROR: relation "t1" does not exist
This isn't terribly user-friendly, but I'm not sure how to do better.
We can't really throw an error at the point of setting the search
path, because there are too many scenarios where a default search
path is effectively set up for you, and it might or might not list
some unsearchable (or even nonexistent) schemas.
regards, tom lane