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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]>* wrote: > > From: Domingo Alvarez Duarte <[EMAIL PROTECTED]> > Subject: Re: [ADMIN] Extended security/restriction to any role with login > access > To: [EMAIL PROTECTED] > Cc: "Carol Walter" <[EMAIL PROTECTED]>, [email protected] > 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 <[EMAIL PROTECTED]> > 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 <[EMAIL PROTECTED]>* wrote: >> >> From: Domingo Alvarez Duarte <[EMAIL PROTECTED]> >> Subject: Re: [ADMIN] Extended security/restriction to any role with login >> access >> To: [EMAIL PROTECTED] >> Cc: "Carol Walter" <[EMAIL PROTECTED]>, [email protected] >> 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 <[EMAIL PROTECTED]> >> 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 <[EMAIL PROTECTED]>*wrote: >>> >>> From: Domingo Alvarez Duarte <[EMAIL PROTECTED]> >>> Subject: Re: [ADMIN] Extended security/restriction to any role with login >>> access >>> To: [EMAIL PROTECTED] >>> Cc: "Carol Walter" <[EMAIL PROTECTED]>, [email protected] >>> 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 <[EMAIL PROTECTED]> >>> 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 >>>> >>>> >>>> >>> >>> >> >> > >
