Обсуждение: How to deny remote backups ?
Hello, First of all sorry about my bad command in english. I have an Internet server with postgres 7.4.7, planned to give remote db services. I have several offices, with client programs that connect to the internet DB server vía a SSH tunnel. The user DB that connects to the remote DB has insert and delete permissions (he is the owner of the DB). The question is ... how can I block or forbide the remote DB duplication ?. I think that a remote person that knows the user DB and his password, can connect to the DB and make a pg_dump of the DB. Is there a method (with SQL user permitions, templates, newest version of postgres, ...) that forbides the remote dump of database or its structure ? I want , for example, only the postgres user can make a local backup inside the server. Thanks.
On Thu, Mar 03, 2005 at 09:45:47 +0100, juanmime@ono.com wrote: > > The user DB that connects to the remote DB has insert and delete permissions > (he is the owner of the DB). > > The question is ... how can I block or forbide the remote DB duplication > ?. I think that a remote person that knows the user DB and his password, > can connect to the DB and make a pg_dump of the DB. Only by not letting the user SELECT the data. If they can see it, they can copy it. If the users really only need INSERT and DELETE access, you can do this.
>>>> >>>> The user DB that connects to the remote DB has insert and delete permissions >>>> (he is the owner of the DB). >>>> >>>> The question is ... how can I block or forbide the remote DB duplication >>>> ?. I think that a remote person that knows the user DB and his password, >>>> can connect to the DB and make a pg_dump of the DB. >> Only by not letting the user SELECT the data. If they can see it, they can >> copy it. If the users really only need INSERT and DELETE access, you can do >> this. OK. but I think that an aproximation to the solution or a half solution could be deny the user consulting the system database catalog or (pg_tables). In that case, the user is not able to know the name of the tables inside the db, and therefore he does not know which are the tables to dump. I think also, that pg_dump could fail at this scenario. 1. What do you think about this ? 2. Is it possible (deny the access to the system catalog or tables (like pg_tables) to a certain user ? Thank you very much.
On Fri, Mar 04, 2005 at 05:33:02PM +0100, juanmime@ono.com wrote: > OK. but I think that an aproximation to the solution or a half solution could > be deny the user consulting the system database catalog or (pg_tables). In > that case, the user is not able to know the name of the tables inside the > db, and therefore he does not know which are the tables to dump. I think > also, that pg_dump could fail at this scenario. Users presumably already know about the tables they're allowed to SELECT from -- otherwise how would they know to query them? So in that case, protecting the system catalogs wouldn't help. And for the tables that users don't know about but could discover through the system catalogs, if the users don't have SELECT privileges then they won't be able to dump those tables anyway. What problem are you really trying to solve? If you want to hide the fact that certain tables exist, then maybe you could put them in a separate database and use a mechanism involving dblink with views and/or functions to control access to them. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
On Fri, Mar 04, 2005 at 17:33:02 +0100, juanmime@ono.com wrote: > > OK. but I think that an aproximation to the solution or a half solution could > be deny the user consulting the system database catalog or (pg_tables). In > that case, the user is not able to know the name of the tables inside the > db, and therefore he does not know which are the tables to dump. I think > also, that pg_dump could fail at this scenario. > 1. What do you think about this ? I don't think it is a good idea. Since you are worried about this, I am going to guess that the users are running a client (such as psql) to do their inserts and deletes. If so, they are going to be able to find out the table names. If this isn't the case and everything is being done by an application running on the server, then you should be having that application control access. > 2. Is it possible (deny the access to the system catalog or tables (like > pg_tables) to a certain user ? You will break things if you prevent people from reading the system catalogs.