Do you need hard link for PostgreSQL?

[Sunnz]

Hi,

I have set up Apache + MySQL + PHP on my BSD system... Apache it chrooted in /var/www, so the usual way to get PHP to be able to 'talk' to MySQL was to hard link to the MySQL socks file in /var/www/var/mysql or something like that.

I am wondering if I need to do the same for PostgreSQL? Lately I want to try using PostgreSQL from PHP as well.

Thanks.

[Sunnz]

Ok I guess I am gotta to answer my own question here...

It turns out that PostgreSQL can be accessed via either a Unix socket or TCP/IP connection, the default port is 5432 or something like that, the client and the server uses the default port to begin with.

Anyway, I edited /var/postgresql/data/postgresql.conf and make ListenAddress="*". I don't know why but when ListenAddress="localhost" it doesn't do TCP/IP for some reason... well actually it may, but it is weird...

I've also edited /var/postgresql/data/pg_hba.conf, and put my internal IP for the server it there... it is not 127.0.0.1/32 but like, if my server's internal IP was 10.1.1.1, I had to put 10.1.1.1/32 in to that file in order to 'allow' connections from 10.1.1.1, it is more or less like a firewall rule. I don't know why the PHP script wouldn't just connect to localhost, but that's what I had to do to make it work. The /32 is necessary as well, even for a single address.

[lvlamb]

Since you don't mention the OS you are running on, here are my settings for OpenBSD.
No links of any sort.

In rc.local:

if [ -x /usr/local/bin/pg_ctl ]; then
	echo -n ' postgreSQL . '
	su -l _postgresql -c "nohup /usr/local/bin/pg_ctl start \
	    -D /media/hda11/postgresql -l /media/hda11/postgresql/logfile \
	    -o '-D /media/hda11/postgresql' >/dev/null"
	echo -n " started . "
fi

Conversely, have to shutdown the server, hence in rc.shutdown:

if [ -f /media/hda11/postgresql/postmaster.pid ]; then
	su -l _postgresql -c "/usr/local/bin/pg_ctl stop -m fast \
	     -D /media/hda11/postgresql"
	rm -f /media/hda11/postgresql/postmaster.pid
fi

In this case, /media/hda11
is a ext2fs essentially shared among other OSes,
all that is needed is that both {_postgresql|postgresql} GID and UID need be the same in any accessing OS.

Got the db directories working across OSes with symlinks only when GID and UID are the same.
rc.local/rc.shutdown method are more explicit.

[Sunnz]

Yes I am running OpenBSD as well, and I've got that in rc.local and rc.shutdown scripts.

I guess you haven't used Apache/PHP/MySQL on OpenBSD?

[lvlamb]

Right.
Got LAMP (or BAMP) installed as deps. NEVER used PHP (but only on the command line) nor MySQL (besides deps that use it).
I stopped using PHP on the www server since I got aware of the numerous security checks I had to wire into the code: PHP code is easy to use, securing PHP code takes an unlimited amount of lines.

If you want to chroot PostgreSQL just install that db in a chroot'd environment.
Either you separate processes, or you don't. Linking a chroot'd db to a local db is a bad idea.

Better look for solutions as rsync or clusters, but don't allow chroot'd instances to access a production db.

[Sunnz]

I am not trying to put a db into a chroot here, not linking chroot db to local db...

It was just that, in order for a PHP script running in Apache with chroot, one would need to hard link /var/run/mysql/mysql.sock into /var/www/var/run/mysql/mysql.sock, it has something to do with that PHP needs to talk to MySQL via that Unix socket.

But yea that problem is basically non existent with PostgreSQL, since it just communicates over TCP/IP, even when PHP is running inside Apache and it is chrooted.