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(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