Wednesday, April 02, 2008

» PostgreSQL on openSUSE

PostgreSQL comes with pretty secure and locked down settings when installed. Here's a quick HOWTO to install the PostgreSQL database server on openSUSE and enable TCP/IP connections with user authentication. 1) Install the package postgresql-server which is in the OSS repository and ships with openSUSE media, here from a shell as root:
zypper install postgresql-server
2) Start and stop the PostgreSQL server to populate its data directory, still as root:
rcpostgresql start && rcpostgresql stop
3) Enable the md5 authentication method using the following commands as root:
su - postgres cat<<EOF >>~postgres/data/pg_hba.conf local all all md5 host all all md5 host all all ::1/128 md5 EOF
3.1) OPTIONAL: Note that if you also want to allow connections from other hosts, proceed as follows, still being in the same shell as above, as the user postgres (this example is to allow connections from all hosts -- you might want to restrict that for security reasons, as explained in the PostgreSQL documentation):
echo "host all all md5" >> ~postgres/data/pg_hba.conf
4) OPTIONAL: If you want to allow connections from any host then you must also do the following steps, from a shell as root (if you're still in the shell from above as user postgres, just type the command exit to return to its parent root shell): 4.1) make the PostgreSQL server listen on all network interfaces (it only listens on the loopback/localhost interface by default), by setting the variable POSTGRES_OPTIONS to -i in /etc/sysconfig/postgresql, using your preferred text editor (still from a shell as root):
4.2) open the PostgreSQL server listen port 5432 in the firewall, by adding the service name postgresql in the variable FW_SERVICES_ACCEPT_EXT in /etc/sysconfig/SuSEfirewall2 using your preferred text editor as root, e.g. like this:
FW_SERVICES_EXT_TCP="http https ftp postgresql"
4.3)Apply the above mentioned firewall modifications:
rcSuSEfirewall2 reload
5) Restart the PostgreSQL server to apply all settings we've made above (as root):
rcpostgresql restart

Labels: ,


Post a Comment

<< Home