Обсуждение: Unauthorized users can see db schema and read functions
Hi,
I've noticed that any user who can logon to a db cluster can read the schema of all databases in it, including the code of all plpgsql functions. Even in schema's he/she doesn't have access to. For tables it just says 'access denied for schema bla', after which the structure is still shown to the user. For functions, there is no warning at all, you can just read (copy, paste) away.
I use pgAdmin3 1.6.2 as a front-end for both linux and windows servers, but I don't think restricting schema information should be a front-end responsibility.
o Why is schema information not restricted?
o Is there any way to prevent this, other than starting another cluster for this user's database
thx!
Willy-Bas
I've noticed that any user who can logon to a db cluster can read the schema of all databases in it, including the code of all plpgsql functions. Even in schema's he/she doesn't have access to. For tables it just says 'access denied for schema bla', after which the structure is still shown to the user. For functions, there is no warning at all, you can just read (copy, paste) away.
I use pgAdmin3 1.6.2 as a front-end for both linux and windows servers, but I don't think restricting schema information should be a front-end responsibility.
o Why is schema information not restricted?
o Is there any way to prevent this, other than starting another cluster for this user's database
thx!
Willy-Bas
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Willy-Bas Loos
Sent: dinsdag 30 januari 2007 9:41
To: pgsql-general@postgresql.org
Subject: [GENERAL] Unauthorized users can see db schema and read functionsHi,
I've noticed that any user who can logon to a db cluster can read the schema of all databases in it, including the code of all plpgsql functions. Even in schema's he/she doesn't have access to. For tables it just says 'access denied for schema bla', after which the structure is still shown to the user. For functions, there is no warning at all, you can just read (copy, paste) away.
I use pgAdmin3 1.6.2 as a front-end for both linux and windows servers, but I don't think restricting schema information should be a front-end responsibility.
o Why is schema information not restricted?
This is a limitation of <= 8.1.x.
In 8.2 the "CONNECT" priviledge was introducted on the database (still wondering why it was not done earlier).
Probably something similar should be done on the schema's too ;)
o Is there any way to prevent this, other than starting another cluster for this user's database
- Upgrade to 8.2...
- (Not sure and seems to contradict with your statement) you can move everything into a different schema, since public will always be readable, and others can be restricted
People who know it for sure should correct me, of course.
I found the behaviour strange too...
- Joris
Willy-Bas Loos wrote: > Hi, > > I've noticed that any user who can logon to a db cluster can read the > schema > of all databases in it, including the code of all plpgsql functions. > Even in > schema's he/she doesn't have access to. [snip] > o Why is schema information not restricted? > o Is there any way to prevent this, other than starting another cluster > for > this user's database The short answer is "because it's always been this way". The long answer is that users are per-cluster not per-database. So, they need a certain amount of access to the system tables just to connect and do anything. Now, in some cases there are views over the system tables that provide restricted access (compare pg_user to pg_shadow) but they aren't everywhere. However, you can use pg_hba.conf to restrict access to a database entirely. -- Richard Huxton Archonet Ltd
> In 8.2 the "CONNECT" priviledge was introducted on the database
Wow it works! :D
>However, you can use pg_hba.conf to restrict access to a database entirely.
That works too!
You can even allow all databases for all, and reject some databases to some, as long as the "reject" lines are above the "all" "all" "md5" line in pg_haba.conf
Great, thanks for the help.
Wow it works! :D
>However, you can use pg_hba.conf to restrict access to a database entirely.
That works too!
You can even allow all databases for all, and reject some databases to some, as long as the "reject" lines are above the "all" "all" "md5" line in pg_haba.conf
Great, thanks for the help.
On 1/30/07, Joris Dobbelsteen <Joris@familiedobbelsteen.nl> wrote:
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Willy-Bas Loos
Sent: dinsdag 30 januari 2007 9:41
To: pgsql-general@postgresql.org
Subject: [GENERAL] Unauthorized users can see db schema and read functionsHi,
I've noticed that any user who can logon to a db cluster can read the schema of all databases in it, including the code of all plpgsql functions. Even in schema's he/she doesn't have access to. For tables it just says 'access denied for schema bla', after which the structure is still shown to the user. For functions, there is no warning at all, you can just read (copy, paste) away.
I use pgAdmin3 1.6.2 as a front-end for both linux and windows servers, but I don't think restricting schema information should be a front-end responsibility.
o Why is schema information not restricted?This is a limitation of <= 8.1.x.In 8.2 the "CONNECT" priviledge was introducted on the database (still wondering why it was not done earlier).Probably something similar should be done on the schema's too ;)o Is there any way to prevent this, other than starting another cluster for this user's database
- Upgrade to 8.2...
- (Not sure and seems to contradict with your statement) you can move everything into a different schema, since public will always be readable, and others can be restricted
People who know it for sure should correct me, of course.I found the behaviour strange too...- Joris