Thread: Help automate pg_dump

We just upgraded from 7.1.3 to 7.2.1 and are at a loss on how to
script the pg_dump routine. We use this tool for backup, but can not
get around the requirement for username/password to be interactive.

We require login from the localhost, and can not revert to trusted
connections.

If the username/password must be interactive, how can I script backup
of the server?

Evan,

Hmmm.

Please let the list know if you get a solution, I am about to go to 7.2 on
my production machines but that could be a show stopper if the cron job
cannot automatically back it up anymore.

Terry Fielder
Network Engineer
Great Gulf Homes / Ashton Woods Homes
terry@greatgulfhomes.com

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of
> nothanks@nowhere.com
> Sent: Monday, June 17, 2002 10:01 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Help automate pg_dump
>
>
> We just upgraded from 7.1.3 to 7.2.1 and are at a loss on how to
> script the pg_dump routine. We use this tool for backup, but can not
> get around the requirement for username/password to be interactive.
>
> We require login from the localhost, and can not revert to trusted
> connections.
>
> If the username/password must be interactive, how can I script backup
> of the server?
>
> Evan,
>
> ---------------------------(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
>

Your best bet is probably some combination of the following:

run your backups from a different machine, setting that ip up with a
trusted connection in pg_hba.conf. (You might be able to do this locally
and force connection over a tcpip socket if you can be sure your users
can only use unix sockets, but it would be better to use a separate
machine) (see
http://www.postgresql.org/idocs/index.php?client-authentication.html#PG-HBA-CONF)

try using the ident authentication type in pg_hba.conf, which (iirc)
verifies the attempted postgres username as matching the unix user name
(unless your giving everyone access to the postgres unix user, which is
a bad idea(tm). (see
http://www.postgresql.org/idocs/index.php?auth-methods.html#AEN16445)

set the environment variable PGPASSWORD at the shell level. psql should
use this variable for any prompting done by the database, but be
forewarned that this method has its own security issues. (see
http://www.postgresql.org/idocs/index.php?libpq-envars.html)

create a "backups" user that only has read rights to the database and
use that user for the sole purpose of backups.
(http://www.postgresql.org/idocs/index.php?user-manag.html)

hope this helps,
Robert Treat

On Mon, 2002-06-17 at 22:01, nothanks@nowhere.com wrote:
> We just upgraded from 7.1.3 to 7.2.1 and are at a loss on how to
> script the pg_dump routine. We use this tool for backup, but can not
> get around the requirement for username/password to be interactive.
>
> We require login from the localhost, and can not revert to trusted
> connections.
>
> If the username/password must be interactive, how can I script backup
> of the server?
>
> Evan,
>
> ---------------------------(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

Hello, Evan,

We use RedHat 7.2

Do an su to (or login as) postgres and create a cron job that runs your backup script.  When cron runs the job it
assumesthe postgres user ID and runs the job, hence no username or password needed.  Note, it maybe necessary to
re-establishposgtres' rights locally if they have been modified from the original install. 

Good luck.

======= At 2002-06-18, 02:01:00 you wrote: =======

>We just upgraded from 7.1.3 to 7.2.1 and are at a loss on how to
>script the pg_dump routine. We use this tool for backup, but can not
>get around the requirement for username/password to be interactive.
>
>We require login from the localhost, and can not revert to trusted
>connections.
>
>If the username/password must be interactive, how can I script backup
>of the server?
>
>Evan,



Best regards.
William Meloney
bmeloney@mindspring.com
2002-06-18

> Please let the list know if you get a solution, I am about to go to 7.2 on
> my production machines but that could be a show stopper if the cron job
> cannot automatically back it up anymore.

You might try this with expect (customizing necessary):

#!/usr/bin/expect -f
# wrapper to make passwd(1) be non-interactive
# username is passed as 1st arg, passwd as 2nd

set password xxx
spawn /usr/bin/psql -d db
expect "password:"
sleep 1
send "$password\r"
expect "password:"
sleep 1
send "$password\r"
expect eof



Greetings,
Bjoern

Hi all,

Below  is what I have written to the list previously. All you need is to
install expect to your machine, if you do not have it.

****************

1. /usr/sbin/createdbbackups.sh


pg_dump dbname_1 > dbname_1.pgdump -p 5434 -u;
pg_dump dbname_2 > dbname_2.pgdump -p 5434 -u;
...

In this file, you should write the  name of the databases that you want to
get the dump of.

Let's say you have 4 databases to backup.


2. /usr/sbin/dbbackup

#!/usr/bin/expect -f
set env(SHELL) /bin/sh
set env(HOME) /usr/sbin/

spawn /usr/sbin/createdbbackups.sh

expect 'User name':
send postgres\r
expect Password:
send PostgreSQL_Passwd\r

In this file, you'll write the last four lines 4 times (assuming that you
have 4 databases in your system.) Each 4 line must correspond the
databases listed in the first file.

I mean, if dbname_1 belongs to postgres user, that write postgres and its
password on the first line. If dbname_2 belongs to (let's say) surojit
user, then write suroojit and ist password there:

expect 'User name':
send surojit\r
expect Password:
send Passwd_Of_surojit\r


At the very end of this file, add the following:

        spawn /usr/sbin/movedbbackups.sh


3.  /usr/sbin/movedbbackups.sh


mv dbname_1.pgdump /backup/`date '+%d-%m-%Y'`;
mv dbname_2.pgdump /backup/`date '+%d-%m-%Y'`;
...

This is for moving the dumps in a daily directory.

  (TO create the directory, you could use

        cd /backup;
        mkdir `date '+%d-%m-%Y'`

  )

***************************

Regards,
Devrim

On Tue, 18 Jun 2002 terry@greatgulfhomes.com wrote:

> Hmmm.
>
> Please let the list know if you get a solution, I am about to go to 7.2 on
> my production machines but that could be a show stopper if the cron job
> cannot automatically back it up anymore.
>
> Terry Fielder
> Network Engineer
> Great Gulf Homes / Ashton Woods Homes
> terry@greatgulfhomes.com
>
> > -----Original Message-----
> > From: pgsql-general-owner@postgresql.org
> > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of
> > nothanks@nowhere.com
> > Sent: Monday, June 17, 2002 10:01 PM
> > To: pgsql-general@postgresql.org
> > Subject: [GENERAL] Help automate pg_dump
> >
> >
> > We just upgraded from 7.1.3 to 7.2.1 and are at a loss on how to
> > script the pg_dump routine. We use this tool for backup, but can not
> > get around the requirement for username/password to be interactive.
> >
> > We require login from the localhost, and can not revert to trusted
> > connections.
> >
> > If the username/password must be interactive, how can I script backup
> > of the server?
> >
> > Evan,
> >
> > ---------------------------(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
> >
>
>
> ---------------------------(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
>

--

Devrim GUNDUZ

devrim@oper.metu.edu.tr
devrim.gunduz@linux.org.tr

Web : http://devrim.oper.metu.edu.tr
-------------------------------------

terry@greatgulfhomes.com wrote:

> Hmmm.
>
> Please let the list know if you get a solution, I am about to go to 7.2 on
> my production machines but that could be a show stopper if the cron job
> cannot automatically back it up anymore.
>
> Terry Fielder
> Network Engineer
> Great Gulf Homes / Ashton Woods Homes
> terry@greatgulfhomes.com
>
> > -----Original Message-----
> > From: pgsql-general-owner@postgresql.org
> > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of
> > nothanks@nowhere.com
> > Sent: Monday, June 17, 2002 10:01 PM
> > To: pgsql-general@postgresql.org
> > Subject: [GENERAL] Help automate pg_dump
> >
> >
> > We just upgraded from 7.1.3 to 7.2.1 and are at a loss on how to
> > script the pg_dump routine. We use this tool for backup, but can not
> > get around the requirement for username/password to be interactive.
> >
> > We require login from the localhost, and can not revert to trusted
> > connections.
> >
> > If the username/password must be interactive, how can I script backup
> > of the server?
> >
> > Evan,
> >
> >

Could you not use expect--it is designed for that sort of problem.

Don


p.s. Any help on my point-polygon problem (yesterday's postings) would be
appreciated.

On Tue, Jun 18, 2002 at 02:01:20AM +0000, nothanks@nowhere.com wrote:
> We just upgraded from 7.1.3 to 7.2.1 and are at a loss on how to
> script the pg_dump routine. We use this tool for backup, but can not
> get around the requirement for username/password to be interactive.
>
> We require login from the localhost, and can not revert to trusted
> connections.

Could you use ident?  Or ident just for the user you want to use for
backups?

A

--
----
Andrew Sullivan                               87 Mowat Avenue
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110

in the crontab of postgres user you could use
0 6  * * * ( cd /appl_logs/postgresql/ && /opt/pgsql/pg_backup.sh -db
dbname )

and pg_backup.sh could be like this
#!/bin/bash
#
# pg_backup.sh
#
# Database backup
#
# Parametres :
#       -db <db name>
#
# a file (with the following format <db name>.<date>-<hour>.dump.gz) will be
created in the current
# directory
#

usage()
{
    echo "`basename $0` -db <db name>"
    exit 1
}

if [ "$1" != "-db" ]; then
    usage
fi

DBNAME="$2"
DATE=`date +%Y%m%d`
TIME=`date +%H%M`
SAV_DB_FILENAME="$DBNAME.$DATE-$TIME.dump.gz"
~postgres/bin/pg_dump $DBNAME | gzip -c9 >$SAV_DB_FILENAME


-----Message d'origine-----
De : pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]De la part de
terry@greatgulfhomes.com
Envoyé : mardi 18 juin 2002 15:24
À : nothanks@nowhere.com; pgsql-general@postgresql.org
Objet : Re: [GENERAL] Help automate pg_dump


Hmmm.

Please let the list know if you get a solution, I am about to go to 7.2 on
my production machines but that could be a show stopper if the cron job
cannot automatically back it up anymore.

Terry Fielder
Network Engineer
Great Gulf Homes / Ashton Woods Homes
terry@greatgulfhomes.com

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of
> nothanks@nowhere.com
> Sent: Monday, June 17, 2002 10:01 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Help automate pg_dump
>
>
> We just upgraded from 7.1.3 to 7.2.1 and are at a loss on how to
> script the pg_dump routine. We use this tool for backup, but can not
> get around the requirement for username/password to be interactive.
>
> We require login from the localhost, and can not revert to trusted
> connections.
>
> If the username/password must be interactive, how can I script backup
> of the server?
>
> Evan,
>
> ---------------------------(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
>


---------------------------(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

>>>>> "n" == nothanks  <nothanks@nowhere.com> writes:

n> If the username/password must be interactive, how can I script backup
n> of the server?

It honors the standard environment variables for user/password, so
just set them.  And make sure your script is not readable by anyone
else, or your security is moot.

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D.                Khera Communications, Inc.
Internet: khera@kciLink.com       Rockville, MD       +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/

On Tue, 2002-06-18 at 18:48, Vivek Khera wrote:
> >>>>> "n" == nothanks  <nothanks@nowhere.com> writes:
>
> n> If the username/password must be interactive, how can I script backup
> n> of the server?
>
> It honors the standard environment variables for user/password, so
> just set them.  And make sure your script is not readable by anyone
> else, or your security is moot.

Actually, if you're using environment variables to preset usernames and
passwords your security is moot.

Try " ps exwww " and see the environment variables of all processes

Cheers,
Tycho

--
Tycho Fruru                            tycho@fruru.com
"Prediction is extremely difficult. Especially about the future."
  - Niels Bohr

On Wed, Jun 19, 2002 at 12:32:29PM +0200, Tycho Fruru wrote:
> On Tue, 2002-06-18 at 18:48, Vivek Khera wrote:
> > >>>>> "n" == nothanks  <nothanks@nowhere.com> writes:
> >
> > n> If the username/password must be interactive, how can I script backup
> > n> of the server?
> >
> > It honors the standard environment variables for user/password, so
> > just set them.  And make sure your script is not readable by anyone
> > else, or your security is moot.
>
> Actually, if you're using environment variables to preset usernames and
> passwords your security is moot.
>
> Try " ps exwww " and see the environment variables of all processes

Hmm, on linux this only shows the environment for your own processes. Does
this work differently on other systems?

$ cat /proc/1/environ
cat: /proc/1/environ: Permission denied

and if you're root, who cares?
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.

On Wed, 2002-06-19 at 12:42, Martijn van Oosterhout wrote:
> On Wed, Jun 19, 2002 at 12:32:29PM +0200, Tycho Fruru wrote:

> > Try " ps exwww " and see the environment variables of all processes
>
> Hmm, on linux this only shows the environment for your own processes. Does
> this work differently on other systems?
>
> $ cat /proc/1/environ
> cat: /proc/1/environ: Permission denied
>
> and if you're root, who cares?

I tried this a long time ago (perhaps too long ago :-) with success.

(checking different versions)
It does the right thing (limit access) at least since 2.2.0
Looking at the source I think that in the 1.2 series it was still a
problem (ie. you could see other user's environments)

I'm growing old too fast.

T.

--
Tycho Fruru                            tycho@fruru.com
"Prediction is extremely difficult. Especially about the future."
  - Niels Bohr

On 18 Jun 2002 12:48:15 -0400, Vivek Khera <khera@kcilink.com> wrote:

>>>>>> "n" == nothanks  <nothanks@nowhere.com> writes:
>
>n> If the username/password must be interactive, how can I script backup
>n> of the server?
>
>It honors the standard environment variables for user/password, so
>just set them.  And make sure your script is not readable by anyone
>else, or your security is moot.

We, that might be the case, and I thank you for your answer, however,
on some platforms, the ps command can be used to view processes and
thier environment variables, making this solution quite insecure.
Perhaps another solution should be investigated by the postgres team.

Thanks again.

Evan