Re: Permissions to schema

2009/10/26 Eduardo Sá dos Reis <eduardoreis(at)pjf(dot)mg(dot)gov(dot)br>

> Hi,
>
> I have the structure on Postgre:
>
> *database*: dbGlobal
>
> *schema1:* sch_sist1 *owner *sch_sist1: john
>
> *schema2 * sch_sist2 *owner *sch_sist2: mary
>
>
> I' m using pgAdmin III.
>
> Is there a way to avoid the mary to access the schema sch_sist1 ?
>
> I don't want that mary see/access the schema of john and I don't want
> that john see/access the schema of mary.
>
> Anybody has a solution.
>

http://www.postgresql.org/docs/current/static/sql-revoke.html

kgb=# create schema s1;
CREATE SCHEMA
kgb=# create user mary;
CREATE ROLE
kgb=# revoke all on schema s1 from mary;
REVOKE
kgb=# create table s1.testing (id int);
CREATE TABLE
kgb=# \c kgb mary
kgb=> set search_path=s1;
SET
kgb=> \dt
No relations found.
kgb=> select * from s1.testing;
ERROR: permission denied for schema s1
kgb=>

--Scott