Re: Extended security/restriction to any role with login access

Ok !

I could revoke privileges to postgres.pg_catalog from public but now new
users can login but can't see anything, even when I grant access to then on
one view I've created on a new database.

Can someone give a detailed stepe by step on how to achieve this:

- Probably change postgres and template1 to disallow access to public group
by default.
- After changes new users without superuser privileges or owned objects
can't see anything.
- Once granted access to a part of a database let's say a view, the granted
user can see it.

Let's start with fresh default postgresql installation.
Make the necessary changes.
Create to users with login access only, user1 and user2.
Create a new database db1 with owner user1.
Right now user1 can login and access his database db1. And user2 can login
but can't see anithing.
Now user1 create a table table1 in his public schema and a view view1 on
that table, he grants select privilege to user2.
Now user2 can connect and see only db1.public.view1

That is wat I want to achive, and I think that more people will need
something like this too.

Thanks again in advance for any help on the matter !
On Fri, Jun 27, 2008 at 9:10 PM, Lennin Caro <lennin(dot)caro(at)yahoo(dot)com> wrote:

> in pgadmin3:
>
> Menu -> Display
> check the option "System Object"
>
> this show you the catalogs of postgresql "pg_xxxxxxxx"
>
> change the grants to users here
>
> --- On *Fri, 6/27/08, Domingo Alvarez Duarte <mingodad(at)gmail(dot)com>* wrote:
>
> From: Domingo Alvarez Duarte <mingodad(at)gmail(dot)com>
> Subject: Re: [ADMIN] Extended security/restriction to any role with login
> access
> To: lennin(dot)caro(at)yahoo(dot)com
> Cc: "Carol Walter" <walterc(at)indiana(dot)edu>, pgsql-admin(at)postgresql(dot)org
> Date: Friday, June 27, 2008, 6:31 PM
>
>
> How can I change that ?
> I mean make the catalog of tables, function and roles private and only
> accessible to granted users ?
>
> Someon mentioned once to make changes in template1, wich changes will be
> nneded ? In case this is possible.
>
> Thanks for any help/sugestion !
>
> On Fri, Jun 27, 2008 at 5:12 PM, Lennin Caro <lennin(dot)caro(at)yahoo(dot)com>
> wrote:
>
>>
>> Ok...
>>
>> the catalog of tables, function and roles are public. Pgadmin use the
>> catalog to create the tree of databases, function and roles. The user can
>> see this but cant change this. In oracle the catalog of user, tables,
>> function are public
>>
>> --- On *Fri, 6/27/08, Domingo Alvarez Duarte <mingodad(at)gmail(dot)com>* wrote:
>>
>> From: Domingo Alvarez Duarte <mingodad(at)gmail(dot)com>
>> Subject: Re: [ADMIN] Extended security/restriction to any role with login
>> access
>> To: lennin(dot)caro(at)yahoo(dot)com
>> Cc: "Carol Walter" <walterc(at)indiana(dot)edu>, pgsql-admin(at)postgresql(dot)org
>> Date: Friday, June 27, 2008, 2:35 PM
>>
>>
>> Look this isn't the point I know what can be done with pg_hba.conf
>>
>> The main point is:
>>
>> When I create a postgresql user and grant to it only access to part of a
>> database (let's say one view). I'm expecting that the server will honor it.
>> But right now postgresql server isn't.
>>
>> This is the reason I'm writing here and tried too hackers list but no one
>> seems to understand the importance of this point.
>>
>> Maybe it's my fault of knowledge but till now no one showed me how to get
>> the expected result, people try to tell me how to use firewall, change the
>> way of build my application, they don't understand the point. I'll repeat it
>> again:
>>
>>
>> When I create a postgresql user and grant to it only access to part of a
>> database (let's say one view). I'm expecting that the server will honor it.
>> But right now postgresql server isn't.
>>
>> Actually any user with login access can see all
>> databases/roles/functions/table-definitions/triggers. What I thinks isn't
>> correct.
>>
>> I'll apreciate any solution to this problem.
>>
>> On Fri, Jun 27, 2008 at 3:44 PM, Lennin Caro <lennin(dot)caro(at)yahoo(dot)com>
>> wrote:
>>
>>> ok, let try this .....
>>>
>>> open the pg_hba.conf and check the line
>>>
>>> host all all 127.0.0.1/32 trust
>>>
>>> and change to
>>>
>>> host all all 127.0.0.1/32 password
>>>
>>>
>>>
>>> --- On *Thu, 6/26/08, Domingo Alvarez Duarte <mingodad(at)gmail(dot)com>*wrote:
>>>
>>> From: Domingo Alvarez Duarte <mingodad(at)gmail(dot)com>
>>> Subject: Re: [ADMIN] Extended security/restriction to any role with login
>>> access
>>> To: lennin(dot)caro(at)yahoo(dot)com
>>> Cc: "Carol Walter" <walterc(at)indiana(dot)edu>, pgsql-admin(at)postgresql(dot)org
>>> Date: Thursday, June 26, 2008, 9:45 PM
>>>
>>>
>>> I did the following:
>>> -Connect as superuser postgres with pgadmin and create a user -> noaccess
>>> CREATE ROLE noaccess LOGIN
>>> NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE;
>>>
>>> -Disconnect from the server
>>> -Connect to the server with user 'noaccess' through pgadmin3, and I can
>>> see all databases/functions/schemas/roles.
>>>
>>> What am I missing ?
>>>
>>> On Thu, Jun 26, 2008 at 8:44 PM, Lennin Caro <lennin(dot)caro(at)yahoo(dot)com>
>>> wrote:
>>>
>>>> hello...
>>>>
>>>> you can restric acces from all the databases in your cluster. When you
>>>> use pgadmin3 this show all the databases but if you dont have access to the
>>>> databases you cant see the struct of this.
>>>>
>>>> check waht user use pgadmin3 for connect to databases
>>>>
>>>> create groups and add privileges to the group later add the users to the
>>>> group
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>