Обсуждение: Increasing security in a shared environment ...
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
> "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
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
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
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
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
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
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
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
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
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.
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