pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedExtended security/restriction to any role with login access
11+ messages / 3 participants
[nested] [flat]
* Extended security/restriction to any role with login access
@ 2008-06-26 17:04 Domingo Alvarez Duarte <mingodad@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Domingo Alvarez Duarte @ 2008-06-26 17:04 UTC (permalink / raw)
To: pgsql-admin
Hello !
I'm trying to use postgresql in an application that by design will give
access to users to a subset of the database.
For example for customers access to products_view (wich will only show
public offers), orders (only their own orders).
I'll provide an application as user interface for the data.
For that I'll give for each of then a role in the database that will belong
to a group role customers_group.
The customers_group only has access to the views/functions that I'll
specify.
Till here no problem postgresql do that pretty well.
My concern is once I give login access to any user, even without grant
him/her any access to any database, he/she can using an application like
pgadmin3 view all databases/roles/functions/table-definitions on my server.
And that was not my intention.
Removing all from public doesn't work : revoke all on schema public from
public;
What I think would be the server behavior when I create a role with login
access an say that I only grant access to one view like this:
create role oneuser login;
grant select on somedatabase.someview to oneuser;
In that case when the user login the only thing he/she sees is the view
database.someview, even when they use pgadmin3 to connect.
Actually he/she can see with pgadmin3 : all databases, all roles and it's
right access, all tables on every database (no access to data), all
functions, all triggers, all table definitions.
The above isn't the intention to a user with a restrict view of the
database.
Can I achieve it actually, if not how hard could be to implement that in the
official release ?
Thanks in advance for any feedback/ideas !
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Extended security/restriction to any role with login access
@ 2008-06-26 17:34 Carol Walter <walterc@indiana.edu>
parent: Domingo Alvarez Duarte <mingodad@gmail.com>
0 siblings, 2 replies; 11+ messages in thread
From: Carol Walter @ 2008-06-26 17:34 UTC (permalink / raw)
To: Domingo Alvarez Duarte <mingodad@gmail.com>; +Cc: pgsql-admin
Hello, Domingo,
My question is why do your users need access to pgadmin3?
I have not used pgadmin3; we use phpPgAdmin. I can restrict access
to that by putting it behind .htaccess. That is only users with a
user name in .htacess can run phpPgAdmin. In the case of pgadmin3,
shouldn't you be able to restrict access to it by setting privs at
the operating system level? With phpPgAdmin, I can also restrict it
so a user can only see the databases the s/he owns. Postgres owns my
databases so I can't do it this way, but it could be done.
Carol
On Jun 26, 2008, at 1:04 PM, Domingo Alvarez Duarte wrote:
> Hello !
>
> I'm trying to use postgresql in an application that by design will
> give access to users to a subset of the database.
>
> For example for customers access to products_view (wich will only
> show public offers), orders (only their own orders).
>
> I'll provide an application as user interface for the data.
>
> For that I'll give for each of then a role in the database that
> will belong to a group role customers_group.
>
> The customers_group only has access to the views/functions that
> I'll specify.
>
> Till here no problem postgresql do that pretty well.
>
> My concern is once I give login access to any user, even without
> grant him/her any access to any database, he/she can using an
> application like pgadmin3 view all databases/roles/functions/table-
> definitions on my server. And that was not my intention.
>
> Removing all from public doesn't work : revoke all on schema public
> from public;
>
> What I think would be the server behavior when I create a role with
> login access an say that I only grant access to one view like this:
>
> create role oneuser login;
> grant select on somedatabase.someview to oneuser;
>
> In that case when the user login the only thing he/she sees is the
> view database.someview, even when they use pgadmin3 to connect.
>
> Actually he/she can see with pgadmin3 : all databases, all roles
> and it's right access, all tables on every database (no access to
> data), all functions, all triggers, all table definitions.
>
> The above isn't the intention to a user with a restrict view of the
> database.
>
> Can I achieve it actually, if not how hard could be to implement
> that in the official release ?
>
> Thanks in advance for any feedback/ideas !
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Extended security/restriction to any role with login access
@ 2008-06-26 18:44 Lennin Caro <lennin.caro@yahoo.com>
parent: Carol Walter <walterc@indiana.edu>
1 sibling, 1 reply; 11+ messages in thread
From: Lennin Caro @ 2008-06-26 18:44 UTC (permalink / raw)
To: Domingo Alvarez Duarte <mingodad@gmail.com>; Carol Walter <walterc@indiana.edu>; +Cc: pgsql-admin
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
--- On Thu, 6/26/08, Carol Walter <walterc@indiana.edu> wrote:
From: Carol Walter <walterc@indiana.edu>
Subject: Re: [ADMIN] Extended security/restriction to any role with login access
To: "Domingo Alvarez Duarte" <mingodad@gmail.com>
Cc: pgsql-admin@postgresql.org
Date: Thursday, June 26, 2008, 5:34 PM
Hello, Domingo,
My question is why do your users need access to pgadmin3?
I have not used pgadmin3; we use phpPgAdmin. I can restrict access
to that by putting it behind .htaccess. That is only users with a
user name in .htacess can run phpPgAdmin. In the case of pgadmin3,
shouldn't you be able to restrict access to it by setting privs at
the operating system level? With phpPgAdmin, I can also restrict it
so a user can only see the databases the s/he owns. Postgres owns my
databases so I can't do it this way, but it could be done.
Carol
On Jun 26, 2008, at 1:04 PM, Domingo Alvarez Duarte wrote:
> Hello !
>
> I'm trying to use postgresql in an application that by design will
> give access to users to a subset of the database.
>
> For example for customers access to products_view (wich will only
> show public offers), orders (only their own orders).
>
> I'll provide an application as user interface for the data.
>
> For that I'll give for each of then a role in the database that
> will belong to a group role customers_group.
>
> The customers_group only has access to the views/functions that
> I'll specify.
>
> Till here no problem postgresql do that pretty well.
>
> My concern is once I give login access to any user, even without
> grant him/her any access to any database, he/she can using an
> application like pgadmin3 view all databases/roles/functions/table-
> definitions on my server. And that was not my intention.
>
> Removing all from public doesn't work : revoke all on schema public
> from public;
>
> What I think would be the server behavior when I create a role with
> login access an say that I only grant access to one view like this:
>
> create role oneuser login;
> grant select on somedatabase.someview to oneuser;
>
> In that case when the user login the only thing he/she sees is the
> view database.someview, even when they use pgadmin3 to connect.
>
> Actually he/she can see with pgadmin3 : all databases, all roles
> and it's right access, all tables on every database (no access to
> data), all functions, all triggers, all table definitions.
>
> The above isn't the intention to a user with a restrict view of the
> database.
>
> Can I achieve it actually, if not how hard could be to implement
> that in the official release ?
>
> Thanks in advance for any feedback/ideas !
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Extended security/restriction to any role with login access
@ 2008-06-26 21:45 Domingo Alvarez Duarte <mingodad@gmail.com>
parent: Lennin Caro <lennin.caro@yahoo.com>
0 siblings, 1 reply; 11+ messages in thread
From: Domingo Alvarez Duarte @ 2008-06-26 21:45 UTC (permalink / raw)
To: lennin.caro@yahoo.com; +Cc: Carol Walter <walterc@indiana.edu>; pgsql-admin
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
>
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Extended security/restriction to any role with login access
@ 2008-06-27 05:43 Domingo Alvarez Duarte <mingodad@gmail.com>
parent: Carol Walter <walterc@indiana.edu>
1 sibling, 0 replies; 11+ messages in thread
From: Domingo Alvarez Duarte @ 2008-06-27 05:43 UTC (permalink / raw)
To: Carol Walter <walterc@indiana.edu>; +Cc: pgsql-admin
My users will not need to use pgadmin3, the whole problem is any user with
login access to a postgresql server can use pgadmin3 to connect and see all
databases/roles/functions/table-definitions/schemas. This is not a problem
with pgadmin3 but with postgresql server.
Look at it in a general way someone write one application that will connect
to postgresql server (intranet or internet) the user of that application
will see mor or less data/functionality based on her/his credentials
(username/password) that are stored on postgresql and assigned grant access
to the correspondig part of the database.
My concern is once I give login access to any user, even without grant
him/her any access to any database, he/she can using an application like
pgadmin3 view all databases/roles/functions/table-definitions on my server.
And that was not my intention.
What I think would be the server behavior when I create a role with login
access an say that I only grant access to one view like this:
create role oneuser login;
grant select on somedatabase.someview to oneuser;
In that case when the user login the only thing he/she sees is the view
database.someview, even when they use pgadmin3 to connect.
On Thu, Jun 26, 2008 at 7:34 PM, Carol Walter <walterc@indiana.edu> wrote:
> Hello, Domingo,
>
> My question is why do your users need access to pgadmin3?
> I have not used pgadmin3; we use phpPgAdmin. I can restrict access to that
> by putting it behind .htaccess. That is only users with a user name in
> .htacess can run phpPgAdmin. In the case of pgadmin3, shouldn't you be able
> to restrict access to it by setting privs at the operating system level?
> With phpPgAdmin, I can also restrict it so a user can only see the
> databases the s/he owns. Postgres owns my databases so I can't do it this
> way, but it could be done.
>
> Carol
>
>
> On Jun 26, 2008, at 1:04 PM, Domingo Alvarez Duarte wrote:
>
> Hello !
>>
>> I'm trying to use postgresql in an application that by design will give
>> access to users to a subset of the database.
>>
>> For example for customers access to products_view (wich will only show
>> public offers), orders (only their own orders).
>>
>> I'll provide an application as user interface for the data.
>>
>> For that I'll give for each of then a role in the database that will
>> belong to a group role customers_group.
>>
>> The customers_group only has access to the views/functions that I'll
>> specify.
>>
>> Till here no problem postgresql do that pretty well.
>>
>> My concern is once I give login access to any user, even without grant
>> him/her any access to any database, he/she can using an application like
>> pgadmin3 view all databases/roles/functions/table-definitions on my server.
>> And that was not my intention.
>>
>> Removing all from public doesn't work : revoke all on schema public from
>> public;
>>
>> What I think would be the server behavior when I create a role with login
>> access an say that I only grant access to one view like this:
>>
>> create role oneuser login;
>> grant select on somedatabase.someview to oneuser;
>>
>> In that case when the user login the only thing he/she sees is the view
>> database.someview, even when they use pgadmin3 to connect.
>>
>> Actually he/she can see with pgadmin3 : all databases, all roles and it's
>> right access, all tables on every database (no access to data), all
>> functions, all triggers, all table definitions.
>>
>> The above isn't the intention to a user with a restrict view of the
>> database.
>>
>> Can I achieve it actually, if not how hard could be to implement that in
>> the official release ?
>>
>> Thanks in advance for any feedback/ideas !
>>
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Extended security/restriction to any role with login access
@ 2008-06-27 13:44 Lennin Caro <lennin.caro@yahoo.com>
parent: Domingo Alvarez Duarte <mingodad@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Lennin Caro @ 2008-06-27 13:44 UTC (permalink / raw)
To: Domingo Alvarez Duarte <mingodad@gmail.com>; +Cc: Carol Walter <walterc@indiana.edu>; pgsql-admin
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Extended security/restriction to any role with login access
@ 2008-06-27 14:35 Domingo Alvarez Duarte <mingodad@gmail.com>
parent: Lennin Caro <lennin.caro@yahoo.com>
0 siblings, 1 reply; 11+ messages in thread
From: Domingo Alvarez Duarte @ 2008-06-27 14:35 UTC (permalink / raw)
To: lennin.caro@yahoo.com; +Cc: Carol Walter <walterc@indiana.edu>; pgsql-admin
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
>>
>>
>>
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Extended security/restriction to any role with login access
@ 2008-06-27 15:12 Lennin Caro <lennin.caro@yahoo.com>
parent: Domingo Alvarez Duarte <mingodad@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Lennin Caro @ 2008-06-27 15:12 UTC (permalink / raw)
To: Domingo Alvarez Duarte <mingodad@gmail.com>; +Cc: Carol Walter <walterc@indiana.edu>; pgsql-admin
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Extended security/restriction to any role with login access
@ 2008-06-27 18:31 Domingo Alvarez Duarte <mingodad@gmail.com>
parent: Lennin Caro <lennin.caro@yahoo.com>
0 siblings, 1 reply; 11+ messages in thread
From: Domingo Alvarez Duarte @ 2008-06-27 18:31 UTC (permalink / raw)
To: lennin.caro@yahoo.com; +Cc: Carol Walter <walterc@indiana.edu>; pgsql-admin
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
>>>
>>>
>>>
>>
>>
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Extended security/restriction to any role with login access
@ 2008-06-27 19:10 Lennin Caro <lennin.caro@yahoo.com>
parent: Domingo Alvarez Duarte <mingodad@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Lennin Caro @ 2008-06-27 19:10 UTC (permalink / raw)
To: Domingo Alvarez Duarte <mingodad@gmail.com>; +Cc: Carol Walter <walterc@indiana.edu>; pgsql-admin
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Extended security/restriction to any role with login access
@ 2008-06-27 21:33 Domingo Alvarez Duarte <mingodad@gmail.com>
parent: Lennin Caro <lennin.caro@yahoo.com>
0 siblings, 0 replies; 11+ messages in thread
From: Domingo Alvarez Duarte @ 2008-06-27 21:33 UTC (permalink / raw)
To: lennin.caro@yahoo.com; +Cc: Carol Walter <walterc@indiana.edu>; pgsql-admin
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
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2008-06-27 21:33 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-06-26 17:04 Extended security/restriction to any role with login access Domingo Alvarez Duarte <mingodad@gmail.com>
2008-06-26 17:34 ` Carol Walter <walterc@indiana.edu>
2008-06-26 18:44 ` Lennin Caro <lennin.caro@yahoo.com>
2008-06-26 21:45 ` Domingo Alvarez Duarte <mingodad@gmail.com>
2008-06-27 13:44 ` Lennin Caro <lennin.caro@yahoo.com>
2008-06-27 14:35 ` Domingo Alvarez Duarte <mingodad@gmail.com>
2008-06-27 15:12 ` Lennin Caro <lennin.caro@yahoo.com>
2008-06-27 18:31 ` Domingo Alvarez Duarte <mingodad@gmail.com>
2008-06-27 19:10 ` Lennin Caro <lennin.caro@yahoo.com>
2008-06-27 21:33 ` Domingo Alvarez Duarte <mingodad@gmail.com>
2008-06-27 05:43 ` Domingo Alvarez Duarte <mingodad@gmail.com>
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