Re: [ADMIN] postgresql in FreeBSD jails: proposal

Quoting Stephen Frost <sfrost@snowman.net>:

> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
> > "Marc G. Fournier" <scrappy@hub.org> writes:
> > > Easiest fix: change the UID of the user running the postmaster
> (ie. pgsql) so
> > > that each runs as a distinct UID (instead of distinct PGPORT) ...
> been doing 
> > > this since moving to FreeBSD 6.x ... no patches required ...
> > 
> > Sure, but in the spirit of "belt and suspenders too", I'd think
> that
> > doing that *and* something like Mischa's proposal wouldn't be bad.
> 
> I agree that we should try to be careful about stepping on 
> segments that might still be in use, but I would also discourage
> jail users from using the same uid for multiple PG clusters 
> since the jail doesn't protect the shmem segment.  
> We use seperate uids even w/ linux-vservers where shmem
> and everything *is* seperate, following the same 
> 'belt and suspenders too' spirit for security.

Thanks for all the input. Fixing freebsd might get answered
on a different channel :-)

Unfortunately, different uid's is not even an option here;
but serious security in this sitch is  not relevant, either.

We have a freebsd core guy here, and he says that there's no
pressing incentive for jails to handle sysv ipc, given mmap
and file locking :-( And given his other comments, I wouldn't
consider jails a "secure" environment, just a modest and
convenient way to emulate multiple machines with caveats.
.........................................................
So, given Tom's comment, that it's antisocial to zap a shm seg 
that other processes have attached ...

I'm going to skip the kill(1,0) test and depend on nattch only,
with a function that PGSharedMemoryIsInUse() can also use.
(For a healthy server, nattch is never less than 2, right?)
If no unpleasant edge cases come out of this in our test
framework, I'd like to submit that as a patch. 
Talked with our Linux guys about vserver, and they see no issues. 
Mr. Solaris here is currently a long way ooto ... opinions?

Afaics the change in behaviour is, if a degraded server exited
with some backend hanging, the second server will create a new
segment after bumping the ipc key; if system shm limits do not
allow for two such shm segments, the second server will bail.
For production systems, ensuring no orphan shm segs
is not left to heuristic clean-up by server re-start.

Hope that makes sense for the generic Postgres world.

If anyone is interested in creating hung backends, you can
create a named pipe, and tell the server to COPY from it.
---
Engineers think that equations approximate reality.
Physicists think that reality approximates the equations.
Mathematicians never make the connection.