Thread: Re: Views, views, views: Summary of Arguments
Andrew Dunstan wrote: > Tom Lane wrote: > >"Merlin Moncure" <merlin.moncure@rcsonline.com> writes: > >>However, I think PostgreSQL has a fairly serious security problem in > >>that the system catalogs are open to the public. I don't seem to be > >>winning many supporters on this particular point though. > > > >No, you're not, and it's not like we've never heard this argument > >before. > > > >Just upthread there were several complaints about the information_schema > >being too restrictive to be useful --- I think we'd get a whole lot more > >of that if we tried to prevent direct examination of the catalogs. > > There is a case for a facility to "harden" postgres. My experiments some > time ago show you can pretty much hide everything without breaking > anything badly if you're careful. I have it on my personal TODO list to > complete a hardening script - although I have no idea when I'll get to it. :-). I tried it from that angle and could only come up with two modes: 'pgadmin on' and 'pgadmin off' (per user). If you can do better, I'd be thrilled. I also don't want to overblow my own argument...the database can be secured quite effectively if you know what to do. It would just be nice to have a little flexibility. I suppose a hardening script, internal or external to the project is a reasonable way of addressing my security concerns, if not superior. Merlin
Merlin Moncure wrote: >I tried it from that angle and could only come up with two modes: >'pgadmin on' and 'pgadmin off' (per user). If you can do better, I'd be >thrilled. I also don't want to overblow my own argument...the database >can be secured quite effectively if you know what to do. It would just >be nice to have a little flexibility. > >I suppose a hardening script, internal or external to the project is a >reasonable way of addressing my security concerns, if not superior. > > > > My approach was to remove all significant permissions (including on the catalog) from public and regrant them to a pseudopublic group, comprising designated users. The designated users would notice no difference at all, while everyone else would be able to see only what was explicitly granted to them. But there would be lots of testing and thinking to be done before releasing it into the wild :-) cheers andrew
Andrew, Merlin, > My approach was to remove all significant permissions (including on the > catalog) from public and regrant them to a pseudopublic group, > comprising designated users. The designated users would notice no > difference at all, while everyone else would be able to see only what > was explicitly granted to them. But there would be lots of testing and > thinking to be done before releasing it into the wild :-) <plug>Doesn't it seem like a really complete set of system views (based on information_schema or otherwise) would potentially allow securing the pg_catalog?</plug> -- Josh Berkus Aglio Database Solutions San Francisco
Josh Berkus <josh@agliodbs.com> writes:
> Andrew, Merlin,
>> My approach was to remove all significant permissions (including on the
>> catalog) from public and regrant them to a pseudopublic group,
>> comprising designated users. The designated users would notice no
>> difference at all, while everyone else would be able to see only what
>> was explicitly granted to them. But there would be lots of testing and
>> thinking to be done before releasing it into the wild :-)
> <plug>Doesn't it seem like a really complete set of system views (based on
> information_schema or otherwise) would potentially allow securing the
> pg_catalog?</plug>
It'd just move the issues to a different place ... you still have to
test and think ;-)
regards, tom lane
Josh Berkus wrote: >Andrew, Merlin, > > > >>My approach was to remove all significant permissions (including on the >>catalog) from public and regrant them to a pseudopublic group, >>comprising designated users. The designated users would notice no >>difference at all, while everyone else would be able to see only what >>was explicitly granted to them. But there would be lots of testing and >>thinking to be done before releasing it into the wild :-) >> >> > ><plug>Doesn't it seem like a really complete set of system views (based on >information_schema or otherwise) would potentially allow securing the >pg_catalog?</plug> > > > Not really, no. It would just be one more thing that my hardening script had to remove permissions from. I still have an open mind about the sysviews project, but the more oversold, hyped and promoted with bogus arguments it gets the more skeptical I become. cheers andrew
Andrew, > Not really, no. It would just be one more thing that my hardening script > had to remove permissions from. Hmmm ... even though the sysviews check users' permissions? That was one of our ideas behind making it "safer than the system catalogs". > I still have an open mind about the sysviews project, but the more > oversold, hyped and promoted with bogus arguments it gets the more > skeptical I become. Geez, who poured Drano on your breakfast cereal? Lighten up. ;-) -- --Josh Josh Berkus Aglio Database Solutions San Francisco
Josh Berkus wrote: >Andrew, > > > >>Not really, no. It would just be one more thing that my hardening script >>had to remove permissions from. >> >> > >Hmmm ... even though the sysviews check users' permissions? That was one of >our ideas behind making it "safer than the system catalogs". > > It might be safer, but that doesn't hit my target at all. I am aiming at a zero-knowledge user, i.e. one who cannot discover anything at all about the db. The idea is that even if subvert can subvert a client and get access to the db the amount of metadata they can discover is as close to zero as possible. cheers andrew
Andrew, > It might be safer, but that doesn't hit my target at all. I am aiming at > a zero-knowledge user, i.e. one who cannot discover anything at all > about the db. The idea is that even if subvert can subvert a client and > get access to the db the amount of metadata they can discover is as > close to zero as possible. Yeah, I can see that. I've personally had this concern about our PG installation on the web server, and as you know about pgFoundry as well, especially since GForge does not use good user security. However, I see 2 seperate cases here: 1) The "ISP" case, where you want to hide all catalog information from the users except the database owner or superuser. 2) The "Enterprise server" setting, where you want to allow catalog access (for example, for pgAdmin) restricted to the current user permissions. -- --Josh Josh Berkus Aglio Database Solutions San Francisco
On 2005-05-13, Andrew Dunstan <andrew@dunslane.net> wrote: > Josh Berkus wrote: >><plug>Doesn't it seem like a really complete set of system views (based on >>information_schema or otherwise) would potentially allow securing the >>pg_catalog?</plug> > > Not really, no. It would just be one more thing that my hardening script > had to remove permissions from. It is specifically intended that you should not have to do that. The precise qualification rules are not yet firmly defined, but if a non-superuser can see anything in the views that does not relate to a permission that was actually granted to them, then it is a bug. (Areas which I expect to need some fine-tuning are: the schema contents view, the rules and triggers views, and possibly some of the constraint info.) > I still have an open mind about the sysviews project, but the more > oversold, hyped and promoted with bogus arguments it gets the more > skeptical I become. I have to say that I find the arguments _against_ it just as bogus. Most significantly, there is a lot of comment on what people _think_ we could do (or not do), and no comment about what we actually _did_. I strongly suggest to anyone thinking of commenting on them that you actually install them and look at them first - while the project is as yet unfinished, and there is a lack of documentation and plenty of rough edges (and quite likely some bugs too), it does actually work and a number of people (some of whom have commented in this thread) have already found it useful. Grab a copy of it from pgfoundry's CVS, go into the sql/ directory and run ./build.sh yourdatabasename (as a superuser, you can add options like -U if needed - the options to build.sh are just passed on to psql). -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services
On 2005-05-13, Josh Berkus <josh@agliodbs.com> wrote: > Andrew, >> It might be safer, but that doesn't hit my target at all. I am aiming at >> a zero-knowledge user, i.e. one who cannot discover anything at all >> about the db. The idea is that even if subvert can subvert a client and >> get access to the db the amount of metadata they can discover is as >> close to zero as possible. > > Yeah, I can see that. I've personally had this concern about our PG > installation on the web server, and as you know about pgFoundry as well, > especially since GForge does not use good user security. > > However, I see 2 seperate cases here: > > 1) The "ISP" case, where you want to hide all catalog information from the > users except the database owner or superuser. I don't believe this is ever feasible in practice, since client interfaces at any level higher than libpq will need to access metadata corresponding to the data they are retrieving. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services
Andrew - Supernews wrote: >> >>1) The "ISP" case, where you want to hide all catalog information from the >>users except the database owner or superuser. >> >> > >I don't believe this is ever feasible in practice, since client interfaces >at any level higher than libpq will need to access metadata corresponding >to the data they are retrieving. > > > In the general case you might well be right. Following a scheme like I have in mind is not something that would be transparent to the application - it will probably impose some serious limits on the app. The little sample application I did for testing did everything by stored procedure. Anyway, as I said, it's a project for the future. cheers andrew
Andrew - Supernews wrote: >Most significantly, there is a lot of comment on what people _think_ >we could do (or not do), and no comment about what we actually _did_. >I strongly suggest to anyone thinking of commenting on them that you >actually install them and look at them first - while the project is as >yet unfinished, and there is a lack of documentation and plenty of >rough edges (and quite likely some bugs too), it does actually work and >a number of people (some of whom have commented in this thread) have >already found it useful. Grab a copy of it from pgfoundry's CVS, go into >the sql/ directory and run ./build.sh yourdatabasename (as a superuser, >you can add options like -U if needed - the options to build.sh are just >passed on to psql). > > > I did look over them. Maybe I'd get the whole thing better if I had a brief description of each view rather that having to infer the purpose for myself from an sql statement of a list of fields. If you're concerned to make a case I think that would be useful. If that's been published and I missed it I apologise. cheers andrew
> I did look over them. Maybe I'd get the whole thing better if I had a > brief description of each view rather that having to infer the purpose > for myself from an sql statement of a list of fields. If you're > concerned to make a case I think that would be useful. If that's been > published and I missed it I apologise. No, you're right, we need that. I'll work on it over the weekend. -- --Josh Josh Berkus Aglio Database Solutions San Francisco
On Sat, 14 May 2005 04:34 am, Andrew Dunstan wrote: > > Andrew - Supernews wrote: > > >> > >>1) The "ISP" case, where you want to hide all catalog information from the > >>users except the database owner or superuser. > >> > >> > > > >I don't believe this is ever feasible in practice, since client interfaces > >at any level higher than libpq will need to access metadata corresponding > >to the data they are retrieving. > > > > > > > > In the general case you might well be right. Following a scheme like I > have in mind is not something that would be transparent to the > application - it will probably impose some serious limits on the app. > The little sample application I did for testing did everything by stored > procedure. Anyway, as I said, it's a project for the future. > From a general user point of view, I do not know the system catalogs very well. I am very unsure of what level of information is available to every user on the system. - Which parts of other databases can be seen by users? - What is the best method to restrict connections to db's people don't have permissions to. - Is there some restrictions you can place on tables people don't have access too. Otherwise they can see all the columns and table info. These are just some of the questions I have, I'm not sure where to get answers, searching the archives may help, but it's definitely not a final answer. Especially since this stuff would be a moving target with each version change of PostgreSQL. Tom mentioned that he had not had these security concerns raised before. From my point of view I just have no idea about the level of information offered to any given user and am scared to run PostgreSQL in an ISP shared environment because of it. I am sure I can secure people from connecting to a db by refusing them access in pg_hba.conf. But I'm unsure of exactly what that buys me, and what is doesn't. A hardening script would be helpful, but some clear information on what is also available to the average user would be good too. I know I should probably step up to do this and don't have time at the moment. I'm sure if I did, I would also miss a great number of things. Regards Russell Smith
On Sat, May 14, 2005 at 12:25:01PM +1000, Russell Smith wrote: > - Which parts of other databases can be seen by users? The name, username of the owner, etc. No table names, for example. The user list is also visible to everyone, across databases. > - What is the best method to restrict connections to db's people don't have > permissions to. pg_hba.conf. Apparently some people run separate postmasters for each, and there are reasons to do that (shared xlogs can be a hassle, for example; if something goes ill in another database, you may be screwed too. Or shared oldest Xid.) > Tom mentioned that he had not had these security concerns raised before. From > my point of view I just have no idea about the level of information offered > to any given user and am scared to run PostgreSQL in an ISP shared > environment because of it. Actually I've seen complaints before. Within a DB you can see the names and columns of all tables, views, etc. Some people want to hide that, and I see the point. In a shared environment you can probably get away with giving separate databases to each, and you have perfect isolation, so no worries there. But if you want to partially share data in a useful manner (say use foreign keys from one "DB" to another) you have to use schemas, and by doing so you are automatically granting visibility to lots of info about your database. (Not the data itself though.) -- Alvaro Herrera (<alvherre[a]surnet.cl>) "Hoy es el primer día del resto de mi vida"
* Russell Smith (mr-russ@pws.com.au) wrote:
> Tom mentioned that he had not had these security concerns raised before. From
> my point of view I just have no idea about the level of information offered
> to any given user and am scared to run PostgreSQL in an ISP shared
> environment because of it. I am sure I can secure people from connecting to
> a db by refusing them access in pg_hba.conf. But I'm unsure of exactly what
> that buys me, and what is doesn't.
It's certainly also a concern of mine that any given use can see every
table in the database. I see that as a definite problem and just
assumed it was already on the radar and something that was planned to be
fixed. It astounds me that the claim is that such security is
impossible.
It bothers me a great deal that I can't control very easily what a given
user can see when they connect over ODBC or via phppgadmin in terms of
schemas, tables and columns. I fixed this in application code in
phppgadmin but that's clearly insufficient since it doesn't do anything
for the other access methods.
I'd certainly really like to see this fixed. It would raise my comfort
level a great deal wrt allowing people to use ODBC and psql directly.
Thanks,
Stephen
>>Tom mentioned that he had not had these security concerns raised before. From >>my point of view I just have no idea about the level of information offered >>to any given user and am scared to run PostgreSQL in an ISP shared >>environment because of it. I am sure I can secure people from connecting to >>a db by refusing them access in pg_hba.conf. But I'm unsure of exactly what >>that buys me, and what is doesn't. > > It's certainly also a concern of mine that any given use can see every > table in the database. I see that as a definite problem and just > assumed it was already on the radar and something that was planned to be > fixed. It astounds me that the claim is that such security is > impossible. > > It bothers me a great deal that I can't control very easily what a given > user can see when they connect over ODBC or via phppgadmin in terms of > schemas, tables and columns. I fixed this in application code in > phppgadmin but that's clearly insufficient since it doesn't do anything > for the other access methods. Modifiying phpPgAdmin is useless - people can query the catalogs manually. Hackers - we get an email about information hiding in shared postgresql/phppgadmin installations at least once a fortnight :) Chris
* Christopher Kings-Lynne (chriskl@familyhealth.com.au) wrote:
> >It bothers me a great deal that I can't control very easily what a given
> >user can see when they connect over ODBC or via phppgadmin in terms of
> >schemas, tables and columns. I fixed this in application code in
> >phppgadmin but that's clearly insufficient since it doesn't do anything
> >for the other access methods.
>
> Modifiying phpPgAdmin is useless - people can query the catalogs manually.
It's not entirely *useless*; it's just not a proper fix for the security
issue, I'll grant you that. Personally I found the hack that I did pretty
useful since most of my users aren't likely to go sniffing through the
catalog and it was a temporary workaround for the complaints until
there's a proper fix.
> Hackers - we get an email about information hiding in shared
> postgresql/phppgadmin installations at least once a fortnight :)
I agree with this- it needs to be dealt with and fixed already, once and
for all.
Thanks,
Stephen
On Sat, May 14, 2005 at 08:55:17AM -0400, Stephen Frost wrote: > * Christopher Kings-Lynne (chriskl@familyhealth.com.au) wrote: > > >It bothers me a great deal that I can't control very easily what a given > > >user can see when they connect over ODBC or via phppgadmin in terms of > > >schemas, tables and columns. I fixed this in application code in > > >phppgadmin but that's clearly insufficient since it doesn't do anything > > >for the other access methods. > > > > Hackers - we get an email about information hiding in shared > > postgresql/phppgadmin installations at least once a fortnight :) > > I agree with this- it needs to be dealt with and fixed already, once and > for all. Given that the newsysviews all base visibility on granted permissions, would they do the job for you? -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
* Jim C. Nasby (decibel@decibel.org) wrote:
> On Sat, May 14, 2005 at 08:55:17AM -0400, Stephen Frost wrote:
> > * Christopher Kings-Lynne (chriskl@familyhealth.com.au) wrote:
> > > Hackers - we get an email about information hiding in shared
> > > postgresql/phppgadmin installations at least once a fortnight :)
> >
> > I agree with this- it needs to be dealt with and fixed already, once and
> > for all.
>
> Given that the newsysviews all base visibility on granted permissions,
> would they do the job for you?
From what I've seen of them, yes, I believe they're exactly what I'm
looking for. Of course, I'd really like to have them in core and have
client applications updated to use them (assuming they need to be
changed, which I'm guessing they would), etc.
Unfortunately it's a bit too late to change what I'm about to put into
production to the newsysviews (not 100% sure they're entirely ready yet
either) but I'll set them up on some of my development machines and play
around with them some more. Here's to hopeing they're in 8.1...
Thanks,
Stephen
On Sat, May 14, 2005 at 10:00:09AM -0400, Stephen Frost wrote: > * Jim C. Nasby (decibel@decibel.org) wrote: > > On Sat, May 14, 2005 at 08:55:17AM -0400, Stephen Frost wrote: > > > * Christopher Kings-Lynne (chriskl@familyhealth.com.au) wrote: > > > > Hackers - we get an email about information hiding in shared > > > > postgresql/phppgadmin installations at least once a fortnight :) > > > > > > I agree with this- it needs to be dealt with and fixed already, once and > > > for all. > > > > Given that the newsysviews all base visibility on granted permissions, > > would they do the job for you? > > From what I've seen of them, yes, I believe they're exactly what I'm > looking for. Of course, I'd really like to have them in core and have As would I. > client applications updated to use them (assuming they need to be > changed, which I'm guessing they would), etc. > > Unfortunately it's a bit too late to change what I'm about to put into > production to the newsysviews (not 100% sure they're entirely ready yet > either) but I'll set them up on some of my development machines and play > around with them some more. Here's to hopeing they're in 8.1... Your feedback would be most welcome. -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"