
How hard would it be to add this:

"The \l command should only list databases that the current user is
authorized for, the \du command should only list users authorized for the
current database (and perhaps only superusers should get even that much
information), etc.  Perhaps it is possible to set PG to do this, but that
should probably be the default."

This is from a PgSQL vs MySQL thread on -general ... how hard would it be
make it so that a non-superuse user can't do a \l and see everyone's
databases?  Or, when doing a \d in a database you are able to connect to,
it would only show those tables that you are authorized for?


----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664

Mon, 29 Mar 2004 00:28:34 -0400 (AST)
"Marc G. Fournier" <scrappy@postgresql.org>
Increasing security in a shared environment ...
> "The \l command should only list databases that the current user is
> authorized for, the \du command should only list users authorized for the
> current database (and perhaps only superusers should get even that much
> information), etc.  Perhaps it is possible to set PG to do this, but that
> should probably be the default."
> 
> This is from a PgSQL vs MySQL thread on -general ... how hard would it be
> make it so that a non-superuse user can't do a \l and see everyone's
> databases?  Or, when doing a \d in a database you are able to connect to,
> it would only show those tables that you are authorized for?

Well, you can just go SELECT * FROM pg_database;  so fixing \l won't do 
anything.

I too would like to see more security in this respect, but it will be 
difficult if not impossible to implement methinks...

Chris


Mon, 29 Mar 2004 12:46:58 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Increasing security in a shared environment ...
Hi Christopher,

> > "The \l command should only list databases that the current user is
> > authorized for, the \du command should only list users authorized for the
> > current database (and perhaps only superusers should get even that much
> > information), etc.  Perhaps it is possible to set PG to do this, but that
> > should probably be the default."
> > 
Seem reasonable. Why not prevent normal users to dig on the pg_catalog? What is the impact of it?

> Well, you can just go SELECT * FROM pg_database;  so fixing \l won't do 
> anything.
> 
> I too would like to see more security in this respect, but it will be 
> difficult if not impossible to implement methinks...
> 
Why is it "impossible"?

-- 
Euler Taveira de Oliveira
euler (at) ufgnet.ufg.br
Desenvolvedor Web e Administrador de Sistemas
UFGNet - Universidade Federal de Goiás

Mon, 29 Mar 2004 14:08:04 -0300
Euler Taveira de Oliveira <euler@ufgnet.ufg.br>
Re: Increasing security in a shared environment ...
Christopher Kings-Lynne wrote:

>> "The \l command should only list databases that the current user is
>> authorized for, the \du command should only list users authorized for 
>> the
>> current database (and perhaps only superusers should get even that much
>> information), etc.  Perhaps it is possible to set PG to do this, but 
>> that
>> should probably be the default."
>>
>> This is from a PgSQL vs MySQL thread on -general ... how hard would 
>> it be
>> make it so that a non-superuse user can't do a \l and see everyone's
>> databases?  Or, when doing a \d in a database you are able to connect 
>> to,
>> it would only show those tables that you are authorized for?
>
>
> Well, you can just go SELECT * FROM pg_database;  so fixing \l won't 
> do anything.
>
> I too would like to see more security in this respect, but it will be 
> difficult if not impossible to implement methinks...
>

I just played around briefly with removing *all* public access to a 
couple of catalog tables - pg_class and pg_attrdef. Obviously this 
breaks things like \d and friends. I'm not sure how much else it might 
break - certainly a non-privileged user was still able to select from a 
table, and create and drop a table. Maybe we should look at some 
paranoid settings for the catalog tables as an option for "create database".

My previous answer to this question has been "use a middleware layer 
that exposes just the operations you want exposed". But this issue has 
come up a few times so maybe some more thought is needed. Of course, we 
are only talking about metadata here, not user table contents, but maybe 
some people have a justifiable need to hide even the metadata.


cheers

andrew

Mon, 29 Mar 2004 12:09:01 -0500
Andrew Dunstan <andrew@dunslane.net>
Re: Increasing security in a shared environment ...
On Mon, 29 Mar 2004, Andrew Dunstan wrote:

> My previous answer to this question has been "use a middleware layer
> that exposes just the operations you want exposed". But this issue has
> come up a few times so maybe some more thought is needed. Of course, we
> are only talking about metadata here, not user table contents, but maybe
> some people have a justifiable need to hide even the metadata.

You could almost look at it from a security perspective ... if any user
can see all databases, then its simple enough to try and connect to them
all and see which ones are open ... its not hard to 'mis-configure'
pg_hba.conf without realizing it, leaving things open when you meant for
them to be closed ... it would be an added layer of protection ...

Does anyone know how ppl like Oracle handle this?  Are system catalogs
like this open to all users?

----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664

Mon, 29 Mar 2004 13:20:59 -0400 (AST)
"Marc G. Fournier" <scrappy@postgresql.org>
Re: Increasing security in a shared environment ...
It's rumoured that Euler Taveira de Oliveira once said:
> Hi Christopher,
>
>> > "The \l command should only list databases that the current user is
>> > authorized for, the \du command should only list users authorized
>> > for the current database (and perhaps only superusers should get
>> > even that much information), etc.  Perhaps it is possible to set PG
>> > to do this, but that should probably be the default."
>> >
> Seem reasonable. Why not prevent normal users to dig on the pg_catalog?
> What is the impact of it?

Because they can't use tools like pgAdmin or phpPgAdmin unless they can at
least read all the catalogs.
Regards, Dave



Mon, 29 Mar 2004 19:11:13 +0100 (BST)
"Dave Page" <dpage@vale-housing.co.uk>
Re: Increasing security in a shared environment ...
Andrew Dunstan <andrew@dunslane.net> writes:
> I just played around briefly with removing *all* public access to a 
> couple of catalog tables - pg_class and pg_attrdef. Obviously this 
> breaks things like \d and friends. I'm not sure how much else it might 
> break - 

pg_dump, for starters ...

I'm not sure that hiding the contents of the current database's catalog
is all that useful a goal in practice.  If you have two users sharing a
database then probably you *want* them to be able to exchange some
amount of information.  I can see the use-case for hiding contents of
the shared tables (pg_database, pg_shadow, pg_group) in installations
where different users have different databases but you want to run just
one common postmaster.  Even there, though, it doesn't seem all that
essential --- its only usefulness is security by obscurity.

			regards, tom lane

Mon, 29 Mar 2004 13:16:25 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Increasing security in a shared environment ... 
On Mon, 29 Mar 2004, Dave Page wrote:

> It's rumoured that Euler Taveira de Oliveira once said:
> > Hi Christopher,
> >
> >> > "The \l command should only list databases that the current user is
> >> > authorized for, the \du command should only list users authorized
> >> > for the current database (and perhaps only superusers should get
> >> > even that much information), etc.  Perhaps it is possible to set PG
> >> > to do this, but that should probably be the default."
> >> >
> > Seem reasonable. Why not prevent normal users to dig on the pg_catalog?
> > What is the impact of it?
>
> Because they can't use tools like pgAdmin or phpPgAdmin unless they can at
> least read all the catalogs.

k, but what I'm suggesting shouldn't prevent that, should it?  They should
only be able to see those resources that they have permissions to see, not
all of them ... no?

----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664

Mon, 29 Mar 2004 14:21:47 -0400 (AST)
"Marc G. Fournier" <scrappy@postgresql.org>
Re: Increasing security in a shared environment ...
It's rumoured that Marc G. Fournier once said:
> On Mon, 29 Mar 2004, Dave Page wrote:
>
> k, but what I'm suggesting shouldn't prevent that, should it?  They
> should only be able to see those resources that they have permissions
> to see, not all of them ... no?

Wouldn't that require per-row permissions?

ie. you can only see the pg_attribute rows for relations that you have
some access to?
Regards Dave



Mon, 29 Mar 2004 20:12:35 +0100 (BST)
"Dave Page" <dpage@vale-housing.co.uk>
Re: Increasing security in a shared environment ...
Tom Lane wrote:

>Andrew Dunstan <andrew@dunslane.net> writes:
>  
>
>>I just played around briefly with removing *all* public access to a 
>>couple of catalog tables - pg_class and pg_attrdef. Obviously this 
>>breaks things like \d and friends. I'm not sure how much else it might 
>>break - 
>>    
>>
>
>pg_dump, for starters ...
>  
>

Right. So I played around a little more and restored read priv but only 
for the db owner, which seemed to work, and makes some sense to me.

>I'm not sure that hiding the contents of the current database's catalog
>is all that useful a goal in practice.  If you have two users sharing a
>database then probably you *want* them to be able to exchange some
>amount of information.  
>

It's that "probably" that niggles a bit. I don't know what usage 
patterns other people have, and since my typical use is exactly *one* 
user other than the owner/dba, and all access is mediated by my 
middleware, none of this affects me. ISTM we need to cater for as broad 
a set of usage patterns as is reasonable.



>I can see the use-case for hiding contents of
>the shared tables (pg_database, pg_shadow, pg_group) in installations
>where different users have different databases but you want to run just
>one common postmaster.  Even there, though, it doesn't seem all that
>essential --- its only usefulness is security by obscurity.
>
>  
>

That phrase to me denotes "something they could easily discover if only 
they knew about it". How would they discover the contents of these, 
assuming they did know about them and we blocked access?

What is not clear to me is how we would even decide which databases to 
hide, if it is not an all or nothing deal. Marc's phrase "those 
resources that they have permissions to see" doesn't define it nearly 
nicely enough. Say I block access to db foo to all users but bar via 
pg_hba.conf. Would we then want to prevent all other users from seeing 
foo in the list of databases? Things like that are why I started 
exploring a somewhat broader approach.

cheers

andrew

Mon, 29 Mar 2004 14:25:24 -0500
Andrew Dunstan <andrew@dunslane.net>
Re: Increasing security in a shared environment ...
On Mon, 29 Mar 2004, Marc G. Fournier wrote:

> On Mon, 29 Mar 2004, Dave Page wrote:
> 
> > It's rumoured that Euler Taveira de Oliveira once said:
> > > Hi Christopher,
> > >
> > >> > "The \l command should only list databases that the current user is
> > >> > authorized for, the \du command should only list users authorized
> > >> > for the current database (and perhaps only superusers should get
> > >> > even that much information), etc.  Perhaps it is possible to set PG
> > >> > to do this, but that should probably be the default."
> > >> >
> > > Seem reasonable. Why not prevent normal users to dig on the pg_catalog?
> > > What is the impact of it?
> >
> > Because they can't use tools like pgAdmin or phpPgAdmin unless they can at
> > least read all the catalogs.
> 
> k, but what I'm suggesting shouldn't prevent that, should it?  They should
> only be able to see those resources that they have permissions to see, not
> all of them ... no?

I think an auto-filtering system for \l and other backslash commands as 
needed, makes a lot more sense than trying 
to deny access to the catalogs.  Obscuring them for security reasons is no 
win, really.  Obscuring them so user number 1,000,000 in his own database 
doesn't have to look at user numbers 1 through 999,999 to see his database 
go by.

While I'm not sure I'd build a 1,000,000 user database, somewhere between 
the 80 we currently have at work and a few thousand you'd go nuts if you 
saw a bunch of data that didn't belong to you every time you hit \l.


Mon, 29 Mar 2004 12:57:28 -0700 (MST)
"scott.marlowe" <scott.marlowe@ihs.com>
Re: Increasing security in a shared environment ...
On Mon, 29 Mar 2004, Andrew Dunstan wrote:

> It's that "probably" that niggles a bit. I don't know what usage
> patterns other people have, and since my typical use is exactly *one*
> user other than the owner/dba, and all access is mediated by my
> middleware, none of this affects me. ISTM we need to cater for as broad
> a set of usage patterns as is reasonable.

In my case, I have a dozen clients running OpenACS, all sharing the
postmaster instance ... now, at the pg_hba.conf level, the database is
restrict to userid @ IP ... so, I'm generally not too concerned about
ClientA being able to access ClientBs database ... but "just in case" an
admin somehow makes a mistake with the pg_hba.conf file, not being to find
out about other databases in the system would be nice ...

> What is not clear to me is how we would even decide which databases to
> hide, if it is not an all or nothing deal. Marc's phrase "those
> resources that they have permissions to see" doesn't define it nearly
> nicely enough. Say I block access to db foo to all users but bar via
> pg_hba.conf. Would we then want to prevent all other users from seeing
> foo in the list of databases? Things like that are why I started
> exploring a somewhat broader approach.

by default, pgsql superuse would see everything

usera, when doing a \l, would only see those databases that are owned by
usera ... maybe have some sort of GRANT ALL ON database so that userb
would see it listed to.

userc, altho not owner of any database, would ahve grant access to usera's
database, and see only that one ...

inside of usera's database, even though userc had access to the database,
a 'GRANT REVOKE' on a specific table would result in that table not being
seen in a \d listing ...

As to 'SELECT * FROM pg_database;' or 'SELECT * FROM pg_class' ... similar
to pg_shadow ... move it to a different name and have a VIEW on the
appropriate system tables that auto-adds something to the effect that the
list is restricted to those with access to those tables ...

----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664

Mon, 29 Mar 2004 18:45:15 -0400 (AST)
"Marc G. Fournier" <scrappy@postgresql.org>
Re: Increasing security in a shared environment ...