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

Jason Tishler wrote:

I feel your pain, but I did try to help by adding the following to
/usr/doc/postgresql-7.2.2/FAQ_MSWIN:
   A pre-built PostgreSQL is part of the standard Cygwin distribution   and is installed by Cygwin's setup.exe. You are encouraged to use   this version unless it does not meet your needs. Please read the   README file, /usr/doc/Cygwin/postgresql-${version}.README, where   "${version}" is the version (e.g., 7.2). 

Unfortunately, the Cygwin port was named "MSWIN" and not "Cygwin".
Maybe this can (or should) be changed when the native Win32 PostgreSQL
port is release? 

Gotcha.

You know?  You'd think that your mention in the FAQ_MSWIN would be enough.  For some reason, hitting the readme we're working on here just totally elluded me for days.

Maybe we need some flashing neon lights pointing people to the readme, especially given the fact that cygipc is so critical and is mentioned only here.

And agreed, the Cygwin port should be distinguised from the native Win32 port, to be symantically correct.  Also, just to avoid confusion.

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.   

In XP Home, is there a way to create users via the GUI? 

Yes, you can create users via the GUI.  However, as you'll note from my comments later, the creation of users isn't the issue.  Something related for file and directory permissions is.

FYI, to get to the user creation/administration GUI in XP Home:  Start Menu -> Settings -> Control Panel -> User Accounts.

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.   

What do you mean by "filesystem owner?"  The user that owns the files? 

By "filesystem owner" I mean the user that owns the files/directories used by postgres.

Since most users (probably) install the postgres package at the time they install Cygwin, the files/directories used by postgres are owned by the user account used to run the Cygwin installer -- what I call "Cygwin installer user".

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.   

Are you sure about cygipc?  Under NT/2000, cygipc can run just fine
under SYSTEM.  You even contradict this below.

Cygipc actually runs fine under SYSTEM.

This was actually a bit of a confusing reference to some problems I had when I was using shell scripts to launch both cygipc and postgres.  Disregard mention of cygipc being unable to run under SYSTEM.

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.   

Why is a "postgres" user different than a "Cygwin installer user" in this
context? 

The best theory I can come up with is file/directory ownership.  That is, an XP user simply created and named as "postgres" user won't have ownership of the postgres files/directories.  Therefore, it will have countless problems when trying to execute.  Nor does SYSTEM work, even though it should theoretically have root-like access to the filesystem.

I've even attemped recursive "chown"'s of the various directories to the postgres user.  Perhaps I simply missed a few files/directories, and therefore postgres couldn't read or write to something it needed to.  I don't know.

I never fully worked this out, ...
[snip]   

Please try again.  If XP Home supports multiple users, then I really
would like to use the standard "postgres" user account to be consistent
will all other PostgreSQL installations. 

I'll try once again.  Using both a "postgres" user and as SYSTEM.

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."   

What is an XP Home "administrative" user?  How does one create such a
user or modify an existing user to become one?  Can one make the
"postgres" user an "administrative" user? 

In XP Home, there are (exactly) 2 different types of users:  Administrative and Guest.  When you create a user, you can declare it one or the other.  I think you can change existing users between the 2 types, as well.

In all of my attempts, I used only "Administrative" users.  SYSTEM, at least in my understanding, is a super-Administrative user with no limits to filesystem or application-execution rights.

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.   

I will add the above, but only if we can't get the "postgres" user to
work. 

Gotcha.

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

The above is already in my README.

Yep.  Just highlighting it, as it seems to be a step that is very easily forgotten/overlooked.

Especially when one makes many attempts to launch the various postgres executables after fiddling with config files/etc.  Rebooting, (this IS Windows, afterall), and whatever.  It's also very easy to overlook launching ipc-daemon each time.

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.   

I recommend leaving this as is to be consistent with NT/2000/XP Pro. 

Works for me.  Indeed, that's exactly how I have mine set-up right now.

    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.   

The above is a stylistic preference and not a functional one.  I will
leave it as is.  Of course, users can choose their preferred service
name. 

Granted.

We also must add a -- user <Cygwin installer user> (need double quotes
if there are spaces in the user name) ...
[snip]   

I would really like to determine whether or not a "postgres" user can be
used.  If not, then we will go with your "Cygwin installer user"
suggestion. 

Gotcha.  Again, I'll give it another shot.  Of course, if the "postgres" user IS the "Cygwin installer user" then there'd definitely be no problem.  Though, I find it highly unlikely that this would be the case in most instances.

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

Why?  I don't see the need. 

Sorry.  Got this readme confused with something I was working on with "sshd".

I really should stop writing these things up at 4 in the morning.  :-)

Phew.  That was long, but hopefully, complete.   

Wow!  Thanks for the above.  I really appreciate your time and the very
valuable information provided.

BTW, are you willing to run the regression test under XP Home and report
the results to the list?  I can help you set it up, if you are
interested. 

I'd be glad to help.  Let's get on it.

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

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