Re: Automating logins for mundane chores

Phoenix Kiula wrote:
> I am writing some simple batch scripts to login to the DB and do a
> pg_dump. Also, when I login to do my own SQL tinkering, I'd like not
> to be asked for a password every time (which, for silly corporate
> reasons, is quite a convoluted one).
>
> So I read up on .pgpass. Where should this file be located. "User's
> home directory" says the manual, but there's no "home directory" for
> database users (or is there? if so, where?), only for the postgres
> user. So I promptly did "su - postgres" and added the requisite info
> in the .pgpass file therein. But that doesn't seem to automate
> anything for actual DB users.

.pgpass is read by the client. It may not even be located on the same
machine as your server, depending on where you run psql.

> Next, the manual refers to some PGPASSFILE env variable (
> http://www.postgresql.org/docs/8.2/interactive/libpq-envars.html ) but
> it is unclear where this environment is set up. I don't see any such
> setting in the postgresql.conf file, which wouldn't make much sense
> anyway. Where else can I tweak the environment variables? No pointer
> in the manual. Or was this page only some arcane C libpg stuff?

It's in the environment on the client machine. If it's for your scripts,
you can set it inside the script before you launch psql for example. If
you need it.

> So, my questions:
>
> 1. Where do I set up the automated password for (a) psql stuff and (b)
> for bash scripts or cron jobs -- I suppose both could have the same
> solution.

a) In the home directory of the user running psql.
b) In the home directory of the user running the cronjob.

> 2. While we're on psql, I quite like the "\timing" stuff inside psql.
> I find it very useful to have that on every time I login to psql
> console, but I don't see any command line option to automate this
> every time. The psql man page (
> http://www.postgresql.org/docs/8.2/interactive/app-psql.html ) talks
> about a "psqlrc", which on my system is found at
> "/usr/share/pgsql/psqlrc" so I opened it up, entered the only line:
>
> \timing
>
> And saved it. Then I started the psql console again, but there's no
> timing on by default. How can I setup default options for psql?

That should be /usr/share/pgsql/psqlrc.sample, which is just a sample
file and isn't parsed (unless your distribution did something really
strange). On a source install, you need to put the file in
/usr/local/pgsql/etc/psqlrc - since you're obviously not using a source
install, you'll need to put it wherever your package is configured to
have it (should be documented alongside the package, I hope).

The easier way is to put it in the file .psqlrc in your home directory
(same home directory as you put .pgpass in), assuming you only want this
for one user.

//Magnus