Dotaz: Vzdálené připojení na PostgreSQL server

[Wex]

Dobrý den,
nedaří se mi připojit vzdáleně na databázový server.
Server:
-Debian jméno gravy
Konfigurace je následující:
postgresql.conf

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'                  # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost', '*' = all
                                        # (change requires restart)
port = 5432                             # (change requires restart)
max_connections = 100                   # (change requires restart)
# Note:  Increasing max_connections costs ~400 bytes of shared memory per
# connection slot, plus lock space (see max_locks_per_transaction).
#superuser_reserved_connections = 3     # (change requires restart)
unix_socket_directory = '/var/run/postgresql'           # (change requires restart)
#unix_socket_group = ''                 # (change requires restart)
#unix_socket_permissions = 0777         # begin with 0 to use octal notation
                                        # (change requires restart)
#bonjour = off                          # advertise server via Bonjour
                                        # (change requires restart)
#bonjour_name = ''                      # defaults to the computer name
                                        # (change requires restart)

# - Security and Authentication -

#authentication_timeout = 1min          # 1s-600s
ssl = true                              # (change requires restart)
#ssl_ciphers = 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH'      # allowed SSL ciphers
                                        # (change requires restart)
#ssl_renegotiation_limit = 512MB        # amount of data between renegotiations
#password_encryption = on
#db_user_namespace = off

# Kerberos and GSSAPI
#krb_server_keyfile = ''
#krb_srvname = 'postgres'               # (Kerberos only)
#krb_caseins_users = off

# - TCP Keepalives -
# see "man 7 tcp" for details

#tcp_keepalives_idle = 0                # TCP_KEEPIDLE, in seconds;
                                        # 0 selects the system default
#tcp_keepalives_interval = 0            # TCP_KEEPINTVL, in seconds;
                                        # 0 selects the system default
#tcp_keepalives_count = 0               # TCP_KEEPCNT;
                                        # 0 selects the system default

------------------------------
pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             147.xxx.xxx.8/32        trust #ip adresa klienta
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5

------------------------------
iptables -L

Chain INPUT (policy DROP)
target     prot opt source               destination
ACCEPT     tcp  --  anywhere             gravy                tcp spts:1024:65535 dpt:postgresql state NEW,ESTABLISHED
...
...
...

Chain FORWARD (policy DROP)
target     prot opt source               destination
REJECT     all  --  anywhere             anywhere             reject-with icmp-port-unreachable

Chain OUTPUT (policy DROP)
target     prot opt source               destination
ACCEPT     tcp  --  gravy                anywhere             tcp spt:postgresql dpts:1024:65535 state ESTABLISHED
...
...
...

----------------------------------------
netstat -nlp | grep 5432

tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      17384/postgres
tcp6       0      0 ::1:5432                :::*                    LISTEN      17384/postgres
unix  2      [ ACC ]     STREAM     LISTENING     11232    17384/postgres      /var/run/postgresql/.s.PGSQL.5432

------------------------------
Klient je stanice windows. Připojuji se pomocí pgAdmin III. Tunelované připojení přes ssh funguje. Přímé připojení ne. Přímé připojení potřebuji kvůli jdbc pro konfiguraci databáze z javy. Na klientovy i serveru jsou zkoušel vypnout firewall, ale stále dostávám hlášku:

could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host "147.229.146.27" and accepting TCP/IP connections on port 5432?

Dokáže mi někdo poradit, kde mám hledat problém? Zdá se mi, že server i firewall mám nakonfigurovaný správně.
Předem děkuji za rady.

---

---

Odpovědi

[AraxoN]

Podľa netstat počúva len na localhoste. Reštartoval si postgres po zmene konfigurácie? S hviezdičkou by ten netstat mal vyzerať takto:

tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      2289/postgres
tcp6       0      0 :::5432                 :::*                    LISTEN      2289/postgres

Možno berie iný konfiguračný súbor, nie ten ktorý edituješ ty?

[Wex]

Tak problém byl v tom, že jsem dával

/etc/init.d/postgresql reload

a ne

/etc/init.d/postgresql restart

Při reloadu to ignorovalo změny k konfiguračních souborech :-/
Děkuju za nasměrování.

[neklan]

Mylim, ze reload funguje na vsechny configy krome toho jednoho hlavniho : postgresql.conf

pg_hba a spol staci obnovit reloadem, na hlavni potreba restart

[okbob]

Na cca 80% položek z postgresql.conf stačí reload. U těch, kde nestačí je to řečeno, že vyžadují restart

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
#port = 5432                            # (change requires restart)
max_connections = 100                   # (change requires restart)
 ...
#temp_buffers = 8MB                     # min 800kB
#max_prepared_transactions = 0          # zero disables the feature
                                        # (change requires restart)
# Note:  Increasing max_prepared_transactions costs ~600 bytes of shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
# It is not advisable to set max_prepared_transactions nonzero unless you
# actively intend to use prepared transactions.
#work_mem = 4MB                         # min 64kB
#maintenance_work_mem = 64MB            # min 1MB
#autovacuum_work_mem = -1               # min 1MB, or -1 to use maintenance_work_mem
#max_stack_depth = 2MB                  # min 100kB