Thread: Schemas and access

I am just beginning to investigate schemas, and have run into a
problem.  Searching the mailing lists and documentation doesn't help.

This is 7.3.3 on Linux, kernel 2.4.21, good ole Slackware, compiled
from source.

Two users, JoeBob and MaryJo.  JoeBob has schema xyzzy with table
plugh.  JoeBob has run

    GRANT SELECT on xyzzy.plugh TO public;

JoeBob and MaryJo can both show permissions:

    joebob=> \z zyzzy.*
    Access privileges for database "joebob"
     Schema | Table  | Access privileges
    --------+--------+--------------------
     xyzzy  | plugh  | {=r,joebob=arwdRxt}

JoeBob can do anything with the table, that works.  But MaryJo can't
even select:

    joebob=> select * from xyzzy.plugh;
    ERROR:  xyzzy: permission denied

It sure looks like the schema itself is invisible to MaryJo, but
MaryJo can run \dt xyzzy.* perfectly fine.

Apparently I haven't quite got the hang of schema yet.  Would someone
enlighten me, please?

--
            ... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._.
     Felix Finch: scarecrow repairman & rocket surgeon / felix@crowfix.com
  GPG = E987 4493 C860 246C 3B1E  6477 7838 76E9 182E 8151 ITAR license #4933
I've found a solution to Fermat's Last Theorem but I see I've run out of room o

> I am just beginning to investigate schemas, and have run into a
> problem.  Searching the mailing lists and documentation doesn't help.
>
> This is 7.3.3 on Linux, kernel 2.4.21, good ole Slackware, compiled
> from source.
>
> Two users, JoeBob and MaryJo.  JoeBob has schema xyzzy with table
> plugh.  JoeBob has run
>
>     GRANT SELECT on xyzzy.plugh TO public;
>
> JoeBob and MaryJo can both show permissions:
>
>     joebob=> \z zyzzy.*
>     Access privileges for database "joebob"
>      Schema | Table  | Access privileges
>     --------+--------+--------------------
>      xyzzy  | plugh  | {=r,joebob=arwdRxt}
>
> JoeBob can do anything with the table, that works.  But MaryJo can't
> even select:
>
>     joebob=> select * from xyzzy.plugh;
>     ERROR:  xyzzy: permission denied
>
> It sure looks like the schema itself is invisible to MaryJo, but
> MaryJo can run \dt xyzzy.* perfectly fine.

I think there is a "greant usage on schema" type statement that you
probably need.

felix-lists@crowfix.com writes:
>     joebob=> select * from xyzzy.plugh;
>     ERROR:  xyzzy: permission denied
> It sure looks like the schema itself is invisible to MaryJo, but
> MaryJo can run \dt xyzzy.* perfectly fine.

You need to grant USAGE permission on the schema to MaryJo.

We've seen this mistake often enough that I wonder if maybe schemas
should default to granting USAGE permission to public ...

            regards, tom lane