Using dblink and dblink-connect-u
gargoyle60
localhost).
After reading the links...
http://www.postgresql.org/docs/9.0/static/contrib-dblink.html
http://www.postgresql.org/docs/9.0/interactive/contrib-dblink-connect-u.html
http://www.postgresql.org/docs/9.0/static/libpq-connect.html
and experimenting, I can manage to acquire any of my connections using the different dblink methods
described in the above links but only by specifying a password.
I notice in the third link it mentions...
"Password to be used if the server demands password authentication."
I am trying to develop a backup strategy and I want to execute a number of simple SELECT queries
from the main postgres superuser account to acquire certain information before running backups.
However, all of my databases have passwords and obviously I don't want to have to specify passwords
in scripts if I can avoid it.
So, is there a way to temporarily remove (then reinstate) server password authentication when
running as postgres?
Laurenz Albe
> I have been looking at connecing to remote databases (all my databases are on the same machine at
> localhost).
>
>
> I am trying to develop a backup strategy and I want to execute a number of simple SELECT queries
> from the main postgres superuser account to acquire certain information before running backups.
> However, all of my databases have passwords and obviously I don't want to have to specify passwords
> in scripts if I can avoid it.
> So, is there a way to temporarily remove (then reinstate) server password authentication when
> running as postgres?
To answer your last question, if you are user postgres and the database cluster
is local, you can modify pg_hba.conf to "trust" authentication and run "pg_ctl reload".
I don't know what your security concerns are, but temporarily relaxing
authentication checks does not seem such a good idea to me.
Does anybody who is *not* the DBA have shell access to the postgres
account on the server machine? If yes, you are in trouble anyway.
If no, you could consider adding a permanent entry to pg_hba.conf that
allows user postgres to connect *locally* without password, like
local postgres mydb trust
host postgres mydb 127.0.0.1/32 trust
That way you don't have to worry about passwords in administrative
scripts.
Yours,
Laurenz Albe
gargoyle60
>Does anybody who is *not* the DBA have shell access to the postgres
>account on the server machine? If yes, you are in trouble anyway.
I am the ONLY user on the machine, whether I log in as postgres or named user accounts for the
different databases. Nobody else has access unless they break in to my address or steal my PC.
I've looked at the "trust" setting and it might be suitable - all my databases are relatively small
(< 30,000 rows of data combined, each row only limited columns) and my backups should run within a
matter of two to three minutes, possibly less.
Most of my data is also encrypted (or will be) using my own external encryption algorithms so even
if somebody hacks in remotely there is little risk of them acquiring anything useful. Of course,
they could cause damage by overwriting my data, hence regular backups.