I have a postgresql
demon on my virtual Linux box, and just installed pgAdmin III on the Windows machine. Now I'd like to configure the first connection.
What I did so far:
- I edited
/etc/postgresql/9.1/main/pg_hba.conf
and added a line to make the demon accept connections from my Windows machine:host all all 192.168.123.45/32 md5
- I set a shell passphrase for the system user
postgres
(sudo passwd postgres
) - Edit: this seems to be irrelevant
I copied/usr/share/postgresql/9.1/pg_service.conf.sample
to/etc/postgresql-common/pg_service.conf
and uncommented the sample service, giving me
[postgres]
dbname=postgres
user=postgres - Edit: this seems to be irrelevant
I put a line into/etc/environment
to point to thepgservice.conf
file:PGSERVICEFILE=/etc/postgresql-common/pg_service.conf
(which works; when I duplicated the PuTTY session, it had this variable) - I restarted the database server after each change:
sudo /etc/init.d/postgresql restart
- Finally, I restartet the Linux box,
The server seems to have restarted successfully:
[ ok ] Restarting PostgreSQL 9.1 database server: main.
However, when I try to connect to the service, pgAdmin III gives me an error message:
Error connecting to the server: definition of service "postgres" not found.
dmesg | grep postgres
gives me a single line:
[ 18.054965] postgres (2242): /proc/2242/oom_adj is deprecated, please use /proc/2242/oom_score_adj instead.
I have no clue whether postgresql
uses my pq_service.conf
at all, or whatever else is the problem; any help is appreciated ...
Edit:
My original question, "How can I know whether postgresql uses my pg_service.conf file?
", seems to be answered - it simply doesn't. I still can't connect; but now the question doesn't match the error messages anymore.
I removed the "Service" entry from my "New Server Registration" data. Now I get another error - something like "password authentication for user >>postgres<< failed". I'm quite sure the password is correct - I just set it ... and I tested it by commenting out my ssh key from the authorized_keys
file.
I'd happily connect with my ssh key, but this seems to be difficult as well. With PuTTY, my key is taken from Pageant, and I'm logged in without any problem; pgAdmin talks about "SSH tunnelling", but I normally don't need tunnels for this local machine ...
I tried to create a tunnel anyway.
- PuTTY session:
- Source port is
5432
- destination is
my-vbox.host.name:5432
- Source port is
- In pgAdmin, "SSH Tunnel":
- Username
postgres
- using Identity file
- Tunnel host
localhost
→ error:
"SSH error: Could not connect to socket with error code 10051
" - Tunnel host
localhost:5432
→ error:
"SSH error: Unable to resolve host: localhost:5432
" - Tunnel host
127.0.0.1:5432
→ error:
"SSH error: Unable to resolve host: 127.0.0.1:5432
"
- Username
How the heck is this supposed to be configured?!
pg_service.conf
is a standard config file but forlibpq
-based clients. And has zero effect on the server side. – Milen A. Radevpostgres
, psql won't ask further questions when I intend to act as database userpostgres
. When the system userwww-data
runs Zope, and the database userwww-data
owns the database, all I need to connect is the database name. Those two are different things, but connected. – Tobiasmd5
as the auth method, you're telling that you want all users from 192.168.123.45 for all databases to provide a password. Replace it withtrust
to avoid the password if that's what is blocking you. – Daniel Vérité