Re: Postgres Cygwin Install-as-Service on XP Pro/Home Mini

-------- Original Message --------
Subject: Re: [CYGWIN] Postgres Cygwin Install-as-Service on XP Pro/Home
Mini HOWTO
Date: Mon, 11 Nov 2002 16:14:41 -0700
From: Kevin Wong <kevin-wong(at)usa(dot)net>
Reply-To: kevin-wong(at)usa(dot)net, kevin-wong(at)usa(dot)net
To: Jason Tishler <jason(at)tishler(dot)net>
References: <3DC76EA4(dot)7090503(at)usa(dot)net> <20021107201232(dot)GA1340(at)tishler(dot)net>

Sorry about the delay in responding.

I'd be happy to help, hence my original e-mail in the first place. :-)

Now, I'm not sure where to put my comment about the difference between
the /usr/doc/<packagename> directory and the
/usr/doc/Cygwin/<packagename> directory.

The reason I mention that is because of the incredibly lengthy time it
took for me to finally find your original readme. I'm pretty familiar
with most Unixes, and was more than capable of finding the
/usr/doc/postgres... directory. However, being somewhat unfamiliar with
Cygwin, I found it very difficult to actually get postgres going at all,
until I found your readme and understood the standard directory
structures for Cygwin packages.

I don't know whether it should be mentioned at all, given that this is
just a general Cygwin thing. And if so, whether to make more explicit
note in Cygwin documentation (generally) for the benefit of all. Maybe
I just missed the whole discussion in the general Cygwin documentation,
I don't know. Or maybe it's not in there.

Anyways, enough on that topic.

Onto your questions/suggestions...

In all of what's to follow, I'm assuming that Cygwin and the Postgres
data directories to be used are all on NTFS volumes. I have yet to try
any of this with Cygwin and/or Postgres on VFAT volumes mounted in
Cygwin. I think this is an important note for the Postgres/Cygwin readme.

Now, I'm about to go into a rather lengthy treatise on something here
that might have to be explained or touched-upon in the Postgres/Cygwin
readme.

As it turns out, perhaps the greatest issue of difficutly or confusion
will be with the user/security model under XP Home vs. that in NT/W2K/XP
Pro. Unfortunately, I don't have XP Pro on-hand to verify my comments
with 100% accuracy. However, I do know that the XP Home model is
substantially different from the rest.

On the W32 security model "continuum", XP Home sits right in the center
with 95/98/ME + FAT/VFAT on the lowest end, and NT/W2K/XP Pro + NTFS on
the high end.

That is, XP Home's file-system-level security is pretty good, indeed
vastly better than 95/98/ME because it's been designed using NTFS from
the ground-up, but still lacks the concept of user groups found in
NT/W2K/XP Pro.

Application-level security, however, is quite another story, and is
where XP Home really shows its limitations compared to NT/W2K/XP Pro.
Basically, XP Home improves substantially on 95/98/ME by adding truly
separate desktops/preferences and the mandatory log-in (finally, no more
hitting Cancel or ESC to bypass the login screen). XP Home also offers
a the very-much needed feature of NT/W2K/XP Pro "Services," (it is the
same kernel, afterall).

But it's right here, in the collision between security models and
Services in XP Home that we have a problem.

First of all, in terms of the Postgres/Cygwin readme, as I mentioned in
the previous e-mail, there is also a lack of command-line
user/user-group/security management tools in XP Home, which are probably
found in XP Pro. So, those steps must be omitted, which I see you've
done here.

Secondly, because of application-level security model limitations in XP
Home, it doesn't look like Cygipc or Postgres can be run as anything but
the filesystem owner. Or, at least the owner of the relevant Postgres
directories. That is, the SYSTEM user cannot be used as the account for
executing the Cygipc and Postgres services. Nor can a "postgres" user,
created for this purpose. The services just would refuse to run when I
tried either the SYSTEM or postgres users.

I never fully worked this out, but the problem seems related to file
ownership, even though I tried changing the owners of the various
directories. No matter what I tried with these accounts, I would always
get "FATAL 1: cannot open /usr/share/postgresql/data/PG_VERSION:
Permission denied" errors in in /var/log/postmaster.log.

Basically, things only seemed to work when the account for services
execution was the same as the filesystem owner -- and that account must
be an "administrative" user, not a "guest." When I changed the services
to my own user account, the one used to install Cygwin and postgres to
begin with, everything worked fine.

So, with all of this in mind...

We should premise all of your blended steps with the direction that the
user launch a Cygwin shell, after logging-in to XP Home as the user
under which Cygwin and Postgres were originally installed. And, very
importantly, that user must be an "administrative" user. Again, this is
all because of the security model limitations in XP Home.

Also, we can't forget to mention the need to obtain/install Cyg-ipc, either.

Now, assuming the user does login to XP Home as the Cygwin/Postgres
installer, and is an administrative user, and has installed Cyg-ipc,
let's look at each of your revised steps...

>1. Install the cygipc ipc-daemon as a NT service:
>
> $ ipc-daemon --install-as-service
>
>
>
Yep. Works. This uses the SYSTEM user as the login for the service.
That actually works just fine. To be strictly consistent, we could
instruct the user to change the account in the Services Control Panel
applet to match the user in the next step, but it's probably unnecessary.

>2. Install postmaster as a NT service:
>
> $ cygrunsrv --install postmaster --path /usr/bin/postmaster --args "-D /usr/share/postgresql/data -i" --dep ipc-daemon --termsig INT --shutdown
>
I would change --install postmaster to read --install "Cygwin Postgres"
<note double quotes>, to keep the display of the Cyg-ipc and Postgres
entries in the Services Control Panel applet grouped together.

We also must add a -- user <Cygwin installer user> (need double quotes
if there are spaces in the user name) and a --password <Cygwin installer
user's password> to the command. (Well, we don't strictly have to do
the --password switch, as the user will be prompted for their password
if it's not provided, we'd just have to explain this if we omit the
password switch).

If you don't use the "--user" switch, you'll be in for a world of
frustration and confusing error messages. Yes, that's even though
theoretically the SYSTEM user should have root-like privelege. Well,
for some damn reason, SYSTEM just doesn't work under XP Home for this
step. I think it's some other XP Home filesystem/app-level security
limitation, and is not found in XP Pro.

>3. Create the PostgreSQL data directory:
>
> $ mkdir /usr/share/postgresql/data
>
>4. Start the cygipc ipc-daemon:
>
> $ net start ipc-daemon # [1]
>
>5. Initialize PostgreSQL
>
> $ initdb -D /usr/share/postgresql/data
>
>6. Start postmaster:
>
> $ net start postmaster # [1]
>
>7. Connect to PostgreSQL:
>
> $ psql template1
>
>[1] Cygwin's bin directory (e.g., C:\Cygwin\bin) must be added to the
> Windows NT/2000/XP's system PATH and the machine rebooted for the
> SCM to find cygwin1.dll.
>
>
>

Everything above is just fine. I would also suggest adding
C:\Cygwin\sbin, and C:\Cygwin\usr\sbin to the PATH in note [1].

The only point of confusion that I had when reading the file was the [x]
numbers, only afterwards realizing that you were indicating a reference
to the notes below. I suggest making a quick note at the top of the
readme for clarity.

>Note that the ipc-daemon and postmaster services are installed to run
>under whatever user account XP Home uses for services.
>

Obviously, this isn't quite the case anymore, and can probably be excluded.

Phew. That was long, but hopefully, complete.

-Kev.

--
**********

This message is directed in confidence solely to the
person(s) identified. This message may also be subject to
privilege and all rights to that privilege are expressly
claimed. If you have received this message in error, please
notify the sender(s) and destroy the message immediately.

**********

Kevin Wong, LL.B
E-Mail: kevin-wong(at)usa(dot)net
Phone: 1-403-397-1778

--
**********

This message is directed in confidence solely to the
person(s) identified. This message may also be subject to
privilege and all rights to that privilege are expressly
claimed. If you have received this message in error, please
notify the sender(s) and destroy the message immediately.

**********

Kevin Wong, LL.B
E-Mail: kevin-wong(at)usa(dot)net
Phone: 1-403-397-1778