Scheduled back up

On 18 Jul 2003 at 15:58, Kallol Nandi wrote:

How to go about scheduled backup in Postgresql.

You need to use cron and pgdump. Man pages for both of them will give you what
you want.

Bye
Shridhar

--
Cohen's Law: There is no bottom to worse.

As i experienced with pg_dump it looks like in ver.7.3 requires interactive
enter password for *custom users*.

The 7.1 will not make this kind of problems.

Still in 7.3 you can make it with cron, but as i know, only with a script
which might look like this

<?php
exec("pg_dump -u [other options] db_name < /path_to/password_file.txt >
the_dumped_file");
?>
where password_file.txt contains:
user_name
user_password

good luck, and if u find that i already said is wrong please notify me
[2 days ago i've send [almost] the same problem and this is the only answer
that i got it]

----- Original Message -----
From: "Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
To: "Pgsql-General" <pgsql-general@postgresql.org>
Sent: Friday, July 18, 2003 1:35 PM
Subject: Re: [GENERAL] Scheduled back up

On 18 Jul 2003 at 15:58, Kallol Nandi wrote:

How to go about scheduled backup in Postgresql.

You need to use cron and pgdump. Man pages for both of them will give you

what

Sincerely i dunno why this happens and after couple of emails received two
days ago I stoped searching why.
In any case I need it to dump for custom users their dbs data from a php
script.
And now it works, but if i gave the same cmd which run through exec the
pg_dump force me to introduce the user_name and password to continue.

I add some of my emails received about this story.

"Viorel Dragomir" <bc@vio.ro> writes:

In 7.3.x the pg_dump accepts the password only from stdin. The user must

en=

ter his password.
It's a feature or a bug, because i can't see any logic in this behaviour.

It's a feature ... or at least an intentional change.

If you want to keep your password in a file, see the ~/.pgpass feature.

regards, tom lane

----------------------------------------------------------------------------
-------

Viorel Dragomir wrote:

In 7.1.2 you can launch this command without any problems:
pg_dump -u -s database < file.txt

where file.txt contains 2 lines:
username
password
~
~
~

And you'll get the dump of the <database>.

In 7.1.3 this command work, the only incovenience is that pg_dump
shows "User name: Password:"
but gets the password from file.

In 7.3.x the pg_dump accepts the password only from stdin. The user
must enter his password.
It's a feature or a bug, because i can't see any logic in this behaviour.

It's a feature :-)
The logic is that it is insecure to keep passwords in files.
If you are not concerned about the security in this case, you can switch
to ident or trust identification. That won't prompt you for the password.

Dima.

----- Original Message -----
From: "Tino Wildenhain" <tino@wildenhain.de>
To: "Viorel Dragomir" <bc@vio.ro>
Cc: <shridhar_daithankar@persistent.co.in>; "Pgsql-General"
<pgsql-general@postgresql.org>
Sent: Friday, July 18, 2003 4:23 PM
Subject: Re: [GENERAL] Scheduled back up

Hi Viorel,

what are the exact circumstances for this? I'm not experiencing
that behavior. Maybe it depends on your settings in pg_hba.conf?
And are you using -X set-session-authorization and so on?

Regards
Tino Wildenhain

Viorel Dragomir wrote:

As i experienced with pg_dump it looks like in ver.7.3 requires

interactive

enter password for *custom users*.

The 7.1 will not make this kind of problems.

Still in 7.3 you can make it with cron, but as i know, only with a

script

which might look like this

<?php
exec("pg_dump -u [other options] db_name <

/path_to/password_file.txt >

the_dumped_file");
?>
where password_file.txt contains:
user_name
user_password

good luck, and if u find that i already said is wrong please notify me
[2 days ago i've send [almost] the same problem and this is the only

answer

that i got it]

----- Original Message -----
From: "Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
To: "Pgsql-General" <pgsql-general@postgresql.org>
Sent: Friday, July 18, 2003 1:35 PM
Subject: Re: [GENERAL] Scheduled back up

On 18 Jul 2003 at 15:58, Kallol Nandi wrote:

How to go about scheduled backup in Postgresql.

You need to use cron and pgdump. Man pages for both of them will give

you

On Fri, Jul 18, 2003 at 01:39:19PM +0300, Viorel Dragomir wrote:

As i experienced with pg_dump it looks like in ver.7.3 requires interactive
enter password for *custom users*.

I don't know what a custom user is, but if you put the password in
~/.pgpass, authentication happens automatically. That's a new
feature in 7.3.x.

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

----- Original Message -----
From: "Andrew Sullivan" <andrew@libertyrms.info>
To: "Pgsql-General" <pgsql-general@postgresql.org>
Sent: Friday, July 18, 2003 2:45 PM
Subject: Re: [GENERAL] Scheduled back up

On Fri, Jul 18, 2003 at 01:39:19PM +0300, Viorel Dragomir wrote:

As i experienced with pg_dump it looks like in ver.7.3 requires

interactive

enter password for *custom users*.

I don't know what a custom user is, but if you put the password in
~/.pgpass, authentication happens automatically. That's a new
feature in 7.3.x.

for user *user_name*
~/.pgpass
will be kind of /home/user_name/.pgpass ?

But I can't do that, the users that are granted to use the database don't
have any user id on that machine,
so no /home/user_name directory either.

I'm sorry for *custom users*.
The project it's kind of cpanel.
A user can create and grant access for his databases.
And thx to pg_dump he can export & import databases.
So any user that have a db might want to export his data through his web
interface.

And I tested pg_dump with his options on command line and I couldn't get the
password from a file, accepted only from stdin.

Thx for infos

vioss

Hi Viorel,

what are the exact circumstances for this? I'm not experiencing
that behavior. Maybe it depends on your settings in pg_hba.conf?
And are you using -X set-session-authorization and so on?

Regards
Tino Wildenhain

Viorel Dragomir wrote:

"Viorel Dragomir" <bc@vio.ro> writes:

I don't know what a custom user is, but if you put the password in
~/.pgpass, authentication happens automatically. That's a new
feature in 7.3.x.

But I can't do that, the users that are granted to use the database don't
have any user id on that machine,
so no /home/user_name directory either.

No, .pgpass is sought in the home directory of the user running pg_dump
(or any other client program). It's not a server-side file.

regards, tom lane

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Viorel Dragomir" <bc@vio.ro>
Cc: "Andrew Sullivan" <andrew@libertyrms.info>; "Pgsql-General"
<pgsql-general@postgresql.org>
Sent: Friday, July 18, 2003 4:41 PM
Subject: Re: [GENERAL] Scheduled back up

"Viorel Dragomir" <bc@vio.ro> writes:

I don't know what a custom user is, but if you put the password in
~/.pgpass, authentication happens automatically. That's a new
feature in 7.3.x.

But I can't do that, the users that are granted to use the database

don't

have any user id on that machine,
so no /home/user_name directory either.

No, .pgpass is sought in the home directory of the user running pg_dump
(or any other client program). It's not a server-side file.

In my case the user is apache.
I dunno for sure but the apache doesn't have a home directory.

I try to find information about pgpass but still i dunno what pgpass should
contain.
a) only one password - of the ~/ user
b) username:password

On Fri, 2003-07-18 at 15:49, Viorel Dragomir wrote:

No, .pgpass is sought in the home directory of the user running pg_dump
(or any other client program). It's not a server-side file.

In my case the user is apache.
I dunno for sure but the apache doesn't have a home directory.

If you have root access to the box, you can fix that by assigning a home
directory to the apache user in the /etc/passwd file. Just make sure the
shell assigned to the apache user is invalid, so nobody can log in as
apache user.

Cheers,
Csaba.

----- Original Message -----
From: "Csaba Nagy" <nagy@ecircle-ag.com>
To: "Viorel Dragomir" <bc@vio.ro>
Cc: "Tom Lane" <tgl@sss.pgh.pa.us>; "Andrew Sullivan"
<andrew@libertyrms.info>; "Pgsql-General" <pgsql-general@postgresql.org>
Sent: Friday, July 18, 2003 5:03 PM
Subject: Re: [GENERAL] Scheduled back up

On Fri, 2003-07-18 at 15:49, Viorel Dragomir wrote:

No, .pgpass is sought in the home directory of the user running

pg_dump

(or any other client program). It's not a server-side file.

In my case the user is apache.
I dunno for sure but the apache doesn't have a home directory.

If you have root access to the box, you can fix that by assigning a home
directory to the apache user in the /etc/passwd file. Just make sure the
shell assigned to the apache user is invalid, so nobody can log in as
apache user.

Thanks for information.
But this is not really a problem because apache doesn't have any grants on
database.
It launches a script that connects to db with a diff username [each username
has a unique password].

So if it has only one password on .pgpass only one user can log in.

On Fri, Jul 18, 2003 at 02:56:53PM +0300, Viorel Dragomir wrote:

I'm sorry for *custom users*.
The project it's kind of cpanel.
A user can create and grant access for his databases.
And thx to pg_dump he can export & import databases.
So any user that have a db might want to export his data through his web
interface.

Hmm. Well, you could use a super-user account to do the dump. It
would have access to everything, and would only need one password.
I'm wondering about security implications of that, however.

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

"Viorel Dragomir" <bc@vio.ro> writes:

But this is not really a problem because apache doesn't have any grants on
database.
It launches a script that connects to db with a diff username [each username
has a unique password].
So if it has only one password on .pgpass only one user can log in.

Not so at all. Read the documentation about .pgpass.
http://www.postgresql.org/docs/view.php?version=7.3&amp;idoc=0&amp;file=libpq-files.html

regards, tom lane

I'm sorry for *custom users*.
The project it's kind of cpanel.
A user can create and grant access for his databases.
And thx to pg_dump he can export & import databases.
So any user that have a db might want to export his data through his web
interface.

Hmm. Well, you could use a super-user account to do the dump. It
would have access to everything, and would only need one password.
I'm wondering about security implications of that, however.

There is a significant difference between 'backups', which are more of
a DBA-level task and thus likely to be under the control of someone
who in a UNIX context has root access or at least DBA shell access, plus
the ability to schedule cron jobs, and 'exports' or 'imports', which
are something that an individual user may wish to do with his/her own
data independent of any database-wide backup schedules.

The existing protocols (pg_dump, etc) are largely geared towards
DBA-level backups, thus they tend to depend upon backend database
access and validation schemes, ie, through .pgpass or pg_hba.conf,
as opposed to frontend tools and user-level privilege grants.
--
Mike Nolan

On Fri, Jul 18, 2003 at 10:28:16AM -0400, Tom Lane wrote:

http://www.postgresql.org/docs/view.php?version=7.3&amp;idoc=0&amp;file=libpq-files.html

Note there's a mistake in that documentation:

achieve this by the command chmod 0600 .pgaccess.

Of couse, chmoding .pgaccess when you're trying to set the
permissions on .pgpass won't help. (The development version has the
mistake fixed.)

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

nolan@celery.tssi.com writes:

There is a significant difference between 'backups', which are more of
a DBA-level task and thus likely to be under the control of someone
who in a UNIX context has root access or at least DBA shell access, plus
the ability to schedule cron jobs, and 'exports' or 'imports', which
are something that an individual user may wish to do with his/her own
data independent of any database-wide backup schedules.

The existing protocols (pg_dump, etc) are largely geared towards
DBA-level backups, thus they tend to depend upon backend database
access and validation schemes, ie, through .pgpass or pg_hba.conf,
as opposed to frontend tools and user-level privilege grants.

What's wrong with \copy in psql?

-Doug

The existing protocols (pg_dump, etc) are largely geared towards
DBA-level backups, thus they tend to depend upon backend database
access and validation schemes, ie, through .pgpass or pg_hba.conf,
as opposed to frontend tools and user-level privilege grants.

What's wrong with \copy in psql?

\copy is fairly limited, especially when compared with the import/export
options in something like Access. This is one of those situation where
the tools that Access provides runs circles around the tools that psql
provides.

Also, what about users who don't have psql access?

More importantly, backups are a back end task, data import/export
is more of a front end task.

If we want to appeal to a wider user base, we have to consider user tool
needs.
--
Mike Nolan