Обсуждение: doing backups
hi folks, I'm writing out a nightly backup script for a machine. This machine has many databases, running on different ports. ie: 5432,5433 etc.. These servers are ""owned"" (use term loosly) by users on the system, and some users have choses to set passwds. which is 100% within their right. However that makes it a pain for postgres to do pg_dumps. Now if you make pgsql user and postgresql analgous to root and unix, the postgres user shouldn't need a passwd. anyone care to discuss this topic ? Jeff MacDonald jeff@pgsql.com
On Fri, 31 Mar 2000, Jeff MacDonald wrote: > Now if you make pgsql user and postgresql analgous to > root and unix, the postgres user shouldn't need a passwd. My root users always have passwords. *shrug* But you're right, automated backup on password protected databases is next to impossible right now, especially when using pg_dump. If those users want to enable you to do backups for them perhaps they should give you (and only you) some sort of ident controlled access. -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
NO!!!! You don't want to give ANY account root access or root level security! I run my backups FROM root using cron, but root can su to any account that it likes! I have some ksh code tucked away that will be easy to convert to bash that will run commands as a particular user and will check their return codes, etc. In fact, I would be very interested in co-developing a solution provided that it would be OpenSourced as, at the the moment, we have backup script that do pg_dumps from the individual user accts. Another thing to consider is backing up the directories that the database are stored in. For that matter, I am currently thinking a lot about fail-over between more than one server at a database level (yes, that dreaded database replication!!!!). This would seem an essential next step for PostgreSQL and would certainly throw it into a very much bigger league! Bradley Kieser Director Kieser.net Jeff MacDonald wrote: > hi folks, > > I'm writing out a nightly backup script for a machine. > This machine has many databases, running on different ports. > ie: 5432,5433 etc.. > > These servers are ""owned"" (use term loosly) by users on the > system, and some users have choses to set passwds. which is > 100% within their right. > > However that makes it a pain for postgres to do pg_dumps. > > Now if you make pgsql user and postgresql analgous to > root and unix, the postgres user shouldn't need a passwd. > > anyone care to discuss this topic ? > > Jeff MacDonald > jeff@pgsql.com
>> Now if you make pgsql user and postgresql analgous to >> root and unix, the postgres user shouldn't need a passwd. >My root users always have passwords. *shrug* But you're right, automated >backup on password protected databases is next to impossible right now, >especially when using pg_dump. If those users want to enable you to do >backups for them perhaps they should give you (and only you) some sort of >ident controlled access. I missed the first half of this, but, if I have understood correctly, I don't think this is the case: you can do automated backups by doing echo -e 'username\npassword\n' | pg_dump -u databasename Yours, Moray
that's true but that still assumes you know the pasword. also someone mentioned that you can just su to any account, well that's true but it still doesn't negate teh fact that the user has a passwd on thier database. as i said, i think the postgres user should have acess to all databases no matter what. jeff On Mon, 3 Apr 2000, Moray McConnachie wrote: > > >> Now if you make pgsql user and postgresql analgous to > >> root and unix, the postgres user shouldn't need a passwd. > > >My root users always have passwords. *shrug* But you're right, automated > >backup on password protected databases is next to impossible right now, > >especially when using pg_dump. If those users want to enable you to do > >backups for them perhaps they should give you (and only you) some sort of > >ident controlled access. > > I missed the first half of this, but, if I have understood correctly, I > don't think this is the case: you can do automated backups by doing > > echo -e 'username\npassword\n' | pg_dump -u databasename > > > > Yours, > Moray > > > > Jeff MacDonald jeff@pgsql.com
On Mon, 3 Apr 2000, Moray McConnachie wrote: > I missed the first half of this, but, if I have understood correctly, I > don't think this is the case: you can do automated backups by doing > > echo -e 'username\npassword\n' | pg_dump -u databasename The problem is concerning pg_dumpall. You can't assume that all databases are password protected, nor that the same password works for all of them. -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
Jeff MacDonald writes: > i think the postgres user should have acess to all databases no matter > what. I disagree. Firstly, for the backend to actually know that you're the postgres (Unix) user it would have to authenticate you, using ident or kerberos perhaps. But in the case in question the password authentication method was explicitly chosen over the other methods. Secondly, what about postgres users on other systems, everyone can create one on theirs. Or what about systems without users as such. Your request doesn't generalize very well. What it comes down to is that if someone wants you to access their databases (be it for making backups or whatever) they have to grant you access. (There wouldn't be anything preventing them from giving you a password as well, or letting you in via ident, etc., while keeping the password mechanism for themselves.) On a side note, the postgres Unix user and the postgres database user are not really related other than by name. The names must currently be equal for initdb purposes but in the long run this should be removed as well. -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden