open port 5432 for postgres

Have a question or want to start a discussion? Post it! No Registration Necessary.  Now with pictures!

Threaded View
Hello....I'm new to newsgroups and this is my very first post.
I need to know how to open up port 5432 using iptables to allow me to
connect to our postgres server (Fedora Linux) with pgAdmin.

Thanks,
renz


Re: open port 5432 for postgres
Am Tue, 22 Nov 2005 13:59:32 -0800 schrieb renz:

Quoted text here. Click to load it

Just like allowing any other tcp connection to whatever port when using
iptables.

man iptables

and maybe concerning pg_hba.conf:

http://www.schwer.us/nblug/dba/postgresql.html#secure

Wolfgang

Re: open port 5432 for postgres

Ansgar -59cobalt- Wiechers wrote:
Quoted text here. Click to load it

host running pgAdmin is a Windows 2003 SBS server, and host running
iptables and postgres is a Fedora Core release 3 (Heidelberg) Kernel
2.6.9-1.667 on an i686
...I'm trying to setup a BACKUP postgres server in case the current one
goes down.

Quoted text here. Click to load it

I didn't use any options.
Yes, postgres is running on this host.
output of netstat -ntl:

Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address               Foreign Address
Stat e
tcp        0      0 0.0.0.0:32769               0.0.0.0:*
LIST EN
tcp        0      0 0.0.0.0:37                  0.0.0.0:*
LIST EN
tcp        0      0 0.0.0.0:111                 0.0.0.0:*
LIST EN
tcp        0      0 127.0.0.1:5432              0.0.0.0:*
LIST EN
tcp        0      0 127.0.0.1:25                0.0.0.0:*
LIST EN
tcp        0      0 :::22                       :::*
LIST EN
                           renz
Quoted text here. Click to load it


Re: open port 5432 for postgres
renz wrote:
 
Quoted text here. Click to load it

Your Postgres (like mine on this test machine) is only listening on the
loopback interface, thus it is not reachable from external clients ...

zaphod:~ # rcpostgresql start
Initializing the PostgreSQL database at location /var/lib/pgsql/data done
Starting PostgreSQL
                                          done
zaphod:~ # netstat -ntl

Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State
tcp        0      0 0.0.0.0:111             0.0.0.0:*               LISTEN
tcp        0      0 0.0.0.0:631             0.0.0.0:*               LISTEN
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN
                    ^^^^^^^^^^^^^^

loopback only

tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN
tcp        0      0 :::22                   :::*                    LISTEN
tcp        0      0 ::1:5432                :::*                    LISTEN
                     ^^^^^^^
ipV6 stuff, doesn't amtter here
  
tcp        0      0 ::1:25                  :::*                  
LISTEN

zaphod:~ # ps ax | grep postmaster
15891 pts/1    S      0:00 /usr/bin/postmaster -D /var/lib/pgsql/data

OK, the -i option is missing (I already told you to check this on
your box, so we have to change this, however this is a SuSE box and on
Fedora the configuration/file to edit will probably be different, anyhow I
have to edit /etc/sysconfig/postgresql:

# SuSE default setting ...
# POSTGRES_OPTIONS=""
# SuSE default changed to:
POSTGRES_OPTIONS="-iF"

zaphod:~ # rcpostgresql start
Starting PostgreSQL                                                   done

zaphod:~ # netstat -ntl
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State
tcp        0      0 0.0.0.0:111             0.0.0.0:*               LISTEN
tcp        0      0 0.0.0.0:631             0.0.0.0:*               LISTEN
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN
                  ^^^^^^^^^^^^^^

Well, there we are, that is what you need to see if you want your remote
clients to be able to communicate with the PostgreSQL server, after that
take care about the iptables rules.

Concerning the options please do a

man postmaster

on your box.

Wolfgang



Re: open port 5432 for postgres
Thanks everyone for helping me, especially to Wolfgang.....I just need
to add the -i option in postgres.....either that or reinstall postgres,
instead of working with somebody else's installation.

renz

Wolfgang Kueter wrote:
Quoted text here. Click to load it


Re: open port 5432 for postgres


Quoted text here. Click to load it

Do note, though, that if pgAdmin is on the same host, no port needs to
be opened. Unless you are paranoid enough to filter loopback...

        Joachim

Re: open port 5432 for postgres



jKILLSPAM.schipper@math.uu.nl wrote:
Quoted text here. Click to load it

NO, pgAdmin is not on the same host.

I added these to my iptables, applied the rule, then restarted
iptables:

iptables -A INPUT -p tcp --source 192.168.1.0/24  --syn --dport 5432 -j
ACCEPT
iptables -A INPUT -p udp --source 192.168.1.0/24  --dport 5432 -j
ACCEPT

but when I ran nmap to check, this is the result I get:

PORT    STATE SERVICE
22/tcp  open  ssh
37/tcp  open  time
111/tcp open  rpcbind

                         renz


Re: open port 5432 for postgres


renz wrote:

Quoted text here. Click to load it

Please post the output of iptables -nvL

and after that trust me and do something like:

# Quick and easy stateful filtering to overcome all problems with answer
# packets

iptables -A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
iptables -A OUTPUT -m state --state ESTABLISHED,RELATED -j ACCEPT

# Allow Postgres
iptables -A INPUT -s 192.168.1.0/24 -p tcp  --dport 5432 -m state \\
--state NEW -j ACCEPT

Quoted text here. Click to load it

Doesn't say anything, unless you can be sure that 5432 was is the range
that nmap scanned

what does telnet <destination_ip> 5432 say?

you should some sort of protocol like in this example:

---8<---
wk@work19:~> telnet work6 5432
Trying 192.168.1.6...
Connected to work6.
Escape character is '^]'.
quit
EFATAL:  invalid length of startup packet
Connection closed by foreign host.
---8<---

Wolfgang

Re: open port 5432 for postgres


Wolfgang Kueter wrote:
Quoted text here. Click to load it

Here the output of iptables -nvL:

Chain INPUT (policy ACCEPT 0 packets, 0 bytes)
 pkts bytes target     prot opt in     out     source
destination
29094 3828K RH-Firewall-1-INPUT  all  --  *      *       0.0.0.0/0
      0.0.0.0/0

Chain FORWARD (policy ACCEPT 0 packets, 0 bytes)
 pkts bytes target     prot opt in     out     source
destination
 2728  176K RH-Firewall-1-INPUT  all  --  *      *       0.0.0.0/0
      0.0.0.0/0

Chain OUTPUT (policy ACCEPT 18890 packets, 1733K bytes)
 pkts bytes target     prot opt in     out     source
destination

Chain RH-Firewall-1-INPUT (2 references)
 pkts bytes target     prot opt in     out     source
destination
10005  419K ACCEPT     all  --  lo     *       0.0.0.0/0
0.0.0.0/0
    5   280 ACCEPT     icmp --  *      *       0.0.0.0/0
0.0.0.0/0           icmp type 255
    0     0 ACCEPT     esp  --  *      *       0.0.0.0/0
0.0.0.0/0
    0     0 ACCEPT     ah   --  *      *       0.0.0.0/0
0.0.0.0/0
    0     0 ACCEPT     udp  --  *      *       0.0.0.0/0
224.0.0.251         udp dpt:5353
    0     0 ACCEPT     udp  --  *      *       0.0.0.0/0
0.0.0.0/0           udp dpt:631
 4907  994K ACCEPT     all  --  *      *       0.0.0.0/0
0.0.0.0/0           state RELATED,ESTABLISHED
  293 17568 ACCEPT     tcp  --  *      *       0.0.0.0/0
0.0.0.0/0           state NEW tcp dpt:22
16612 2574K REJECT     all  --  *      *       0.0.0.0/0
0.0.0.0/0           reject-with icmp-host-prohibited


Quoted text here. Click to load it

Connecting to 192.168.1.110...Could not open a connection to host on
port 5432 :
 Connect failed

I'll try your suggested rules tomorrow at work
                        renz


Re: open port 5432 for postgres


Am Wed, 23 Nov 2005 17:31:52 -0800 schrieb renz:


Quoted text here. Click to load it

Well, policies are ACCEPT but ...

 
Quoted text here. Click to load it

OK, everything via loopback interface allowed ...

Quoted text here. Click to load it

Accept some IMCP stuff:

Quoted text here. Click to load it


Accept IPSeC (VPN stuff)

Quoted text here. Click to load it

What might that be? /etc/services says Mulicast DNS, strange, anyhow ...

Quoted text here. Click to load it

Well ipp, but that is usually tcp ...

Quoted text here. Click to load it

This accepts all answers packets

    *       0.0.0.0/0
Quoted text here. Click to load it

Allows ssh acces to the box

Quoted text here. Click to load it

And the everything else is forbidden, so obviously the postgres
service is not reachable

Quoted text here. Click to load it

So your rules are obvioulsly wrong ...

Quoted text here. Click to load it

No wonder when you look at the loaded ruleset.

As we know fom the iptables -nvL output yu posted your rules are not
correct (the rules I posted yesterday will do) but besides that there is
another thing you should have in mind: Postgres might not be listening.
You can easily check this with netstat -an (you should see tcp port 5432
in listen state) or do a

ps ax

as root.

If you see the postmaster process without -i option like in:
 
6136 pts/1    S      0:00 /usr/bin/postmaster -D /var/lib/pgsql/data

you have to change the start options for the postgres daemon and make
sure that it is started with the -i flag (and maybe -F). I don't you
Fedora and therefore I'm not sure where configure this but finding
that out yourself should not be a great problem. After restarting the
daemon

ps ax will produce an output like:

6136 pts/1    S      0:00 /usr/bin/postmaster -D /var/lib/pgsql/data -i

And with the correct iptables rules, everything will be fine.

Wolfgang

Re: open port 5432 for postgres



Wolfgang Kueter wrote:
Quoted text here. Click to load it

I'm making progress...I can see port 5432 now when I run the nmap
command... but it says closed, as well as ports 53 & 80......I probably
need to check the order of the rules..

                   renz


Re: open port 5432 for postgres


Am Thu, 24 Nov 2005 14:09:05 -0800 schrieb renz:


Quoted text here. Click to load it

Just look at your ruleset with iptables -nvL. It shows you the ruleset
that is loaded and any packet passes through that ruleset until it reaches
one of the final targets.

Final targets are ACCEPT, DROP an REJECT. Non final targets for packets
are LOG and any self-defined chains while ACCEPT, DROP and REJECT are
again the final targets for those self-defined chains.

I'm pretty sure that you'll eventually figure it out. :-)

Wolfgang

Re: open port 5432 for postgres


renz wrote:
Quoted text here. Click to load it

Care to provide some details on the network? Which host is running
pgAdmin, which host is running iptables, and which one is running
postgres?

Quoted text here. Click to load it

Which options did you use for this scan? Is postgres even running on the
host you scanned? What's the output of "netstat -ntl" on that host?

cu
59cobalt
--
"Another option [for defragmentation] is to back up your important files,
erase the hard disk, then reinstall Mac OS X and your backed up files."
--http://docs.info.apple.com/article.html?artnum=25668

Re: open port 5432 for postgres


Quoted text here. Click to load it

Those rules look funky. Why do you not allow non-SYN TCP? And why allow
UDP at all?

If you do outbound filtering, you'll also want some rules for that...

I'm guessing, if the above is not a typo, that nmap -sS -p 5432 will
report the port as open...

        Joachim

Re: open port 5432 for postgres
Quoted text here. Click to load it

You're welcome ;-)

Quoted text here. Click to load it

Is it in your internal network? That would be fine. Then this will do:

iptables -A $CHAIN -p tcp -s $INTERNAL_NET --dport 5432 -d $POSTGRES_SERVER \\
 -j ACCEPT

(see man iptables)

Is it through the Internet? Don't do it! Better use i.e. ssh with
port forwarding for having a crypto tunnel.

Yours,
VB.
--
"Ich bin ein freier Mensch und werde jetzt von meinen Freiheitsrechten
Gebrauch machen - und zwar ausgiebig - natürlich nur in dem Rahmen, den
Otto Schily mir noch zur Verfügung stellt."
                   Wolfgang Clement am 10.10.05 als Noch-Superminister

Site Timeline