Обсуждение: 8.3 .4 + Vista + MingW + initdb = ACCESS_DENIED

I'm trying to upgrade my copy of postgresql from 8.2.x to 8.3.4 on a
Windows Vista SP1 laptop. I build postgres using mingw/msys and have had
no issues with 8.1.x and 8.2.x.  However, with 8.3.4 I run into problems.

First, building fails:

c:/Development/MingW/bin/../lib/gcc/mingw32/3.4.5/../../../../include/sspi.h:60: 

error: syntax error before "SECURITY_STRING"
In file included from
c:/Development/MingW/bin/../lib/gcc/mingw32/3.4.5/../../../../include/security.h:39,                 from
../../../../src/include/libpq/libpq-be.h:50,

This also happens from libpq-int.h.  The solution in both cases is to
add an additional header file:

#ifdef ENABLE_SSPI
#define SECURITY_WIN32
#include <ntsecapi.h>  <------- Add this include
#include <security.h>
#undef SECURITY_WIN32

That fixes the build issue.

Second, once I've successfully built and installed postgres, I run into 
a bigger problem.  When using initdb, I get this error:

creating template1 database in c:/Data/postgres30/base/1 ... FATAL:
could not create shared memory segment: 5
DETAIL:  Failed system call was CreateFileMapping(size=1802240,
name=Global\PostgreSQL:c:/Data/postgres).

A bit of googling and reading MSDN docs shows that applications that
don't run in Session 0 on Vista are not allowed to create shared memory
in the Global namespace.  Since initdb is invoked from the command line,
it runs in Session 1.

To get around this, you can give the user running and application the
"Create Global objects" right using the Local Security Policy.  Needless
to say I did that without any luck.

I then installed the pre-built binaries for Vista using the official
windows installer.  Calling initdb in the same way with the same user works.

With 8.3.x the installer uses binaries built with VC 2005 instead of
mingw - so clearly there are lots of differences.  But I'm wondering if
there is some difference in the way security is setup - maybe the
addition of a manifest file to initdb that allows it to create global
shared memory?  I also assume it has to do with the way DACLs are setup,
as described in this thread:

http://archives.postgresql.org/pgsql-patches/2008-02/msg00074.php

Or maybe its the way the executables are installed - I see that the
installer makes SYSTEM their owner which of course doesn't happen with
make install on MingW/msys.

So I'm stumped - the same user running initdb built with VC++ works but
running initdb with MingW fails.  Any help much appreciated...

Thanks,

Charlie











-- 
Charlie Savage
http://cfis.savagexi.com

Charlie Savage wrote:
> I'm trying to upgrade my copy of postgresql from 8.2.x to 8.3.4 on a
> Windows Vista SP1 laptop. I build postgres using mingw/msys and have had
> no issues with 8.1.x and 8.2.x.  However, with 8.3.4 I run into problems.
> 
> First, building fails:
> 
> c:/Development/MingW/bin/../lib/gcc/mingw32/3.4.5/../../../../include/sspi.h:60:
> 
> error: syntax error before "SECURITY_STRING"
> In file included from
> c:/Development/MingW/bin/../lib/gcc/mingw32/3.4.5/../../../../include/security.h:39,
> 
>                  from ../../../../src/include/libpq/libpq-be.h:50,
> 
> This also happens from libpq-int.h.  The solution in both cases is to
> add an additional header file:
> 
> #ifdef ENABLE_SSPI
> #define SECURITY_WIN32
> #include <ntsecapi.h>  <------- Add this include
> #include <security.h>
> #undef SECURITY_WIN32
> 
> That fixes the build issue.

What the... This works fine on the mingw versions on the buildfarm :-(

Have you done anything special to your mingw install?


> Second, once I've successfully built and installed postgres, I run into
> a bigger problem.  When using initdb, I get this error:
> 
> creating template1 database in c:/Data/postgres30/base/1 ... FATAL:
> could not create shared memory segment: 5
> DETAIL:  Failed system call was CreateFileMapping(size=1802240,
> name=Global\PostgreSQL:c:/Data/postgres).
> 
> A bit of googling and reading MSDN docs shows that applications that
> don't run in Session 0 on Vista are not allowed to create shared memory
> in the Global namespace.  Since initdb is invoked from the command line,
> it runs in Session 1.

Where did you find that information? I've been specifically looking for
it, but my searches didn't turn up anything conclusive.

The latest versions contain a fix for the global namespace code. Dave
noticed that this caused issues on vista and thus manually reverted the
patch in the official binary installer. But since we haven't (hadn't)
yet found documentation as to *why* it was failing, the patch has not
yet been reverted in the main source tree.

This is why it's working, probably, and it's not related to how it's built.

If you want to revert the patch in your local tree, this is the one:
http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/port/win32_shmem.c?r1=1.4&r2=1.5

Specifically, you can reintroduce the old bug (that I think is hat made
it work on Vista) by removing the +18 in the lowest loop there.

//Magnus

> 
> Have you done anything special to your mingw install?

The only thing different is that I upgraded to the latest mingw and msys 
packages a couple of weeks ago.  The other thing I can think of is that 
I installed openssl and related packages (the official ones from the 
msys project).  Would that force ENABLE_SSPI to be set or is it always 
set (and is it set in the build farm)?

>> Second, once I've successfully built and installed postgres, I run into
>> a bigger problem.  When using initdb, I get this error:
>>
>> creating template1 database in c:/Data/postgres30/base/1 ... FATAL:
>> could not create shared memory segment: 5
>> DETAIL:  Failed system call was CreateFileMapping(size=1802240,
>> name=Global\PostgreSQL:c:/Data/postgres).
>>
>> A bit of googling and reading MSDN docs shows that applications that
>> don't run in Session 0 on Vista are not allowed to create shared memory
>> in the Global namespace.  Since initdb is invoked from the command line,
>> it runs in Session 1.
> 
> Where did you find that information? I've been specifically looking for
> it, but my searches didn't turn up anything conclusive.

Yeah, information is scattered and came mostly through reading MSDN 
forum posts.  It seems a number of people have been bitten by this 
change to createFileMapping in Vista. My searches:

http://www.google.com/search?hl=en&q=createfilemapping+access_denied+vista&btnG=Search
http://www.google.com/search?hl=en&q=createfilemapping+SeCreateGlobalPrivilege+&btnG=Search

In particular, the session 0 information is documented at:

http://msdn.microsoft.com/en-us/library/aa366537(VS.85).aspx

Quoting:

> The name can have a "Global\" or "Local\" prefix  to explicitly create the object in the global or session
namespace...creatinga file mapping object in the global namespace from a session other than session zero requires the
SeCreateGlobalPrivilegeprivilege.
 

And more here:

http://msdn.microsoft.com/en-us/library/aa480152.aspx#appcomp_topic12

And this blog post might be helpful:

http://www.celceo.com/blogs/windows-insight/2007/09/global-createfilemapping-under.html

Towards the bottom it says:

> Additionally, of course, we need to create everything with the appropriate security 
attributes.  Null Dacls don't grant global access in Vista, so we need 
to create
a real security descriptior and add an ACE with GENERIC_ALL privileges 
for the world SID.

So - clearly initdb needs the SeCreateGlobalPrivilege  permission.  But 
I gave the user that runs initdb that permission using the Local 
Security Policy Editor, so something else is going on.  My first guess 
was that the initdb code had to also request that permission also, but 
that theory seems unlikely to be correct since the official win32 
binaries seem to work fine (I'm assuming they are the exact same code??)

> The latest versions contain a fix for the global namespace code. Dave
> noticed that this caused issues on vista and thus manually reverted the
> patch in the official binary installer. But since we haven't (hadn't)
> yet found documentation as to *why* it was failing, the patch has not
> yet been reverted in the main source tree.
> 
> This is why it's working, probably, and it's not related to how it's built.

Ah - so the trunk code is different than the binary release code?  That 
would explain it.  And in that case, then maybe the solution is setting 
up the security descriptor for calling createFileMapping?

> 
> If you want to revert the patch in your local tree, this is the one:
> http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/port/win32_shmem.c?r1=1.4&r2=1.5
> 
> Specifically, you can reintroduce the old bug (that I think is hat made
> it work on Vista) by removing the +18 in the lowest loop there.

Ok, I just reverted the whole patch (so all 4 changes), did a make clean 
; make; make install.  And no dice, I'm still having the same issue.

Thanks,

Charlie

-- 
Charlie Savage
http://cfis.savagexi.com

Magnus Hagander wrote:
>> Second, once I've successfully built and installed postgres, I run into
>> a bigger problem.  When using initdb, I get this error:
>>
>> creating template1 database in c:/Data/postgres30/base/1 ... FATAL:
>> could not create shared memory segment: 5
>> DETAIL:  Failed system call was CreateFileMapping(size=1802240,
>> name=Global\PostgreSQL:c:/Data/postgres).
>>
>> A bit of googling and reading MSDN docs shows that applications that
>> don't run in Session 0 on Vista are not allowed to create shared memory
>> in the Global namespace.  Since initdb is invoked from the command line,
>> it runs in Session 1.
>>     
>
> Where did you find that information? I've been specifically looking for
> it, but my searches didn't turn up anything conclusive.
>
> The latest versions contain a fix for the global namespace code. Dave
> noticed that this caused issues on vista and thus manually reverted the
> patch in the official binary installer. But since we haven't (hadn't)
> yet found documentation as to *why* it was failing, the patch has not
> yet been reverted in the main source tree.
>
> This is why it's working, probably, and it's not related to how it's built.
>
> If you want to revert the patch in your local tree, this is the one:
> http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/port/win32_shmem.c?r1=1.4&r2=1.5
>
> Specifically, you can reintroduce the old bug (that I think is hat made
> it work on Vista) by removing the +18 in the lowest loop there.
>
>
>   

I am getting this error on XP Pro. The buildfarm members run happily 
from the scheduler, but when run by hand from the command line they 
fail. This is true of both MinGW and MSVC.

This is in *URGENT* need of a fix.

cheers

andrew

On Mon, Oct 13, 2008 at 10:38 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
>
>
> I am getting this error on XP Pro. The buildfarm members run happily from
> the scheduler, but when run by hand from the command line they fail. This is
> true of both MinGW and MSVC.
>

It's on my list to discuss it with Magnus when we meet in Italy
tomorrow. The simple fix is to back out the change that broke it,
which leaves us in our previous broken-but-less-severely state (which
is what we did for the binary packages).

> This is in *URGENT* need of a fix.

I can't see us pushing a point release for this alone (I think I've
seen two reports of people running into the problem when building from
source themselves, and one was internal here in EDB), so the urgency
is simply to ensure it's fixed before we do produce our next release
imho.

-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

> It's on my list to discuss it with Magnus when we meet in Italy
> tomorrow. The simple fix is to back out the change that broke it,
> which leaves us in our previous broken-but-less-severely state (which
> is what we did for the binary packages).

If you mean reverting the patch that Mangus mentioned, I tried that, and 
it did not solve the problem.  See:

http://archives.postgresql.org/pgsql-hackers/2008-09/msg01517.php

So I'm guessing it is something else.

> 
>> This is in *URGENT* need of a fix.
> 
> I can't see us pushing a point release for this alone (I think I've
> seen two reports of people running into the problem when building from
> source themselves, and one was internal here in EDB), so the urgency
> is simply to ensure it's fixed before we do produce our next release
> imho.

Well - building your own Postgres 8.3 on Windows using MingW appears 
broken.  Not sure how many people fall into that category, but its seems 
like a fairly major issue.

Charlie

On Tue, Oct 14, 2008 at 9:43 AM, Charlie Savage <cfis@savagexi.com> wrote:
>> It's on my list to discuss it with Magnus when we meet in Italy
>> tomorrow. The simple fix is to back out the change that broke it,
>> which leaves us in our previous broken-but-less-severely state (which
>> is what we did for the binary packages).
>
> If you mean reverting the patch that Mangus mentioned, I tried that, and it
> did not solve the problem.  See:
>
> http://archives.postgresql.org/pgsql-hackers/2008-09/msg01517.php
>
> So I'm guessing it is something else.

You must be seeing an unrelated issue then - it's definitely that
change which caused us to start using the global namespace again, for
which it seems additional privileges may be required, and reverting
that change has fixed the issue on every failure machine I've seen.

It's also worth noting that that was the *only* change made to the
binary packages, and despite thousands of downloads we've seen no bug
reports that could be attributed to this.

>>> This is in *URGENT* need of a fix.
>>
>> I can't see us pushing a point release for this alone (I think I've
>> seen two reports of people running into the problem when building from
>> source themselves, and one was internal here in EDB), so the urgency
>> is simply to ensure it's fixed before we do produce our next release
>> imho.
>
> Well - building your own Postgres 8.3 on Windows using MingW appears broken.
>  Not sure how many people fall into that category, but its seems like a
> fairly major issue.

Very few people build their own Postgres on Windows, because it's not
exactly straightforward to do because of all the dependencies.


-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

Dave Page wrote:
>>
>> Well - building your own Postgres 8.3 on Windows using MingW appears broken.
>>  Not sure how many people fall into that category, but its seems like a
>> fairly major issue.
>>     
>
> Very few people build their own Postgres on Windows, because it's not
> exactly straightforward to do because of all the dependencies.
>
>   

IMNSHO, it is not acceptable to leave CVS for any significant period in 
a state where someone can't run "make check" by hand, and you certainly 
can't assume that no-one will want to do so.

I can confirm that commenting out '+ 18' from the last loop of the 
offending patch allows me to run and test on XP-Pro.

What do we actually need to create this object in the global namespace?

cheers

andrew

On Tue, Oct 14, 2008 at 2:33 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
>
>
> Dave Page wrote:
>>>
>>> Well - building your own Postgres 8.3 on Windows using MingW appears
>>> broken.
>>>  Not sure how many people fall into that category, but its seems like a
>>> fairly major issue.
>>>
>>
>> Very few people build their own Postgres on Windows, because it's not
>> exactly straightforward to do because of all the dependencies.
>>
>>
>
> IMNSHO, it is not acceptable to leave CVS for any significant period in a
> state where someone can't run "make check" by hand, and you certainly can't
> assume that no-one will want to do so.

It's not the case that no one can run 'make check' - it's very much
dependent on the configuration of the user account. Much as it pains
me to say it, running as Administrator or a member of the
Administrators group should normally work as you will inherit the
required privileges.

> I can confirm that commenting out '+ 18' from the last loop of the offending
> patch allows me to run and test on XP-Pro.
>
> What do we actually need to create this object in the global namespace?

The user running initdb (or the postmaster) needs
SeCreateGlobalPrivilege - which is something we cannot really start
telling people they must have. My view is that we revert the change
(well, replace it with something that looks less like a broken attempt
to use the global namespace) and leave it at that. iirc, the use of
the global namespace is there to ensure things work as they should
under a non-console terminal services session - which is pretty rare
and can usually be avoided.

-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

> The user running initdb (or the postmaster) needs
> SeCreateGlobalPrivilege - which is something we cannot really start
> telling people they must have. My view is that we revert the change
> (well, replace it with something that looks less like a broken attempt
> to use the global namespace) and leave it at that. iirc, the use of
> the global namespace is there to ensure things work as they should
> under a non-console terminal services session - which is pretty rare
> and can usually be avoided.

I'm not so sure that non-console terminal service sessions should be
categorized as "pretty rare".

I use them routinely.

...Robert

On Tue, Oct 14, 2008 at 3:31 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>> The user running initdb (or the postmaster) needs
>> SeCreateGlobalPrivilege - which is something we cannot really start
>> telling people they must have. My view is that we revert the change
>> (well, replace it with something that looks less like a broken attempt
>> to use the global namespace) and leave it at that. iirc, the use of
>> the global namespace is there to ensure things work as they should
>> under a non-console terminal services session - which is pretty rare
>> and can usually be avoided.
>
> I'm not so sure that non-console terminal service sessions should be
> categorized as "pretty rare".
>
> I use them routinely.

For installing and running Postgres? Note that we're not talking about
running clients apps here, but the server itself.

-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

"Robert Haas" <robertmhaas@gmail.com> writes:
>> The user running initdb (or the postmaster) needs
>> SeCreateGlobalPrivilege - which is something we cannot really start
>> telling people they must have. My view is that we revert the change
>> (well, replace it with something that looks less like a broken attempt
>> to use the global namespace) and leave it at that. iirc, the use of
>> the global namespace is there to ensure things work as they should
>> under a non-console terminal services session - which is pretty rare
>> and can usually be avoided.

> I'm not so sure that non-console terminal service sessions should be
> categorized as "pretty rare".

Would there be any value in trying a global name first and falling back
to non-global if that fails?
        regards, tom lane

Dave Page wrote:
> On Tue, Oct 14, 2008 at 2:33 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
>>
>> Dave Page wrote:
>>>> Well - building your own Postgres 8.3 on Windows using MingW appears
>>>> broken.
>>>>  Not sure how many people fall into that category, but its seems like a
>>>> fairly major issue.
>>>>
>>> Very few people build their own Postgres on Windows, because it's not
>>> exactly straightforward to do because of all the dependencies.
>>>
>>>
>> IMNSHO, it is not acceptable to leave CVS for any significant period in a
>> state where someone can't run "make check" by hand, and you certainly can't
>> assume that no-one will want to do so.
> 
> It's not the case that no one can run 'make check' - it's very much
> dependent on the configuration of the user account. Much as it pains
> me to say it, running as Administrator or a member of the
> Administrators group should normally work as you will inherit the
> required privileges.
> 
>> I can confirm that commenting out '+ 18' from the last loop of the offending
>> patch allows me to run and test on XP-Pro.
>>
>> What do we actually need to create this object in the global namespace?
> 
> The user running initdb (or the postmaster) needs
> SeCreateGlobalPrivilege - which is something we cannot really start
> telling people they must have. My view is that we revert the change
> (well, replace it with something that looks less like a broken attempt
> to use the global namespace) and leave it at that. iirc, the use of
> the global namespace is there to ensure things work as they should
> under a non-console terminal services session - which is pretty rare
> and can usually be avoided.

Not quite. The reason it's in the global namespace is to provide an
interlock preventing the starting of a postmaster in two different
sessions at the same time against the same data directory. We need to
figure out exactly how much this interlock is reduced, and if there is
something else we can do to make it work on Vista+...

//Magnus

Tom Lane wrote:
> "Robert Haas" <robertmhaas@gmail.com> writes:
>>> The user running initdb (or the postmaster) needs
>>> SeCreateGlobalPrivilege - which is something we cannot really start
>>> telling people they must have. My view is that we revert the change
>>> (well, replace it with something that looks less like a broken attempt
>>> to use the global namespace) and leave it at that. iirc, the use of
>>> the global namespace is there to ensure things work as they should
>>> under a non-console terminal services session - which is pretty rare
>>> and can usually be avoided.
> 
>> I'm not so sure that non-console terminal service sessions should be
>> categorized as "pretty rare".
> 
> Would there be any value in trying a global name first and falling back
> to non-global if that fails?

Hmm. We could fail on the specific error that we get in this case,
perhaps. I think it should be "a required privilege is not held by the
client", which shouldn't occur otherwise. If it's just "access denied",
that could equally well be caused by a running postmaster in a different
session under a different useraccount...

//Magnus

On Tue, Oct 14, 2008 at 4:19 PM, Magnus Hagander <magnus@hagander.net> wrote:
>
> Not quite. The reason it's in the global namespace is to provide an
> interlock preventing the starting of a postmaster in two different
> sessions at the same time against the same data directory. We need to
> figure out exactly how much this interlock is reduced, and if there is
> something else we can do to make it work on Vista+...

This isn't a Vista+ issue - it happens on XP as well.


-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

>> I'm not so sure that non-console terminal service sessions should be
>> categorized as "pretty rare".
>>
>> I use them routinely.
>
> For installing and running Postgres? Note that we're not talking about
> running clients apps here, but the server itself.

Sure, why not?  I mean, I've come across applications that simply fail
to install - or run - from a non-console session, but it's pretty
broken.  You'd at least like to get an error message that says, hey,
you have to run this from a console session, rather than just crapping
out for no obvious reason.

...Robert

On Tue, Oct 14, 2008 at 5:12 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>>> I'm not so sure that non-console terminal service sessions should be
>>> categorized as "pretty rare".
>>>
>>> I use them routinely.
>>
>> For installing and running Postgres? Note that we're not talking about
>> running clients apps here, but the server itself.
>
> Sure, why not?  I mean, I've come across applications that simply fail
> to install - or run - from a non-console session, but it's pretty
> broken.  You'd at least like to get an error message that says, hey,
> you have to run this from a console session, rather than just crapping
> out for no obvious reason.

You do get such a message from the installer.

-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

> The user running initdb (or the postmaster) needs
> SeCreateGlobalPrivilege - which is something we cannot really start

Why not ? Doesn't the pg installer already tweak the permissions of the
installation user. On XP you can connect to session 0, so that is an alternative on XP.

> telling people they must have. My view is that we revert the change
> (well, replace it with something that looks less like a broken attempt
> to use the global namespace) and leave it at that. iirc, the use of

yea, removing the +18 is grotesque, since it moves the shmem into the private
session but gives it a very misleading name (Global\ --> Global/).

> the global namespace is there to ensure things work as they should
> under a non-console terminal services session - which is pretty rare
> and can usually be avoided.

Hm? non-console is the default for remote, and all you can get in Vista/2008 and up.

The reason it should be in the Global namespace is that shmem is one part of
detecting an existing postmaster. Especially in situations where the db is started
by hand, the protection against duplicate startup is important.

Andreas

On Wed, Oct 15, 2008 at 10:49 AM, Zeugswetter Andreas OSB sIT
<Andreas.Zeugswetter@s-itsolutions.at> wrote:
>
>> The user running initdb (or the postmaster) needs
>> SeCreateGlobalPrivilege - which is something we cannot really start
>
> Why not ? Doesn't the pg installer already tweak the permissions of the
> installation user. On XP you can connect to session 0, so that is an alternative on XP.

It does, but that's not the user running initdb.

>> the global namespace is there to ensure things work as they should
>> under a non-console terminal services session - which is pretty rare
>> and can usually be avoided.
>
> Hm? non-console is the default for remote, and all you can get in Vista/2008 and up.

I didn't realise you couldn't get console on Vista/2K8. Guess I've
spent too much time on Macs in the last couple of years :-)

> The reason it should be in the Global namespace is that shmem is one part of
> detecting an existing postmaster. Especially in situations where the db is started
> by hand, the protection against duplicate startup is important.

Yeah, as Magnus reminded me.

-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

Dave Page wrote:
>> The reason it should be in the Global namespace is that shmem is one part of
>> detecting an existing postmaster. Especially in situations where the db is started
>> by hand, the protection against duplicate startup is important.
>>     
>
> Yeah, as Magnus reminded me.
>   

IMNSHO we need to find a different exclusion mechanism that isn't as 
cumbersome and surrounded by caveats as this one.

The buildfarm also uses an exclusion mechanism, based on Perl's flock(), 
and it's a simple one line call. AFAIK that works just fine on Windows 
(I will double check). If so, we should possibly look at how Perl does 
that on Windows.

cheers

andrew

Andrew Dunstan wrote:
>
>
> Dave Page wrote:
>>> The reason it should be in the Global namespace is that shmem is one 
>>> part of
>>> detecting an existing postmaster. Especially in situations where the 
>>> db is started
>>> by hand, the protection against duplicate startup is important.
>>>     
>>
>> Yeah, as Magnus reminded me.
>>   
>
> IMNSHO we need to find a different exclusion mechanism that isn't as 
> cumbersome and surrounded by caveats as this one.
>
> The buildfarm also uses an exclusion mechanism, based on Perl's 
> flock(), and it's a simple one line call. AFAIK that works just fine 
> on Windows (I will double check). If so, we should possibly look at 
> how Perl does that on Windows.

I have verified that it does indeed work. Underneath the hood it uses 
the native call LockFileEx() see win32io.c in Perl source. I suggest we 
should switch from this flaky use of Global namespace to having the 
postmaster acquire an explicit lock on a file in the datadir.

cheers

andrew

Andrew Dunstan wrote:

> I have verified that it does indeed work. Underneath the hood it uses  
> the native call LockFileEx() see win32io.c in Perl source. I suggest we  
> should switch from this flaky use of Global namespace to having the  
> postmaster acquire an explicit lock on a file in the datadir.

Does it work for all backends to grab a lock, so that if they continue
to live after postmaster has died, then the new postmaster cannot start?

I guess the postmaster could try-acquire an exclusive lock and all other
processes would acquire a shared lock.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Andrew Dunstan <andrew@dunslane.net> writes:
> I have verified that it does indeed work. Underneath the hood it uses 
> the native call LockFileEx() see win32io.c in Perl source. I suggest we 
> should switch from this flaky use of Global namespace to having the 
> postmaster acquire an explicit lock on a file in the datadir.

That can only be a solution if postmaster child processes will inherit
the lock.  (The nasty scenario is where the postmaster has died but one
or more backends are still alive --- a new postmaster attempting to
start MUST detect that and refuse to start.)  Does fork/exec preserve
lock ownership on Windows?
        regards, tom lane

Alvaro Herrera wrote:
> Andrew Dunstan wrote:
>
>   
>> I have verified that it does indeed work. Underneath the hood it uses  
>> the native call LockFileEx() see win32io.c in Perl source. I suggest we  
>> should switch from this flaky use of Global namespace to having the  
>> postmaster acquire an explicit lock on a file in the datadir.
>>     
>
> Does it work for all backends to grab a lock, so that if they continue
> to live after postmaster has died, then the new postmaster cannot start?
>
> I guess the postmaster could try-acquire an exclusive lock and all other
> processes would acquire a shared lock.
>   

I think we'd need to have the postmaster grab an exclusive lock to start 
with, to make sure nothing else has the lock, then swap to a shared lock 
before any backends are created. That might leave a tiny window on 
postmaster start, but I think it would be good enough.

cheers

andrew

Tom Lane wrote:
> Andrew Dunstan <andrew@dunslane.net> writes:
>   
>> I have verified that it does indeed work. Underneath the hood it uses 
>> the native call LockFileEx() see win32io.c in Perl source. I suggest we 
>> should switch from this flaky use of Global namespace to having the 
>> postmaster acquire an explicit lock on a file in the datadir.
>>     
>
> That can only be a solution if postmaster child processes will inherit
> the lock.  (The nasty scenario is where the postmaster has died but one
> or more backends are still alive --- a new postmaster attempting to
> start MUST detect that and refuse to start.)  Does fork/exec preserve
> lock ownership on Windows?
>
>             
>   

I don't think so, no. But we could have the children explicitly acquire 
a shared lock, so if the postmaster at startup tried to grab an 
exclusive lock that would fail if any child were still alive.

cheers

andrew

Tom Lane wrote:
> Does fork/exec preserve lock ownership on Windows?
>  

Not to my knowledge.  On windows, there is only CreateProcess 
(http://msdn.microsoft.com/en-us/library/ms682425.aspx).  That doesn't 
resemble the behavior of fork or exec at all.  Basically, there is no 
fork, windows favors threading models.  The only thing inheritable are 
handles, which doesn't include locked portions of a file.

The LockFileEx docs state:

"If a process terminates with a portion of a file locked or closes a 
file that has outstanding locks, the locks are unlocked by the operating 
system."

-- 
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/

Andrew Dunstan <andrew@dunslane.net> writes:
> Tom Lane wrote:
>> That can only be a solution if postmaster child processes will inherit
>> the lock.

> I don't think so, no. But we could have the children explicitly acquire 
> a shared lock, so if the postmaster at startup tried to grab an 
> exclusive lock that would fail if any child were still alive.

We've been through this before.  That is not an acceptable substitute
because there's a race condition: a new child might have been launched
but not yet acquired the lock.  We need the lock to actually be
inherited across the fork/exec so there is no window where it's not held.
        regards, tom lane

Andrew Chernow <ac@esilo.com> writes:
> Tom Lane wrote:
>> Does fork/exec preserve lock ownership on Windows?

> Not to my knowledge.  On windows, there is only CreateProcess 
> (http://msdn.microsoft.com/en-us/library/ms682425.aspx).  That doesn't 
> resemble the behavior of fork or exec at all.

Hmm.  Now that you mention it, didn't we solve a similar problem by
exploiting the behavior where CreateProcess creates a process but
doesn't start it running?  I'm envisioning
* Create child process in suspended state* Assign it ownership of a lock (can we do that?)* Set it running

If the postmaster crashes between steps 1 and 2, then the zombie process
doesn't hold a lock, but it will never do anything so it doesn't matter.

OTOH, if the postmaster crashes between steps 2 and 3, there's probably
no way to restart your database except to reboot ...
        regards, tom lane

Tom Lane wrote:
> Andrew Chernow <ac@esilo.com> writes:
>> Tom Lane wrote:
>>> Does fork/exec preserve lock ownership on Windows?
> 
>> Not to my knowledge.  On windows, there is only CreateProcess 
>> (http://msdn.microsoft.com/en-us/library/ms682425.aspx).  That doesn't 
>> resemble the behavior of fork or exec at all.
> 
> Hmm.  Now that you mention it, didn't we solve a similar problem by
> exploiting the behavior where CreateProcess creates a process but
> doesn't start it running?  I'm envisioning

Yes, we're doing this to pass certain handles down to it.


>     * Create child process in suspended state
>     * Assign it ownership of a lock (can we do that?)
>     * Set it running
> 
> If the postmaster crashes between steps 1 and 2, then the zombie process
> doesn't hold a lock, but it will never do anything so it doesn't matter.
> 
> OTOH, if the postmaster crashes between steps 2 and 3, there's probably
> no way to restart your database except to reboot ...

Haven't looked through the details (just arrived in Prato), but once the
process dies, the lock will go away. So there shoul dbe no need to
reboot, you just need to get rid of all the running (possibly orphaned)
processes.

//Magnus

Magnus Hagander wrote:
> Tom Lane wrote:
>> Andrew Chernow <ac@esilo.com> writes:
>>> Tom Lane wrote:
>>>> Does fork/exec preserve lock ownership on Windows?
>>> Not to my knowledge.  On windows, there is only CreateProcess 
>>> (http://msdn.microsoft.com/en-us/library/ms682425.aspx).  That doesn't 
>>> resemble the behavior of fork or exec at all.
>> Hmm.  Now that you mention it, didn't we solve a similar problem by
>> exploiting the behavior where CreateProcess creates a process but
>> doesn't start it running?  I'm envisioning
> 
> Yes, we're doing this to pass certain handles down to it.
> 
> 
>>     * Create child process in suspended state
>>     * Assign it ownership of a lock (can we do that?)
>>     * Set it running
>>
>> If the postmaster crashes between steps 1 and 2, then the zombie process
>> doesn't hold a lock, but it will never do anything so it doesn't matter.
>>
>> OTOH, if the postmaster crashes between steps 2 and 3, there's probably
>> no way to restart your database except to reboot ...
> 
> Haven't looked through the details (just arrived in Prato), but once the
> process dies, the lock will go away. So there shoul dbe no need to
> reboot, you just need to get rid of all the running (possibly orphaned)
> processes.
> 
> //Magnus
> 
> 

Be careful.  From LockFileEx docs:

"However, the time it takes for the operating system to unlock these 
locks depends upon available system resources. Therefore, it is 
recommended that your process explicitly unlock all files it has locked 
when it terminates. If this is not done, access to these files may be 
denied if the operating system has not yet unlocked them."

We knows how long it will take for there to be "available system 
resources".

-- 
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/

Andrew Chernow <ac@esilo.com> writes:
> Be careful.  From LockFileEx docs:

> "However, the time it takes for the operating system to unlock these 
> locks depends upon available system resources. Therefore, it is 
> recommended that your process explicitly unlock all files it has locked 
> when it terminates. If this is not done, access to these files may be 
> denied if the operating system has not yet unlocked them."

ROTFL ... so to translate: "If your program crashes, please release
locks before crashing."

No wonder Windows has such a crummy reputation.
        regards, tom lane

Tom Lane wrote:
> 
> Hmm.  Now that you mention it, didn't we solve a similar problem by
> exploiting the behavior where CreateProcess creates a process but
> doesn't start it running?  I'm envisioning
> 
>     * Create child process in suspended state
>     * Assign it ownership of a lock (can we do that?)
>     * Set it running
> 

Not sure, but the SECURITY_ATTRIBUTES argument to CreateFile has a 
bInheritHandle member that specifies whether a child process can inherit 
the fiel HANDLE.  I'm not sure if that would include locks on the file 
HANDLE.

-- 
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/

Tom Lane wrote:
> Andrew Chernow <ac@esilo.com> writes:
>> Be careful.  From LockFileEx docs:
> 
>> "However, the time it takes for the operating system to unlock these 
>> locks depends upon available system resources. Therefore, it is 
>> recommended that your process explicitly unlock all files it has locked 
>> when it terminates. If this is not done, access to these files may be 
>> denied if the operating system has not yet unlocked them."
> 
> ROTFL ... so to translate: "If your program crashes, please release
> locks before crashing."

Obviously that wasn't the intent of the above, but I guess it is the net 
effect.  Either way, I don't think it's a huge problem, it just means 
that PG may not be able to restart for a few seconds until the OS has 
time to clean-up the locks.

Andrew Chernow wrote:
> Tom Lane wrote:
>>
>> Hmm.  Now that you mention it, didn't we solve a similar problem by
>> exploiting the behavior where CreateProcess creates a process but
>> doesn't start it running?  I'm envisioning
>>
>>     * Create child process in suspended state
>>     * Assign it ownership of a lock (can we do that?)
>>     * Set it running
>>
> 
> Not sure, but the SECURITY_ATTRIBUTES argument to CreateFile has a 
> bInheritHandle member that specifies whether a child process can inherit 
> the fiel HANDLE.  I'm not sure if that would include locks on the file 
> HANDLE.
> 

hmmm.  Doesn't seem like that would work, or that it makes sense.  I'm 
guessing the bInheritHandle just makes the same handle available in the 
child process w/o having to reopen it (never used this feature).  Locked 
regions are probably still owned by the parent.

-- 
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/

> Obviously that wasn't the intent of the above, but I guess it is the net
> effect.  Either way, I don't think it's a huge problem, it just means that
> PG may not be able to restart for a few seconds until the OS has time to
> clean-up the locks.

Seconds?

Try "log off and log on again, and if that doesn't do it, then come
back in 10 minutes, and if that doesn't work, then reboot".

Maybe I'm just throwing fuel on the fire unnecessarily, but I've had a
lot of bad experiences with Windows thinking things are in use when
they really aren't.

...Robert

"Matthew T. O'Connor" wrote:

>Tom Lane wrote:
>> 
>> ROTFL ... so to translate: "If your program crashes, please release
>> locks before crashing."
>
>Obviously that wasn't the intent of the above, but I guess it is the net 
>effect.  Either way, I don't think it's a huge problem, it just means 
>that PG may not be able to restart for a few seconds until the OS has 
>time to clean-up the locks.

I don't think so. I am using DevStudio 2005 here and from time to time the
debugger crashes so that I have to kill the program via the task manager.
Afterwards it's not possible to load the crashed project again in a newly
started DevStudio session, because some project files are still locked
exclusively. The only action that helps is rebooting windows.

Now, I don't know whether DevStudio is using LockFileEx() but somehow this
sounds familiar.

Rainer

Rainer Bauer wrote:
> "Matthew T. O'Connor" wrote:
> 
>> Tom Lane wrote:
>>> ROTFL ... so to translate: "If your program crashes, please release
>>> locks before crashing."
>> Obviously that wasn't the intent of the above, but I guess it is the net 
>> effect.  Either way, I don't think it's a huge problem, it just means 
>> that PG may not be able to restart for a few seconds until the OS has 
>> time to clean-up the locks.
> 
> I don't think so. I am using DevStudio 2005 here and from time to time the
> debugger crashes so that I have to kill the program via the task manager.
> Afterwards it's not possible to load the crashed project again in a newly
> started DevStudio session, because some project files are still locked
> exclusively. The only action that helps is rebooting windows.
> 
> Now, I don't know whether DevStudio is using LockFileEx() but somehow this
> sounds familiar.
> 
> Rainer
> 

Has anyone considered not using a file lock on windows?  CreateMutex 
might do the trick if provided a mutex name, making it global rather 
than process bound.  OpenMutex can be used to test if the mutex exists 
or if it is currently locked.  I guess it would stay locked.  If there 
is a crash, it is automatically closed by the os.

The docs state the system closes the handle (mutex) when the process 
terminates and makes no mention of this being a lingering action like 
LockFileEx.  It sounds like the mutex is closed ASAP when the process 
terminates, just like file handles.

-- 
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/

Andrew Chernow wrote:
> Rainer Bauer wrote:
>> "Matthew T. O'Connor" wrote:
>>
>>> Tom Lane wrote:
>>>> ROTFL ... so to translate: "If your program crashes, please release
>>>> locks before crashing."
>>> Obviously that wasn't the intent of the above, but I guess it is the 
>>> net effect.  Either way, I don't think it's a huge problem, it just 
>>> means that PG may not be able to restart for a few seconds until the 
>>> OS has time to clean-up the locks.
>>
>> I don't think so. I am using DevStudio 2005 here and from time to 
>> time the
>> debugger crashes so that I have to kill the program via the task 
>> manager.
>> Afterwards it's not possible to load the crashed project again in a 
>> newly
>> started DevStudio session, because some project files are still locked
>> exclusively. The only action that helps is rebooting windows.
>>
>> Now, I don't know whether DevStudio is using LockFileEx() but somehow 
>> this
>> sounds familiar.
>>
>> Rainer
>>
>
> Has anyone considered not using a file lock on windows?  CreateMutex 
> might do the trick if provided a mutex name, making it global rather 
> than process bound.  OpenMutex can be used to test if the mutex exists 
> or if it is currently locked.  I guess it would stay locked.  If there 
> is a crash, it is automatically closed by the os.
>
> The docs state the system closes the handle (mutex) when the process 
> terminates and makes no mention of this being a lingering action like 
> LockFileEx.  It sounds like the mutex is closed ASAP when the process 
> terminates, just like file handles.
>

Please review the previous discussion. This whole thing came about 
because of major problems in handling Global objects.

cheers

andrew

Andrew Dunstan wrote:
>>
>> Has anyone considered not using a file lock on windows?  CreateMutex 
>> might do the trick if provided a mutex name, making it global rather 
>> than process bound.  OpenMutex can be used to test if the mutex exists 
>> or if it is currently locked.  I guess it would stay locked.  If there 
>> is a crash, it is automatically closed by the os.
>>
>> The docs state the system closes the handle (mutex) when the process 
>> terminates and makes no mention of this being a lingering action like 
>> LockFileEx.  It sounds like the mutex is closed ASAP when the process 
>> terminates, just like file handles.
>>
> 
> Please review the previous discussion. This whole thing came about 
> because of major problems in handling Global objects.
> 
> cheers
> 
> andrew
> 
> 

I did review it which is why I proposed global mutexes.  No one spoke 
about mutexes.  The conversation was about global sections, like file 
mappings.  Global sections fall under a stricter security policy than 
global mutexes.  I just ran the below code on Vista as a dumb-dumb 
non-administrative user (no SeCreateGlobalPrivilege) and it worked like 
a charm (compiled with VisualStudio.NET 2003 v7 13.10.3077).  Maybe I am 
missing something?

// run the below from different consoles.  the first run will
// create the mutex and sleep for 10 seconds before destroying it.
// The other console will open the existing mutex.
#include <windows.h>
#include <stdio.h>

int main(int argc, char **argv)
{  HANDLE h = CreateMutex(NULL, TRUE, "Global\\__postmaster__");
  if(h == NULL)  {    printf("CreateMutex: %u\n", GetLastError());    return 1;  }
  if(GetLastError() == ERROR_ALREADY_EXISTS)  {    printf("postmaster is running\n");  }  else  {    printf("postmaster
mutexcreated and ownership aquired\n");    Sleep(10000);  }
 
  CloseHandle(h);  return 0;
}

-- 
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/

Andrew Chernow wrote:
> Andrew Dunstan wrote:
>>>
>>> Has anyone considered not using a file lock on windows?  CreateMutex 
>>> might do the trick if provided a mutex name, making it global rather 
>>> than process bound.  OpenMutex can be used to test if the mutex 
>>> exists or if it is currently locked.  I guess it would stay locked.  
>>> If there is a crash, it is automatically closed by the os.
>>>
>>> The docs state the system closes the handle (mutex) when the process 
>>> terminates and makes no mention of this being a lingering action 
>>> like LockFileEx.  It sounds like the mutex is closed ASAP when the 
>>> process terminates, just like file handles.
>>>
>>
>> Please review the previous discussion. This whole thing came about 
>> because of major problems in handling Global objects.
>>
>>
>>
>>
>
> I did review it which is why I proposed global mutexes.  No one spoke 
> about mutexes.  The conversation was about global sections, like file 
> mappings.  Global sections fall under a stricter security policy than 
> global mutexes.  I just ran the below code on Vista as a dumb-dumb 
> non-administrative user (no SeCreateGlobalPrivilege) and it worked 
> like a charm (compiled with VisualStudio.NET 2003 v7 13.10.3077).  
> Maybe I am missing something?
>

OK, my apologies. This certainly looks like a promising line of 
development. Can you develop a complete patch along these lines?

cheers

andrew

Andrew Dunstan wrote:
> 
> 
> Andrew Chernow wrote:
>> Andrew Dunstan wrote:
>>>>
>>>> Has anyone considered not using a file lock on windows?  CreateMutex 
>>>> might do the trick if provided a mutex name, making it global rather 
>>>> than process bound.  OpenMutex can be used to test if the mutex 
>>>> exists or if it is currently locked.  I guess it would stay locked.  
>>>> If there is a crash, it is automatically closed by the os.
>>>>
>>>> The docs state the system closes the handle (mutex) when the process 
>>>> terminates and makes no mention of this being a lingering action 
>>>> like LockFileEx.  It sounds like the mutex is closed ASAP when the 
>>>> process terminates, just like file handles.
>>>>
>>>
>>> Please review the previous discussion. This whole thing came about 
>>> because of major problems in handling Global objects.
>>>
>>>
>>>
>>>
>>
>> I did review it which is why I proposed global mutexes.  No one spoke 
>> about mutexes.  The conversation was about global sections, like file 
>> mappings.  Global sections fall under a stricter security policy than 
>> global mutexes.  I just ran the below code on Vista as a dumb-dumb 
>> non-administrative user (no SeCreateGlobalPrivilege) and it worked 
>> like a charm (compiled with VisualStudio.NET 2003 v7 13.10.3077).  
>> Maybe I am missing something?
>>
> 
> OK, my apologies. This certainly looks like a promising line of 
> development. Can you develop a complete patch along these lines?
> 
> cheers
> 
> andrew
> 

I'm not familiar enough with the intialization process of the postmaster 
to provide a patch.  Yeah, I could learn it but I don't have time for 
that right now.  Unfortunately, I've had to do lots of windows coding 
which is why I jumped into this thread to help find a solution.

-- 
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/

Just wanted to close off this thread.

Previously I reported that building 8.3.4 with MingW on Windows resulted 
in an initdb executable that couldn't create new databases due to 
security restrictions in creating global file mappings in Vista.

I'm happy to say that the problem seems fixed in 8.3.5, which I have 
successfully built, installed, and created new databases with.

And just to note this for anyone else who runs into the issue - if you 
build postgresql with MingW then make sure you are using the latest 
version of the MingW runtime:

http://sourceforge.net/project/showfiles.php?group_id=2435&package_id=11598

Specifically, use version 3.15.1 released on 2008-10-04 (or hopefully 
later).  In the previous version, getopt_long is broken, meaning you 
cannot use any long style switches to initdb (for example, initdb 
--locale=C doesn't work and causes initdb to exit with an error message).

Thanks,

Charlie

Charlie Savage wrote:
> Just wanted to close off this thread.
> 
> Previously I reported that building 8.3.4 with MingW on Windows resulted
> in an initdb executable that couldn't create new databases due to
> security restrictions in creating global file mappings in Vista.
> 
> I'm happy to say that the problem seems fixed in 8.3.5, which I have
> successfully built, installed, and created new databases with.

Great!


> And just to note this for anyone else who runs into the issue - if you
> build postgresql with MingW then make sure you are using the latest
> version of the MingW runtime:
> 
> http://sourceforge.net/project/showfiles.php?group_id=2435&package_id=11598
> 
> Specifically, use version 3.15.1 released on 2008-10-04 (or hopefully
> later).  In the previous version, getopt_long is broken, meaning you
> cannot use any long style switches to initdb (for example, initdb
> --locale=C doesn't work and causes initdb to exit with an error message).

There are a lot of earlier versions that work just fine - it's just that
there are a number in between that don't :-(

I'd recommend anybody who needs to build on mingw (the main
recommendations of using binary or msvc still stand, but I realise
everybody don't want that) to look at the buildfarm and pick the same
versions as are being used there.

//Magnus