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@usa.net>
Reply-To:	kevin-wong@usa.net, kevin-wong@usa.net
To:	Jason Tishler <jason@tishler.net>
References:	<3DC76EA4.7090503@usa.net> <20021107201232.GA1340@tishler.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@usa.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@usa.net
Phone:  1-403-397-1778