Re: Schemas and Privileges

2008/12/12 Filip Rembiałkowski <filip(dot)rembialkowski(at)gmail(dot)com>:
>
>
> 2008/12/11 TiTo(R) <tito(at)uel(dot)br>
>>
>> I installed the Postgres Database on my work planning replace Oracle on
>> the new IT systems. I have noticed some differences (between Oracle and
>> Posrgres) that I would like clarify.
>>
>> On postgres, I create 2 users and their schemas. Schema "User1" owned by
>> "User1" and Schema "User2" owned by "User2". User2 can´t access,
>> by 'select', data from tables in schema User1, that is OK, but the User2 can
>> display columns from tables in schema User1. I tried the commands "REVOKE
>> ALL ON SCHEMA User1 FROM User2" and "REVOKE ALL ON SCHEMA User1 FROM
>> public" but, they change nothing.
>>
>> On Oracle, the user can see the tables columns in others schemas when it
>> has "grant" on the table. If user dont have grant on table, it can´t display
>> the table columns of others schemas.
>>

AFAIK, the schemas in Oracle are different because you can connect directly
to it, in postgres only you can connect to database.
The Schemas on postgres are dependient of the database, so if you
are a user of the database, by default you can access the schemas, almost
to the description of them.
If you want this, you must create databases for each users.
There is no 'GRANT (OR REVOKE) DESCRIPTION ON...' in postgres, i don't
know if there is a way to simmulate this.

>> Is there something I can do to make postgres works like Oracle ? I am
>> using postgres 8.1.11 on SLES10.
>>
>
> postgres grant model is different; you can't hide db structure from db
> users.

See that, that's because i make separates db's (because you really want
is independency for each user)

> only the data is protected.
>
> this is not going to change soon, AFAIK.
>
>
>
>
>
> --
> Filip Rembiałkowski
>

--
Emanuel Calvo Franco
Syscope Postgresql Consultant
ArPUG / AOSUG Member