Обсуждение: PostgreSQL Backup Script

Dear all,

I am stuck with database backup.
While using pg_dump the utility asks for password but since I require
this process to be automated the password prompt is giving me
headaches
is their any way by which can pass the password automatically.
Like setting up a variable in php  which passes the password to the
pg_dump utility on prompt this way the whole process of pg_dump would
get automated.
One obvious solution was to use .pgpass but it does not appeal me
because it is again a security threat.
any pointers or help may lead to unlocked secret.

--
With Best Regards,
Vishal Kashyap.
Lead Software Developer,
http://saihertz.com,
http://vishalkashyap.tk

> One obvious solution was to use .pgpass but it does not appeal me
> because it is again a security threat.
> any pointers or help may lead to unlocked secret.

.pgpass is the only way to do it that is NOT a security threat.

You can set PGUSER and PGPASSWORD environmental variables, but they are
easily readable from other processes.

I suggest allowing the 'postgres' user to connect with 'ident sameuser'
in pg_hba.conf from the localhost only, then always just run pg_dump as
'postgres'.

Chris

> I suggest allowing the 'postgres' user to connect with 'ident sameuser'
> in pg_hba.conf from the localhost only, then always just run pg_dump as
> 'postgres'.

That means

# TYPE  DATABASE    USER        IP-ADDRESS        IP-MASK           METHOD
host    <mydb>  <myuser>         localhost     255.255.255.255
ident sameuser

only <myuser> will NOT be asked for password when it does
pg_dump -U <myuser>
when logged in to its shell account with <myuser>
and If I run a crontab with user <myuser> the above lines again stands true.


What you say ?

--
With Best Regards,
Vishal Kashyap.
Lead Software Developer,
http://saihertz.com,
http://vishalkashyap.tk

On Fri, Apr 01, 2005 at 11:18:13 +0530,
  "Vishal Kashyap @ [SaiHertz]" <vishalonlist@gmail.com> wrote:
> > I suggest allowing the 'postgres' user to connect with 'ident sameuser'
> > in pg_hba.conf from the localhost only, then always just run pg_dump as
> > 'postgres'.
>
> That means
>
> # TYPE  DATABASE    USER        IP-ADDRESS        IP-MASK           METHOD
> host    <mydb>  <myuser>         localhost     255.255.255.255
> ident sameuser
>
> only <myuser> will NOT be asked for password when it does
> pg_dump -U <myuser>
> when logged in to its shell account with <myuser>
> and If I run a crontab with user <myuser> the above lines again stands true.
>
>
> What you say ?

Don't do this as a host entry if your OS supports domain sockets with
getpeerid. Use a 'local' entry. That way you don't need to run an ident
server.

You also have the option to set up custom map entries so that the OS user
is not the same as the DB user.

vishalonlist@gmail.com ("Vishal Kashyap @ [SaiHertz]") wrote:
> I am stuck with database backup.
> While using pg_dump the utility asks for password but since I require
> this process to be automated the password prompt is giving me
> headaches
> is their any way by which can pass the password automatically.
> Like setting up a variable in php  which passes the password to the
> pg_dump utility on prompt this way the whole process of pg_dump would
> get automated.
> One obvious solution was to use .pgpass but it does not appeal me
> because it is again a security threat.
> any pointers or help may lead to unlocked secret.

You seem to be imagining that there can exist a way of storing some
form of a password in a fashion that allows automating these sorts of
things that _doesn't_ present an identical security threat to that
involved with .pgpass

It may take a bit of thinking about the problem to disabuse oneself of
such fantasies...
--
(reverse (concatenate 'string "gro.mca" "@" "enworbbc"))
http://linuxfinances.info/info/security.html
Rules of the Evil Overlord #125. "Should I actually decide to kill the
hero in  an elaborate escape-proof  deathtrap room (water  filling up,
sand pouring down, walls converging,  etc.) I will not leave him alone
five-to-ten  minutes  prior  to  "imminent" death,  but  will  instead
(finding a vantage point or  monitoring camera) stick around and enjoy
watching my adversary's demise." <http://www.eviloverlord.com/>