Re: initdb failure with PostgreSQL 7.3.2 / Cygwin 1.3.22-1 / - Mailing list pgsql-cygwin

Jason Tishler wrote:
...
> I have confirmed the above hypothesis with the attached test program,
> cit2.c.  If cit2.exe is invoked as follows:
...
> I will work with the cygipc maintainer to enhance cygipc to properly
> handle Fast User Switching.
>
> Your help in debugging this problem is greatly appreciated.

Jason,

    Thanks for all the help in this.  As I promised, I did a completely
clean install of Cygwin to verify this, and yep, the issue is definitely
with the 'Switch User' "feature" in XP.  As long as users completey sign
off as one user before signing on as another, they should be ok.  Might
be worth a comment in the README, at least 'til it's dealt with.

    Now, coupla pointers for those doing clean installs of Cygwin v1.3.22-1
with PostgreSQL v7.3.2.  The README instructions are fine as is.
However, note that for whatever reason, the current Cygwin install
(Cygwin v1.3.22-1 and setup.exe v2.340.2.5) appears to set /tmp and
/usr/bin permissions incorrectly.  So basically, users who are doing
such an install may want to be sure to
______________________________________________________________________
1.  Install Cygwin as usual.
2.  Run BASH and type the commands

    $ chmod 777 /tmp
    $ chmod 755 /usr/bin /usr/bin/*

     to be sure the directories/files have the right permissions.

3.  Install CygIPC as per its instructions. Not sure, but might be worth
     copying a few of those instructions over to the PostgreSQL README,
     as the actual command for unzipping and untarring the file is not on
     the page referenced by the usual URL:

http://www.neuro.gatech.edu/users/cwilson/cygutils/cygipc/index.html

     but rather at

http://www.neuro.gatech.edu/users/cwilson/cygutils/how_to_install.html

4.  Follow the instructions in /usr/doc/Cygwin/postgresql-7.3.2.README
______________________________________________________________________

WARNING:  One issue I still found is that when a user reaches step #10
           in the README, they may get the following:
    ____________________________________________________________
    $ psql -U postgres template1
    psql: could not connect to server: Bad file descriptor
            Is the server running locally and accepting
            connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
    ____________________________________________________________

Looking in /tmp, I found the following:
______________________________________________________________________
$ ls -al
total 5512
drwxrwxrwx+   3 Frank    Users           0 May  6 13:41 .
drwxrwx---+  10 Frank    Users           0 May  5 19:04 ..
-rwx------    1 postgres None           51 May  6 13:41 .s.PGSQL.5432
-rw-------    1 postgres None           32 May  6 13:41 .s.PGSQL.5432.lock
-rw-rw-rw-    1 SYSTEM   Administ  3916520 May  6 13:41 MultiFileMsg
-rw-rw-rw-    1 SYSTEM   Administ    22032 May  6 13:41 MultiFileSem
-rw-rw-rw-    1 SYSTEM   Administ   202768 May  6 13:41 MultiFileShm
-rw-------    1 postgres None      1499136 May  6 13:41 cygipc_0
______________________________________________________________________

    Notice the permissions on the files

    .s.PGSQL.5432
    .s.PGSQL.5432.lock

are such that ONLY the owner can access them.  As these files are used
for internal socket communications between psql and postmaster, and step
#10 shows the user being logged in with the administrative account (NOT
the 'postgres' account that owns those files), psql will fail, as it is
unable to read/write to these files at all.

    A simple

    $ chmod 755 /tmp/.s.PGSQL*

will correct this...but ONLY for the current bootup.  If the user
installed PostgreSQL and CygIPC as NT services, upon reboot, postmaster
will regenerate these /tmp/ files with the permissions reset to ONLY
allow the owner 'postgres' access.  THIS is a problem.

[Jason, do you know how PostgreSQL determines these permissions, or is
this yet another weird chmod issue?  I don't recall this failing with
older versions.  Does the fact that user SYSTEM in /etc/passwd not have
a path to a shell have any effect on setting permissions, as this is the
user context under which 'postmaster' runs?  I haven't been able to
figure out how to get postmaster to fire up and have the permissions on
these temp files set to something that gives users other than 'postgres'
enough access so that psql via internal sockets works properly.]

    Oh, and if users figure this isn't a problem as they'll only access
PostgreSQL via the TCP/IP port (5432 by default), and so they simply
follow the above instructions and then try the command:

    $ psql -h localhost -U postgres template1

this will also likely fail, as users must first modify the file

    /usr/share/postgresql/data/postgresql.conf

to permit TCP/IP connections (disabled by default).  To do so, note that

    /usr/share/postgresql/data

has its permissions set so only user 'postgres' can access the files (a
reasonable security setup).  So simply log off as the admin user and log
back in as 'postgres', work your way down to /usr/share/postgresql/data,
and use 'pico', 'vi', or the editor of your choice to modify
postgresql.conf so that it contains

    tcpip_socket = true

(it's one of the first settings in the file).

    Another option:  Do this as step #8.1 (after initializing the database)
while still logged in as 'postgres'.

    Once the /tmp/.s.PGSQL* permission issue is resolved, and Cygwin itself
sets permissions on /tmp and /usr/bin properly, hopefully the added
steps won't be necessary.  But for those so inclined, note whatever
issues occur, if you understand how the pieces interact, then you'll
have better luck tracking the problem down.  And in the case of
PostgreSQL, the pieces I know to watch are

    * the executables (located in /usr/bin)
    * the data and configuration files
      (located in /usr/share/postgresql/data normally)
    * the CygIPC semaphore files (in /tmp)
    * and the PostgreSQL lock files (also in /tmp)

Hope this information is helpful to at least one other person.