How to connect pgAdmin III (Windows) to postgresql (Linux in virtual box)

2
votes

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 the pgservice.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
  • 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"

How the heck is this supposed to be configured?!

1
postgresqlpgadmin
Actually pg_service.conf is a standard config file but for libpq-based clients. And has zero effect on the server side. - Milen A. Radev
Thank you - I forgot about the "Service" and updated my question. - Tobias
and I tested it by commenting out my ssh key: you're confusing the password of the postgres database user which is what the error is about, with the password of the postgres linux user which is relevant for ssh but not for the postgresql server. - Daniel Vérité
Yes, but when in a shell as user postgres, psql won't ask further questions when I intend to act as database user postgres. When the system user www-data runs Zope, and the database user www-data owns the database, all I need to connect is the database name. Those two are different things, but connected. - Tobias
@Tobias: it's the pg_hba.conf that controls all this. When you set md5 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 with trust to avoid the password if that's what is blocking you. - Daniel Vérité

1 Answers

4
votes

I finally managed to connect as db user tobias (I never had problems to connect locally, with psql).

# sudo -u postgres psql postgres
psql (9.1.9)
Type "help" for help.

postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication | {}
 tobias    | Superuser                                      | {}

postgres=# ALTER USER tobias WITH PASSWORD 's3cr3t';
ALTER ROLE

After doing this, I could connect as tobias, using the password just set. The "Maintenance DB" happens to be template1; I didn't try to change this.

For further reference - I activated log_connections in /etc/postgresql/9.1/main/postgresql.conf and watched the log:

sudo tail -f /var/log/postgresql/postgresql-9.1-main.log