Hi,

Goal: I am trying to offer a client of mine a full database export
download for their database (scheme and data).

The environment: PHP5/Postgres8.1.17/Apache2
The place where I want to offer this download is secured: a PHP page
which is only accessible after a succesful login (enforced via $_SESSION).

I can execute shell commands in PHP and catch the output.
Right now I am trying something along these lines:

/usr/bin/pg_dump -h localhost -p 5432 -D -O -x -U someuser somedb

The problem is that pg_dump doesn't want a password on the commandline.
(The reason why the password is not welcome on the commandline is clear:
If it did accept a password it would be visible for all who can ps on
the same machine.)

But how should I circumvent this restriction in a smart/safe way?
I read the advise in the postgresfora to use .pgpass instead.

If I understand it right I should create a .pgpass file in the
userdirectory (with 600 permissions).
Format should be: hostnameort:database:usernameassword
(http://www.postgresql.org/docs/curre...pq-pgpass.html)

Now to my problem/confusion:
The user is www-data in my case (that is Apache running PHP).
That is the same user that runs all other websites on the same machine.
Besides the fact www-data doesn't have a homedirectory, it would be an
unsafe solution to put my db-credentials in there because all other
websites could access the same database, without knowing the password(!).

So in my opinion that solution would be more unsafe than allowing a
password as an argument to pg_dump, because the latter would only be
visible for a short time, and using a .pgpass would open up my database
for all, all the time.

I must be missing something important here.
Can anybody clarify it a little more for me?

Is my above analysis correct and how should I solve this problem?

Thanks for your time!

Regards,
Erwin Moller


--
"There are two ways of constructing a software design: One way is to
make it so simple that there are obviously no deficiencies, and the
other way is to make it so complicated that there are no obvious
deficiencies. The first method is far more difficult."
-- C.A.R. Hoare

On 04/23/2010 01:06 PM, Erwin Moller wrote:


Would it be feasible to modify pg_dump to read a password from a
specified file? Ldapsearch and other programs do it.

That way you could write the password to a pipe from your application,
and feed its output to pg_dump -y /dev/fd/0

Marco Mariani schreef:
Hi Marco,

Thanks for your suggestion.
That would surely be better than sharing the password with the rest of
the users on the machine. :-)

But I never heard of that -y option, nor does my 'man pg_dump' know it.
Possible I misunderstand your suggestion.

I posted my question in a different form to comp.lang.php, and Captain
Paralytic had an easy solution:

(From PHP)
$command = "/usr/lib/postgresql/8.1/bin/pg_dump -h localhost -p 5432 -D
-O -x -U someuser somedbdb < /path/to/dbpassword.txt";
$handle = popen($command, 'r');
... read the stream...

But I like the part of your suggestion that writes the password to a
pipe. It is safer than having it open for www-data to read.

Now I only have make a pipe of that file and I am ready to go. :-)

Thanks.

Regards,
Erwin Moller


--
"There are two ways of constructing a software design: One way is to
make it so simple that there are obviously no deficiencies, and the
other way is to make it so complicated that there are no obvious
deficiencies. The first method is far more difficult."
-- C.A.R. Hoare

On 04/23/2010 04:09 PM, Erwin Moller wrote:

Yes, I was mildly proposing to add the option. It's not there.

Marco Mariani schreef:
Well, to be honest, I simply don't have the balls to expand the default
pg_dump on my production environment. ;-)

By the way: I just told a co-worker of mine about the problem and he
said that I solved that problem years ago in another project. (I totally
forgot. I clearly need a memory upgrade.)

An alternative solution is this command:

$command = "export PGPASSWORD=theneededpassword
/usr/lib/postgresql/8.1/bin/pg_dump -D -O -x -c -h localhost -p 5433 -U
someuser somedatabase";

I like that solution even better.
Simple and clean.
I bet somebody in here gave me that solution a few years ago. ;-)

Thanks for your help.

Regards,
Erwin Moller



--
"There are two ways of constructing a software design: One way is to
make it so simple that there are obviously no deficiencies, and the
other way is to make it so complicated that there are no obvious
deficiencies. The first method is far more difficult."
-- C.A.R. Hoare

Erwin Moller schreef:
The above line was scrambled. Here is a working one:
$command = "export
PGPASSWORD=theneededpassword\n/usr/lib/postgresql/8.1/bin/pg_dump -D -O
-x -c -h localhost -p 5433 -U someuser somedatabase";

All on one line.

Regards,
Erwin Moller



--
"There are two ways of constructing a software design: One way is to
make it so simple that there are obviously no deficiencies, and the
other way is to make it so complicated that there are no obvious
deficiencies. The first method is far more difficult."
-- C.A.R. Hoare

On Fri, 23 Apr 2010 13:06:28 +0200, Erwin Moller wrote:

Yup. Dumping database through the web interface is not a really good
idea. For some things, CLI the king. For everything else, there is a
Mastercard(TM).



--
http://mgogala.byethost5.com

Mladen Gogala schreef:

Hi,

Well, personally I think that is for the developer to decide.
For example: I don't want my clients to have shell access. That would
give me more security headaches than offering a backup via a (secured)
webinterface.
If they insist in backing up their database at regular intervals
themselfs, I want to offer that to them, even tough I make daily backups.

And I don't use Mastercard. ;-)

Regards,
Erwin Moller




--
"There are two ways of constructing a software design: One way is to
make it so simple that there are obviously no deficiencies, and the
other way is to make it so complicated that there are no obvious
deficiencies. The first method is far more difficult."
-- C.A.R. Hoare

On Sun, 25 Apr 2010 17:24:30 +0200, Erwin Moller wrote:

Actually, no it is not for the developer to decide. Database backup is a
sensitive operation which should not be a part of the application.

Neither do I. Why would your clients have to do database backup? Database
backup is a part of the DBA job. I am an Oracle DBA working with Oracle
since 1989, lately into Postgres, and have never offered such capability.
In the world of commercial database applications, backup is offered only
as a DBA tool. Clients don't do backups, period. DBA does backups.
Second, pg_dump is not a good backup tool. It offers a logically
consistent snapshot of your data, but it doesn't allow you to do
recovery. There is a strong possibility of data loss.
Do you allow your users to have their database schemas? You are probably
an ISP, offering hosting? If your users are using Postgres, as they
should because it's a great database, they can do it from their own
client tools:

pg_dump -U mgogala -W -h lpo-postgres-01 scott >/tmp/scott.sql
Password:

It works like a charm. I needed -W because I am a trusted user on this
host, being a DBA, so it wouldn't have asked me for my password without
"-W". You get some perks from being able to edit pg_hba.conf.
The bottom line is this: if the client is using Postgres, he or she
should be proficient with psql and pg_dump. No need for web dump.

If they need to backup their application data, which is, by the way, not
instilling confidence, you could always offer to dump them into CSV
format.





--
http://mgogala.byethost5.com

Mladen Gogala schreef:

Hi Mladen Gogala,

(Sorry for the lengthy response, but I don't agree to a lot of things
you say and want to make clear how and why that is.)

That is just your opinion, not a fact.
Why shouldn't a database backup NOT be a part of an application?
I agree that in most cases it is nonsense, but not always. (Read on)

There can be many reasons.
To name a few:
1) Client feels more secure when they have their own copy.
2) They want more backups than are currently configured by me (daily).
3) They want to try something really wild, and want a backup before
doing that.

Possibly a lot more reasons.

I don't know. I don't care.

I built their application, they paid for it. They own it (I always give
intelectual property to my clients.)
Bottomline: If the owner of the software I make for them want it, they
will get it.

If I think it is a really stupid request I will of course tell them, but
in this case I don't think it is stupid.


Here is the problem: We are not talking some fortune 500 company here
with dedicated DBA's.
This is a small company that needed a highly specific solution. I built
that for them. There is nowhere a DBA to be found.
I am the one closest to that in this situation, and my Postgres backups
are configured and running fine, but 'only' on a daily basis.


Well, you make it sound like that is carved in stone. It isn't.
Don't get me wrong: Of course a dedicated DBA is prefered over the
solution I made, but a dedicated DBA is overkill (= too expensive).
So we settle for: daily backups + the option to create a backup on the
fly if the client sees some reason for that.


'strong possibility of data loss'?
This is what Postgres website says about pg_dump:

source: http://www.postgresql.org/docs/8.3/s...pp-pgdump.html
---------------------------
Description

pg_dump is a utility for backing up a PostgreSQL database. It makes
consistent backups even if the database is being used concurrently.
pg_dump does not block other users accessing the database (readers or
writers).

Dumps can be output in script or archive file formats. Script dumps are
plain-text files containing the SQL commands required to reconstruct the
database to the state it was in at the time it was saved. To restore
from such a script, feed it to psql. Script files can be used to
reconstruct the database even on other machines and other architectures;
with some modifications even on other SQL database products.
---------------------------

And a little futher down:
--------------------------------
Notes

If your database cluster has any local additions to the template1
database, be careful to restore the output of pg_dump into a truly empty
database; otherwise you are likely to get errors due to duplicate
definitions of the added objects. To make an empty database without any
local additions, copy from template0 not template1, for example:

CREATE DATABASE foo WITH TEMPLATE template0;

pg_dump has a limitation; when a data-only dump is chosen and the option
--disable-triggers is used, pg_dump emits commands to disable triggers
on user tables before inserting the data and commands to re-enable them
after the data has been inserted. If the restore is stopped in the
middle, the system catalogs might be left in the wrong state.

Members of tar archives are limited to a size less than 8 GB. (This is
an inherent limitation of the tar file format.) Therefore this format
cannot be used if the textual representation of any one table exceeds
that size. The total size of a tar archive and any of the other output
formats is not limited, except possibly by the operating system.

The dump file produced by pg_dump does not contain the statistics used
by the optimizer to make query planning decisions. Therefore, it is wise
to run ANALYZE after restoring from a dump file to ensure good
performance. The dump file also does not contain any ALTER DATABASE ...
SET commands; these settings are dumped by pg_dumpall, along with
database users and other installation-wide settings.

Because pg_dump is used to transfer data to newer versions of
PostgreSQL, the output of pg_dump can be loaded into newer PostgreSQL
databases. It also can read older PostgreSQL databases. However, it
usually cannot read newer PostgreSQL databases or produce dump output
that can be loaded into older database versions. To do this, manual
editing of the dump file might be required.
---------------------------------

I can live with these limitations.
More importantly: If I receive the output of pg_dump I can restore the
database to that state.

Where is this 'strong possibility of data loss'?
I have used pg_dump on many many occasions and never encountered any
dataloss.

Am I missing something important here?


No. They are laymen. I designed the database and created all interaction
with the database. They don't even have the password, nor do they know
how how I named their database (unless they look into the pg_dump output
I just created for them.).

Yes and no. We am not selling hosting actively, but we have our own
dedicated servers on which we run application for clients.

Main reason for this is that we can control the environment in great
detail and keep things safe.
We had our share of frustrations with hosting providers, so we decided a
long time ago to do our own hosting.
And most big ISP don't offer Postgres at all.

My client doesn't understand that, nor do they need to.
And they need shell access for that, don't they?
I don't offer that to them. Even if I did they wouldn't know how to use it.

In short: the reason I offer this db backup via a webinterface is for
the following scenario:
1) Client wants to try something wild with the app and is afraid he/she
will screw up important stuff.
2) They download a snapshot (that runs via pg_dump behing the scenes)
3) They try their wild things.
4a) If they are happy, no futher actions
4b) If they indeed screwed up (for example: deleted important stuff)
they can ask me to roll back to the excact state the app was in before
they started.

I don't see anything bad in that approach.
(Of course, they could ask me every time to do the backup, but that
would cost them. Now they can play around in the knowledge I am only
needed if they need to restore.)



Well, client doesn't know how to tell SQL appart from JavaScript. ;-)
And they don't have a bashshell or anything. I don't offer that to them.


CSV instead of SQL?
What difference would that make?
Sounds like more work for me, so maybe it is a good idea. ;-)

The way I see it:
- It is THEIR data. Not mine. I am not going to keep them away from
their own data.
- If the pg_dump output makes them happy because they can experiment
more and have the knowledge I can restore it: fine. No harm done here.
- I don't see any advantage in CSV or XML over the SQL export. They
won't understand either format.


As always: feel free to completely disagree. :-)
I am curious what you have to say (honestly) and why you think the way
you do.
But arguments like 'Database backup is a sensitive operation which
should not be a part of the application.' are simply not always true.
That is too dogmatic.
If you think I have it all backwards, please correct me. I am always
willing to learn!

Regards,
Erwin Moller


--
"There are two ways of constructing a software design: One way is to
make it so simple that there are obviously no deficiencies, and the
other way is to make it so complicated that there are no obvious
deficiencies. The first method is far more difficult."
-- C.A.R. Hoare