pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedUsing psql, pg_dump, pg_restore in a script
3+ messages / 3 participants
[nested] [flat]
* Using psql, pg_dump, pg_restore in a script
@ 2014-06-16 18:53 John Scalia <jayknowsunix@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: John Scalia @ 2014-06-16 18:53 UTC (permalink / raw)
To: pgsql-admin
Hi all,
My latest task requires me to script extracting the latest data from a partitioned table and put it into another database on a different machine on an hourly basis. To do this, the
script uses a psql command to determine which partition to grab, a pg_dump to get it, and pg_restore to put into the other Db. My issue is that I'd like to make this script very
portable but it seems that whenever you pass a "-h <somehost> -U <someuser>" to these commands, they will ask for a password whether "someuser" actually has a password or not. I've
currently set a password for the user the script uses and I'm using .pgpass to make this work, but was there some reason that an option was not given to enter a password as an
argument to the command? Maybe something like --password <somepassword>? I know this could potentially be a security problem, but so is having a password in the .pgpass file in
clear text.
--
Jay
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Using psql, pg_dump, pg_restore in a script
@ 2014-06-16 19:49 hubert depesz lubaczewski <depesz@gmail.com>
parent: John Scalia <jayknowsunix@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: hubert depesz lubaczewski @ 2014-06-16 19:49 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: pgsql-admin
On Mon, Jun 16, 2014 at 8:53 PM, John Scalia <jayknowsunix@gmail.com> wrote:
> My latest task requires me to script extracting the latest data from a
> partitioned table and put it into another database on a different machine
> on an hourly basis. To do this, the script uses a psql command to determine
> which partition to grab, a pg_dump to get it, and pg_restore to put into
> the other Db. My issue is that I'd like to make this script very portable
> but it seems that whenever you pass a "-h <somehost> -U <someuser>" to
> these commands, they will ask for a password whether "someuser" actually
> has a password or not. I've currently set a password for the user the
> script uses and I'm using .pgpass to make this work, but was there some
> reason that an option was not given to enter a password as an argument to
> the command? Maybe something like --password <somepassword>? I know this
> could potentially be a security problem, but so is having a password in the
> .pgpass file in clear text.
>
On some operating system you can see arguments to program currently running
- for example, on linux, you see:
postgres 18804 0.0 0.2 663664 37676 ? S Jun14 0:08
\_ /usr/lib/postgresql/9.3/bin/postgres -D /var/lib/postgresql/9.3/main -c
config_file=/etc/postgresql/9.3/main/postgresql.conf
Giving password as command line option would show it to everybody.
On the other hand - plaintext file should have privileges set so that
noone, except for owner of the account from which you're running
psql/pg_dump/pg_restore, can read it.
If you prefer, you can set PGPASSWORD environment variable - it will also
be used by all 3 of these programs (and most other).
depesz
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Using psql, pg_dump, pg_restore in a script
@ 2014-06-16 23:33 Tom Lane <tgl@sss.pgh.pa.us>
parent: hubert depesz lubaczewski <depesz@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2014-06-16 23:33 UTC (permalink / raw)
To: hubert depesz lubaczewski <depesz@gmail.com>; +Cc: John Scalia <jayknowsunix@gmail.com>; pgsql-admin
hubert depesz lubaczewski <depesz@gmail.com> writes:
> Giving password as command line option would show it to everybody.
Exactly.
> On the other hand - plaintext file should have privileges set so that
> noone, except for owner of the account from which you're running
> psql/pg_dump/pg_restore, can read it.
> If you prefer, you can set PGPASSWORD environment variable - it will also
> be used by all 3 of these programs (and most other).
Note that environment variables are also unsafe on some platforms, though
(I think) not all. The .pgpass solution is actually quite the safest one.
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2014-06-16 23:33 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-06-16 18:53 Using psql, pg_dump, pg_restore in a script John Scalia <jayknowsunix@gmail.com>
2014-06-16 19:49 ` hubert depesz lubaczewski <depesz@gmail.com>
2014-06-16 23:33 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox