From: Domingo Alvarez Duarte <mingodad@gmail.com>
To: lennin.caro@yahoo.com
Cc: Carol Walter <walterc@indiana.edu>
Cc: pgsql-admin@postgresql.org
Subject: Re: Extended security/restriction to any role with login access
Date: Fri, 27 Jun 2008 23:33:38 +0200
Message-ID: <c2f38c3e0806271433i41473565x57431346b372ef07@mail.gmail.com> (raw)
In-Reply-To: <408751.10145.qm@web59512.mail.ac4.yahoo.com>
References: <c2f38c3e0806271131ue85bf5bx9161a5dd7c6f3d20@mail.gmail.com>
<408751.10145.qm@web59512.mail.ac4.yahoo.com>
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.caro@yahoo.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@gmail.com>* wrote:
>
> From: Domingo Alvarez Duarte <mingodad@gmail.com>
> Subject: Re: [ADMIN] Extended security/restriction to any role with login
> access
> To: lennin.caro@yahoo.com
> Cc: "Carol Walter" <walterc@indiana.edu>, pgsql-admin@postgresql.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.caro@yahoo.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@gmail.com>* wrote:
>>
>> From: Domingo Alvarez Duarte <mingodad@gmail.com>
>> Subject: Re: [ADMIN] Extended security/restriction to any role with login
>> access
>> To: lennin.caro@yahoo.com
>> Cc: "Carol Walter" <walterc@indiana.edu>, pgsql-admin@postgresql.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.caro@yahoo.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@gmail.com>*wrote:
>>>
>>> From: Domingo Alvarez Duarte <mingodad@gmail.com>
>>> Subject: Re: [ADMIN] Extended security/restriction to any role with login
>>> access
>>> To: lennin.caro@yahoo.com
>>> Cc: "Carol Walter" <walterc@indiana.edu>, pgsql-admin@postgresql.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.caro@yahoo.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
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: mingodad@gmail.com, lennin.caro@yahoo.com, walterc@indiana.edu
Subject: Re: Extended security/restriction to any role with login access
In-Reply-To: <c2f38c3e0806271433i41473565x57431346b372ef07@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox