Thread: Automated database backups and authentication
Hello, I would like to backup a set of databases from a cron job. It looks like pg_dumpall will do the trick. The only problem is that there doesn't seem to be any way to pass the database password to pg_dumpall in a non interactive manner. I'd like to use some kind of access control on the databases, but it would need to be able to function non interactively. Ideally whatever I do would be able to function across a network, so that one database server could backup to a second - kind of a poor man's replication. I haven't found any real answers in any of the archives, or docs. Has anyone here done something like this or have any thoughts? Thanks, Darren McClelland
On Tue, Aug 06, 2002 at 11:14:47AM -0700, Darren McClelland wrote:
> I haven't found any real answers in any of the archives, or docs. Has anyone
> here done something like this or have any thoughts?
If you control and trust both servers, why not use ident
authentication?
A
--
----
Andrew Sullivan 87 Mowat Avenue
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M6K 3E3
+1 416 646 3304 x110
Thanks, that's an idea. I'd always been thinking of ident as unreliable, but if I control the authenticating server then it's something usable. At least I can force an attacker to get root before they can go after the other machine. That's about the best anyone can hope for. Darren On Tuesday 06 August 2002 11:30, Andrew Sullivan wrote: > On Tue, Aug 06, 2002 at 11:14:47AM -0700, Darren McClelland wrote: > > I haven't found any real answers in any of the archives, or docs. Has > > anyone here done something like this or have any thoughts? > > If you control and trust both servers, why not use ident > authentication? > > A
On Tue, Aug 06, 2002 at 11:39:03AM -0700, Darren McClelland wrote:
> Thanks, that's an idea. I'd always been thinking of ident as unreliable, but
> if I control the authenticating server then it's something usable. At least I
[cut]
Well, if you want to use ident that way, than you have to trust not
only those two servers, but all hosts in their network segments - do not
forget about ARP poisoning.
I think, that in your setup it would be better to do crypt=-auth
and:
- use PGPASSWORD environment variable, just set it before you execute
pg_dumpall. Go and check pgsql-admin list archives - I remember that lately
PG developers stated that PGPASSWORD variable may not be available in the
next PG release - but in that case there will be other way to give password.
Also check online docs: http://www.postgresql.org/idocs/index.php?libpq-envars.html.
OR:
- you can try to pass password to pg_dumpall through tricks like:
"echo mypass | pg_dumpall .... "
Best regards,
--
--- Artur Pietruk, arturp@plukwa.net
At 02:34 PM 8/6/02 , Artur Pietruk wrote:
>I think, that in your setup it would be better to do crypt=-auth and:
>- use PGPASSWORD environment variable, just set it before you execute
>pg_dumpall.
The problem with that is most UNIX systems are able to display the contents
of the environment of a process. Some make it easier than others (on BSD
systems, it is as easy as a switch on "ps") but most can do it.
That was (I believe) the reason that the variable was going away in 7.3,
because it is a security hole.
-crl
--
Chad R. Larson (CRL22) chad@eldocomp.com
Eldorado Computing, Inc. 602-604-3100
5353 North 16th Street, Suite 400
Phoenix, Arizona 85016-3228
> The problem with that is most UNIX systems are able to display the > contents of the environment of a process. Some make it easier than > others (on BSD systems, it is as easy as a switch on "ps") but most > can do it. > > That was (I believe) the reason that the variable was going away in > 7.3, because it is a security hole. Don't those systems only display the environment of the processes that one owns? That's how it seems on Linux, anyway... John -- John Madden UNIX Systems Engineer Ivy Tech State College jmadden@ivytech.edu
If you have a few users and many databases, and the user that created the
database will be the one to access it, it might work for you to do a query
on pg_database to get a list of databases and their creators, then do a
pg_dump of each database while passing the password (echo <password>
redirection or expect script) for each database depending on the user. (I
something similar in MySQL, though not the password pasing, and it looks
like it can be done in pSQL also).
Alternatively, it seems you might run pg_dumpall as the superuser and after
dumping all the databases, chown the dump files?
Murthy
"Out of the mouths of babes ..."
>-----Original Message-----
>From: Darren McClelland [mailto:darren@zonarsystems.com]
>Sent: Tuesday, August 06, 2002 14:15
>To: pgsql-admin@postgresql.org
>Subject: [ADMIN] Automated database backups and authentication
>
>
>Hello,
>
>I would like to backup a set of databases from a cron job. It
>looks like
>pg_dumpall will do the trick. The only problem is that there
>doesn't seem to
>be any way to pass the database password to pg_dumpall in a
>non interactive
>manner. I'd like to use some kind of access control on the
>databases, but it
>would need to be able to function non interactively.
>
>Ideally whatever I do would be able to function across a
>network, so that one
>database server could backup to a second - kind of a poor
>man's replication.
>
>I haven't found any real answers in any of the archives, or
>docs. Has anyone
>here done something like this or have any thoughts?
>
>Thanks,
>Darren McClelland
>
>
>---------------------------(end of
>broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org
>
On Tue, 6 Aug 2002, John Madden wrote: > Don't those systems only display the environment of the processes that one > owns? That's how it seems on Linux, anyway... No, some systems will display the environment of any process. cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC
On Tue, Aug 06, 2002 at 11:34:58PM +0200, Artur Pietruk wrote:
> On Tue, Aug 06, 2002 at 11:39:03AM -0700, Darren McClelland wrote:
> > Thanks, that's an idea. I'd always been thinking of ident as unreliable, but
> > if I control the authenticating server then it's something usable. At least I
> [cut]
>
> Well, if you want to use ident that way, than you have to trust not
> only those two servers, but all hosts in their network segments - do not
> forget about ARP poisoning.
>
> I think, that in your setup it would be better to do crypt=-auth
> and:
>
> - use PGPASSWORD environment variable, just set it before you execute
If you're worried about people poisoning arp, &c., then you'd have to
be mad to put a password in an environment variable.
If you have this sort of security problem, use Kerberos. It's what
it was designed to solve.
A
--
----
Andrew Sullivan 87 Mowat Avenue
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M6K 3E3
+1 416 646 3304 x110
Hi, Thanks to all who responded. Right now I'm partial to using the PGPASSWORD variable. I was so hung up on looking at the applications I forgot about the underlying libraries. I can set the environmental variable in the backup script and make that only readable by root. It looks like someone would need to be root to look at the environment of a process they don't own, at least on Linux, which is what I'm running on. If an attacker has root on the box, it's game over anyway, and this looks safest from a network based attack. Thanks again, Darren McClelland On Tuesday 06 August 2002 11:14, Darren McClelland wrote: > Hello, > > I would like to backup a set of databases from a cron job. It looks like > pg_dumpall will do the trick. The only problem is that there doesn't seem > to be any way to pass the database password to pg_dumpall in a non > interactive manner. I'd like to use some kind of access control on the > databases, but it would need to be able to function non interactively. > > Ideally whatever I do would be able to function across a network, so that > one database server could backup to a second - kind of a poor man's > replication. > > I haven't found any real answers in any of the archives, or docs. Has > anyone here done something like this or have any thoughts? > > Thanks, > Darren McClelland > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
> I would like to backup a set of databases from a cron job. It looks like > pg_dumpall will do the trick. The only problem is that there doesn't seem to > be any way to pass the database password to pg_dumpall in a non interactive > manner. I'd like to use some kind of access control on the databases, but it > would need to be able to function non interactively. > > Ideally whatever I do would be able to function across a network, so that one > database server could backup to a second - kind of a poor man's replication. > > I haven't found any real answers in any of the archives, or docs. Has anyone > here done something like this or have any thoughts? > I just set this up on my system. I use expect to take the backups and transfer the files to another system using scp. I also use some python scripts to figure out what the names of the backup files should be (using the date for instance). Some issues I ran into: * Since the password will be in the expect script, you need to be careful with permissions on the script. * Also, watch your umask for any temporary files you may create in the process. * Expect scripts which run fine from the command line will mysteriously fail from cron (and just hang around not doing much of anything) I found that using absolute paths to *everything* solved most of those problems. * I set the expect log_user variable to 0 so that I do not get any mail about the process. * I used: set env(SHELL) /bin/sh because I was having trouble with figuring out which shell was going to run, and this seemed easiest. I am using the same system to run a daily report too. I had never used cron, and it was tough to get this working, but now it seems pretty good.
On Tue, 6 Aug 2002 23:32:42 +0000 (UTC)
Lee Harr <missive@frontiernet.net> wrote:
> Some issues I ran into:
> [snip]
> * Expect scripts which run fine from the command line will
> mysteriously fail from cron (and just hang around not
> doing much of anything) I found that using absolute
> paths to *everything* solved most of those problems.
> [snip]
> I had never used cron, and it was tough to get this
> working, but now it seems pretty good.
cron is an interesting beast. Because it doesn't source your .profile (or
whatever shell equivalent, .bashrc, .cshrc, .kshrc, whatever), then often
the shell environment of a cron job isn't quite the same as an interactive
session. Usually, in fact, you don't *WANT* it to do most of those
variables. Who cares what your prompt looks like during a cron job,
anyway? But then PATH isn't right, ORACLE_HOME isn't right, or SYBASE
isn't set. Thus, scripts FAIL through cron that worked interactively.
After struggling with this problem for a few years (hey, I'm not bright,
but eventually I get it!), I settled on this:
Once the script is working in an interactive session, I get a list of
environment variables ("env" or "set" -- usually one of those will spew
forth every variable you've got set) then, instead of
00 04 * * * $HOME/dir/script.sh
I do
00 04 * * * $HOME/dir/wrapper.sh script.sh
wrapper.sh is just a shellscript that #1 sets up the environment as it was
during the interactive shell run, takes a single argument (that is, the
script to run). So wrapper.sh looks something like:
-----------------------------------------------------------------
#!/bin/bash
export ENV1=/usr/bin
export PATH=/usr/local/bin:/usr/bin:/bin:/usr/sbin
export PGHOME=/usr/local/pgsql
$1 > /var/log/databasescripts/$1.log
-----------------------------------------------------------------
The "export XYZ=abc" bit are the lines from the "set" or "env" command
that I deemed necessary. PATH, for me, is always one of those.
With a little experimentation, this method should make setting up cron
jobs a *LOT* easier for you. These days, my cron jobs work on the first
time with no errors about 95% of the time, and I'd say that's completely
because of this method of putting the ENV variables into a wrapper script.
Just make sure you handpick which environment variables to stick into that
script. You don't need/want PWD or _ or SHLVL or anything, but LC_* and
PATH and OSTYPE and HOSTNAME might actually be useful. You will get pretty
good at determining what you need and what you don't after a few
iterations.
--
Tim Ellis
Senior Database Architect
Gamet, Inc.
ps -- Once you have this done, you suddenly realise wrapper.sh is a
perfect place to put some log rotation, log analysis, and email
notification.
I've attached doSQLScript.sh for calling SQL scripts in an Oracle-specific
way, and runScript.sh which is for calling shell scripts (again, with an
Oracle bent). Both of these should be pretty easy to repurpose to
Postgres.
Attachment
You can use local/ident if your OS supports it. See pg_hba.conf. --------------------------------------------------------------------------- Darren McClelland wrote: > Thanks, that's an idea. I'd always been thinking of ident as unreliable, but > if I control the authenticating server then it's something usable. At least I > can force an attacker to get root before they can go after the other machine. > That's about the best anyone can hope for. > Darren > > > > On Tuesday 06 August 2002 11:30, Andrew Sullivan wrote: > > On Tue, Aug 06, 2002 at 11:14:47AM -0700, Darren McClelland wrote: > > > I haven't found any real answers in any of the archives, or docs. Has > > > anyone here done something like this or have any thoughts? > > > > If you control and trust both servers, why not use ident > > authentication? > > > > A > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026