Thread: pg_dump
Hello All... I've been doing some work tonight to try and put a class wrapper around pg_dump in c# so that I could create a back up schedule in my code. I have to say that this is one of the first areas of pgsql that I find really lacking. For example, there seems to be lots of "tricks" that people have come up with to work around not being able to pass a password.
Ultimately, what I would like is to have my app server kick off every night at a configurable time and save a backup to a configurable location. Any advice on how to accomplish this and do it without writing the password to disk?
Also, where is the best place to post feature enhancement suggestions for PostgreSQL?
Thanks in advance.
David
On Fri, Aug 21, 2009 at 12:58 AM, David Saracini<dsaracini@yahoo.com> wrote: > Ultimately, what I would like is to have my app server kick off every night > at a configurable time and save a backup to a configurable location. Any > advice on how to accomplish this and do it without writing the password to > disk? Depends on what you mean by "without writing the password to disk" (do you not want the password hardcoded into your source code, do you not want a .pgpass file, do you not want it in a crontab or .bat file -- you'll have to have the password *somewhere*), and on what platform you're on. If you're on Windows, see the backup guide here: http://wiki.postgresql.org/wiki/Automated_Backup_on_Windows This topic has also been discussed in numerous other places, e.g. http://archives.postgresql.org/pgsql-php/2005-04/msg00007.php http://almosteffortless.com/2008/10/29/postgresql-backup-tips-for-n00bz/ - Josh
Josh,
Thank you for the reply... I appreciate it.
I should have noted below that I am on Windows.
Yes; the topic has been discussed several times and in several places. And I think that I have read almost all of it (at least a major portion).
What I would like to avoid is having the password in plain text on the disk (via a batch file, pgpass.conf, etc.). Plus, from the documentation, using a system variable is deprecated and has security issues. (Just a note, I never got this method to work anyway via C# and the Process class).
And yes, I do understand that if I put it in my source code, it is still on the disk. But, that is going to be on a server (harder to get to...) and also, I can keep it in an encrypted resource and just unencrypt in memory when I need it.
BTW, I do get that this is not totally secure either. Any security measure can be broken once the user has the exe/dll and control of the box that code is running on. But, IMHO, it would certainly be better than that having it unencrypted on disk - or making my end user try to protect the password via Windows' file/directory security.
I guess what I'm wishing for is that pg_dumb was changed to where the password could be sent via the command line. I've seen this requested multiple times and it's always been just rejected. I'm not sure why. Anyone know? If so, could you reply? It *seems* like a minor change. At least it would be in my code and in C#, but... I don't know about pg_dump/pgsql. It might be much, much more difficult. (???)
So, to recap, what I'm learning is that the best way to do this is to dynamically create a batch file, then exec it and get the exitcode via %errorlevel%. Then *try* to make sure I delete it.
Correct?
Thanks again for the help.
David
From: Josh Kupershmidt <schmiddy@gmail.com>
To: David Saracini <dsaracini@yahoo.com>
Cc: pgsql novice forum <pgsql-novice@postgresql.org>
Sent: Tuesday, August 25, 2009 6:46:27 AM
Subject: Re: [NOVICE] pg_dump
On Fri, Aug 21, 2009 at 12:58 AM, David Saracini<dsaracini@yahoo.com> wrote:
> Ultimately, what I would like is to have my app server kick off every night
> at a configurable time and save a backup to a configurable location. Any
> advice on how to accomplish this and do it without writing the password to
> disk?
Depends on what you mean by "without writing the password to disk" (do you not
want the password hardcoded into your source code, do you not want a
.pgpass file,
do you not want it in a crontab or .bat file -- you'll have to have
the password
*somewhere*), and on what platform you're on. If you're on Windows,
see the backup
guide here:
http://wiki.postgresql.org/wiki/Automated_Backup_on_Windows
This topic has also been discussed in numerous other places, e.g.
http://archives.postgresql.org/pgsql-php/2005-04/msg00007.php
http://almosteffortless.com/2008/10/29/postgresql-backup-tips-for-n00bz/
- Josh
David Saracini <dsaracini@yahoo.com> writes:
> I guess what I'm wishing for is that pg_dumb was changed to where the
> password could be sent via the command line. I've seen this requested
> multiple times and it's always been just rejected. I'm not sure why.
> Anyone know?
Yes: on most platforms, anything exposed on a process command line is
*FAR* more insecure than any of the alternatives you just dismissed out
of hand as not secure enough for you. See "ps" and equivalents.
regards, tom lane
Hmmm... I just Googled "ps command" and did some reading. interesting. I've had very little experience with Linux and was unaware of that command. But, does make total sense now. Thanks for helping me understand.
I'm still not very happy with the alternatives! Guess I will have to live with it! ;)
Thanks Tom...
Dave
From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Saracini <dsaracini@yahoo.com>
Cc: Josh Kupershmidt <schmiddy@gmail.com>; pgsql novice forum <pgsql-novice@postgresql.org>
Sent: Wednesday, August 26, 2009 6:52:37 PM
Subject: Re: [NOVICE] pg_dump
David Saracini <dsaracini@yahoo.com> writes:
> I guess what I'm wishing for is that pg_dumb was changed to where the
> password could be sent via the command line. I've seen this requested
> multiple times and it's always been just rejected. I'm not sure why.
> Anyone know?
Yes: on most platforms, anything exposed on a process command line is
*FAR* more insecure than any of the alternatives you just dismissed out
of hand as not secure enough for you. See "ps" and equivalents.
regards, tom lane