Re: permission issues with PostgreSQL 9.2 EnterpriseDB one-click installer on windows 7 causes initcluster to fail

Thanks for fixing it!  I will be looking out for the change in later release=
s.

> On Jan 21, 2014, at 3:51 AM, Sandeep Thakkar <sandeep.thakkar@enterprisedb=
.com> wrote:
>=20
> Thanks. This will be taken care of in the next updates. But, please note t=
hat all this ACL changes will be done only when "--enable_acledit 1" switch i=
s provided on the command line when the installer is run.
>=20
>=20
>> On Wed, Dec 18, 2013 at 8:31 PM, David Fleischhauer <dgfleisch@gmail.com>=
 wrote:
>>=20
>>=20
>>=20
>>> On Wed, Dec 18, 2013 at 2:58 AM, Sandeep Thakkar <sandeep.thakkar@enterp=
risedb.com> wrote:
>>>=20
>>>=20
>>>=20
>>>> On Mon, Dec 16, 2013 at 9:26 PM, David Fleischhauer <dgfleisch@gmail.co=
m> wrote:
>>>> I have added my comments inline as well:
>>>>=20
>>>>=20
>>>>> On Mon, Dec 16, 2013 at 1:35 AM, Sandeep Thakkar <sandeep.thakkar@ente=
rprisedb.com> wrote:
>>>>> Hi David
>>>>>=20
>>>>> Thanks for checking and reporting this. Before we proceed, we would li=
ke some more information. Please see the comments inline.
>>>>>=20
>>>>>=20
>>>>>> On Fri, Dec 13, 2013 at 11:00 PM, david fleischhauer <dgfleisch@gmail=
.com> wrote:
>>>>>> I have noted two bugs dealing with permissions with the EnterpriseDB o=
ne-click installer.  Both are similar cases:
>>>>>>=20
>>>>>> 1.  Permissions are not given to the PostgreSQL bin directory.  If I t=
ry to install postgres on a drive with limited permissions (for my test, onl=
y the 'Administrators' group had permissions), I get an error saying "libint=
l-8.dll" is missing.  That file is located in the postgres bin directory.  T=
he issue is that your initcluster.vbs script only gives permissions for the d=
ata directory and the parent directories of the data directory.  In order fo=
r postgres to install correctly, permissions need to be added for the bin di=
rectory.
>>>>>=20
>>>>> initcluster.vbs is supposed to deal only with the cluster directory an=
d it's permissions. Could you list the ACL on the E:\ drive please? Command l=
ine output will do. (icacls E:\)=20
>>>>=20
>>>>=20
>>>>     E:\>icacls E:\
>>>>     E:\ BUILTIN\Administrators:(OI)(CI)(F)
>>>>=20
>>>>     Successfully processed 1 files; Failed processing 0 files
>>>>=20
>>>>     I understand your reasoning that initcluster.vbs should only deal w=
ith permissions related to the data directory, but in order to create the da=
ta cluster, you need to run initdb which is in the bin directory.  I am assu=
ming my issue is related to the initdb command using dll libraries in the bi=
n directory that it cannot find because the current logged in user does not h=
ave read rights to the bin directory.  Giving read rights to the bin directo=
ry before running initcluster.vbs fixes my issue.  This issue affects the de=
fault GUI installer too.  The GUI installer never sets permissions for the b=
in directory.  One thing to note, all of my testing has been done with a dat=
a directory that is not a sibling of the bin directory.  I do not think this=
 is necessary to reproduce the problem but I have done that to ensure the ic=
acls commands on the data directory does not interfere with the permissions o=
f the bin directory and mask the problem somehow.
>>>=20
>>> You mean your installation location and the data directory location was o=
n different drives? Or just the different paths in E:\?=20
>>=20
>>=20
>> They can be on different drive if you want them to be, or they can just b=
e on different paths in the same directory.  For instance:
>>=20
>>            Postgres Bin Dir:      E:\dir\PostgreSQL\9.2\bin
>>            Postgres Data Dir:    E:\dir\data
>>=20
>> the point is to get a situation where initcluster.vbs does not give permi=
ssions to bin directory's parent (i.e. E:\dir\PostgreSQL\9.2).  initcluster.=
vbs would only give permissions to that directory if the data dir is a sibli=
ng of the bin directory (i.e. E:\dir\PostgreSQL\9.2\data)
>>=20
>> Here is a list of steps to follow to recreate the issue:
>>=20
>> 1.  Empty out your E:\ drive so that there is nothing left in it.
>> 2.  Right-click on the E:\ drive in an explorer window and click "Propert=
ies"
>> 3.  Click on the "Security" tab
>> 4.  Click "Edit..."
>> 5.  Click "Remove" for all "Group or User Names" except for "Administrato=
rs (<cpuname>\Administrators)".  If Administrators is not listed,, add it.
>> 6.  Click "Apply" and close out of all windows
>> 7.  Double click on the postgres installer =3D> postgresql-9.2.5-1-window=
s-x64.exe.  Make sure the installer is version 9.2.5 or later
>> 8.  When prompted for the installation directory, type in:  E:\dir\Postgr=
eSQL\9.2\
>> 9.  When prompted for the data directory, type in: E:\dir\data
>> 10. Answer all the other questions.  The installation should fail due to a=
 libintl-8.dll file not being found (issue #1). The file should be located i=
n the postgres bin directory
>> 11. Uninstall PostgreSQL 9.2
>> 12. Repeat steps 1-6
>> 13. Run the following commands: =20
>>               mkdir "E:\dir\PostgreSQL\9.2\"
>>               icacls "E:\dir\PostgreSQL\9.2\" /grant "NT AUTHORITY\Networ=
kService":"(OI)(CI)RX"
>>               icacls "E:\dir\PostgreSQL\9.2\" /grant "<logged-in user>":"=
(OI)(CI)RX"
>> 14. Repeat steps 7-10.  The installation should fail again, but give you a=
 different error (issue #2).
>> 15. Repeat steps 2 and 3.  You should only see "Administrators (<cpuname>=
\Administrators)" under "Group or User Names".  Notice that no permissions w=
ere granted for the "NT AUTHORITY\NetworkService" or the logged in user.  If=
 you go to "E:\dir" and look at its security tab under properties, you will s=
ee the logged in user present under "Group or User Names", so the icacls com=
mand succeeded for "E:\dir".
>>=20
>> Now go into your temp directory and look at install-postgresql.log.  You w=
ill see the following error:
>>=20
>>=20
>>     The database cluster will be initialized with locale "English_United S=
tates.1252".
>>     The default text search configuration will be set to "english".
>>=20
>>     fixing permissions on existing directory E:/dir/data ... ok
>>     creating subdirectories ... initdb: could not create directory "E:/di=
r": File exists
>>     initdb: removing contents of data directory "E:/dir/data"
>>=20
>>     Called Die(Failed to initialise the database cluster with initdb)...
>>     Failed to initialise the database cluster with initdb
>>=20
>> You will also see a little bit further up:
>>=20
>>     Executing icacls to ensure the <logged-in user> account can read the p=
ath E:
>>         Executing batch file 'radAC52112.bat'...
>>         processed file: E:
>>=20
>>     Successfully processed 1 files; Failed processing 0 files
>>=20
>> Which leads you to believe it correctly gave permissions to the E: drive,=
 but as we confirmed earlier, it did not.  If you follow these steps, hopefu=
lly you will be able to recreate my issues.
>> =20
>>>>>>=20
>>>>>> 2.  Permissions are not properly given to the PostgreSQL data directo=
ry's root drive in PostgreSQL version 9.2.5 and up.  In PostgreSQL 9.2.4 the=
re is a comment in the initcluster.vbs script saying:
>>>>>=20
>>>>> Yes, In 9.2.5, initcluster script has undergone some changes because l=
ot of people did not want the initcluster to change the permissions on the c=
omplete parent path of the data directory as icacls would take a lot of time=
 to do this if that path contains huge number of files. Hence, from 9.2.5, b=
y default the initcluster will change the permissions of just the 'data' dir=
ectory. If user wants the ACL to be edited on the complete path, then he can=
 do it with a new command line option "--enable_acledit 1".
>>>>=20
>>>> Yeah, the new flag is a good idea and changing the script is ok, its ju=
st when it was changed, a really weird corner case that was explicitly handl=
ed before is no longer being handled.
>>>=20
>>> I executed the following command:
>>> icacls  "E:" /grant "NT AUTHORITY\NetworkService":(NP)(RX)=20
>>>=20
>>> and this command worked fine. I used the double quotes around the drive a=
nd it does not end with slash. So, my guess is that the comment in the scrip=
t is not relevant anymore? and the issue#2 also appears because of the permi=
ssions?
>>=20
>> If you open up a cmd windows and type icacls  "E:" /grant "NT AUTHORITY\N=
etworkService":(NP)(RX), it will pass and grant you the correct permissions.=
  How to get the error is to remove all permissions from the drive except fo=
r the administrators group and run the DoCmd(strCmd) method in VB.  Here is t=
he method for reference:
>>=20
>>     ' Execute a command
>>     Function DoCmd(strCmd)
>>         Dim objBatchFile
>>         Set objBatchFile =3D objTempFolder.CreateTextFile(strBatchFile, T=
rue)
>>         objBatchFile.WriteLine "@ECHO OFF"
>>         objBatchFile.WriteLine strCmd & " > """ & strOutputFile & """ 2>&=
1"
>>         objBatchFile.WriteLine "EXIT /B %ERRORLEVEL%"
>>         objBatchFile.Close
>>         WScript.Echo "    Executing batch file '" & strBatchFile & "'..."=

>>         DoCmd =3D objShell.Run(objTempFolder.Path & "\" & strBatchFile, 0=
, True)
>>         If objFso.FileExists(objTempFolder.Path & "\" & strBatchFile) =3D=
 True Then
>>             objFso.DeleteFile objTempFolder.Path & "\" & strBatchFile, Tr=
ue
>>         Else
>>             WScript.Echo "    Batch file '" & strBatchFile & "' does not e=
xist..."
>>         End If
>>         If objFso.FileExists(strOutputFile) =3D True Then
>>             Dim objOutputFile
>>             Set objOutputFile =3D objFso.OpenTextFile(strOutputFile, ForR=
eading)
>>             WScript.Echo "    " & objOutputFile.ReadAll
>>             objOutputFile.Close
>>             objFso.DeleteFile strOutputFile, True
>>         Else
>>             WScript.Echo "    Output file does not exists..."
>>         End If
>>     End Function
>>=20
>> It first creates a batch file and writes three lines to it:
>>=20
>>     @echo off
>>     icacls ...
>>     EXIT /B %ERRORLEVEL%
>>=20
>> then it calls:
>>=20
>>     DoCmd =3D objShell.Run(objTempFolder.Path & "\" & strBatchFile, 0, Tr=
ue)
>>=20
>> if you pass in 'icacls  "E:" /grant "NT AUTHORITY\NetworkService":(NP)(RX=
)' to the DoCmd with an empty E:\ drive and the correct permissions, you wil=
l see that the icacls command is executed and outputs that the command passe=
s.  However, if you go back and look at the permissions on the E:\ drive, yo=
u will see that the permissions actually did not get set.  I believe this is=
 an issue with vb itself as the icacls command should not pass when it obvio=
usly is failing.
>>=20
>>>>=20
>>>>>> =20
>>>>>>=20
>>>>>>     ' Drive letter must not be surrounded by double-quotes and ends w=
ith slash (\)
>>>>>>     ' "icacls" fails on the drives with (NP) flag
>>>>>>=20
>>>>>> In version 9.2.5, the initcluster.vbs script has been changed and the=
 above corner case is not taken care of.  Again, to reproduce this issue, I s=
et the E drive of my machine to only give permissions to the 'Administrators=
' group and my E drive was completely empty.  I also had to fixed issue #1 t=
o get this issue to pop up.  The error I am getting from the logfile is:
>>>>>>=20
>>>>>>     The database cluster will be initialized with locale "English_Uni=
ted States.1252".
>>>>>>     The default text search configuration will be set to "english".
>>>>>>=20
>>>>>>     fixing permissions on existing directory E:/dir/data ... ok
>>>>>>     creating subdirectories ... initdb: could not create directory "E=
:/dir": File exists
>>>>>>     initdb: removing contents of data directory "E:/dir/data"
>>>>>>=20
>>>>>>     Called Die(Failed to initialise the database cluster with initdb)=
...
>>>>>>     Failed to initialise the database cluster with initdb
>>>>>>=20
>>>>>> Here are the slight differences between the icacls command to grant p=
ermissions to the root drive in 9.2.4 and 9.2.5:
>>>>>>=20
>>>>>>     9.2.4:    icacls E:\ /grant ...
>>>>>>     9.2.5:    icacls "E:" /grant ...
>>>>>>=20
>>>>>> As your comment shows, having quotes around 'E:' and also not includi=
ng the slash will cause an issue, both of which are not taken care of in the=
 9.2.5 icacls command.
>>>>>=20
>>>>> Sure. Will look into this. You ran into this issue during the installa=
tion? Or when you run the initcluster script manually?
>>>>=20
>>>>  I can reproduce this issue both ways.  If you run the GUI installer wi=
th the E: drive completely empty and with the administrators group as the on=
ly group with permissions as I have given in the 'icacls E:\' command, you w=
ill first run into issue #1.  To get issue #2, what I have done is explicitl=
y create the postgres install directory if it does not exist and set read an=
d write permissions on the directory.  I do this before running the installe=
r.
>>>>=20
>>>> I can run the same exact icacls command initcluster.vbs runs, and it pa=
sses with flying colors (for both 9.2.4 and 9.2.5).  I even have saved off t=
he radxxxx.bat file and have run that and it still passes with flying colors=
.  The issue crops up when running the batch file within vb.  Oddly enough, i=
f I run the vb script a second time (not through the installer), it will giv=
e the correct permissions.  So I think there is a bug in vb that initcluster=
.vbs is exploiting with the 'icacls "E:" ...' command.  I have created a vb s=
cript that only has the doCmd(...) method in it and I pass in my own icacls c=
ommand and I still get this issue.
>>>>>>=20
>>>>>> Hopefully I have clearly stated the issues.  If these issues have not=
 been reported and there are any issues understanding what I wrote, feel fre=
e to reply to this email.
>>>>>>=20
>>>>>> thanks,
>>>>>> David
>>>>>=20
>>>>>=20
>>>>>=20
>>>>> --=20
>>>>> Sandeep Thakkar
>>>=20
>>>=20
>>>=20
>>> --=20
>>> Sandeep Thakkar
>=20
>=20
>=20
> --=20
> Sandeep Thakkar
>=20