Re: [pgAdmin][RM-2341]: Add menu option for starting PSQL

Hi Dave,

On Mon, Jun 14, 2021 at 8:05 PM Akshay Joshi <akshay(dot)joshi(at)enterprisedb(dot)com>
wrote:

> Hi Dave
>
> On Mon, Jun 14, 2021 at 7:57 PM Dave Page <dpage(at)pgadmin(dot)org> wrote:
>
>> Hi
>>
>> On Mon, Jun 14, 2021 at 3:18 PM Nikhil Mohite <
>> nikhil(dot)mohite(at)enterprisedb(dot)com> wrote:
>>
>>> Hi Akshay,
>>>
>>> I have added the following commands for invalid command check:
>>>
>>> 1. \lo_import
>>> 2. \lo_export
>>> 3. \w or write
>>> 4. \o or \output
>>> 5. \g
>>>
>>> \qecho? \ev?
>>
>> I'm very concerned we're missing more here, and will continue to miss new
>> commands that are added to psql in the future.
>>
>> I think we need to disable the feature entirely in server mode by
>> default, and probably remove the code that attempts to filter out commands.
>>
> In this case, ALLOW_SHELL_COMMAND will be not useful, should we remove it
from config.py?

>
> If I understand correctly we need to change in config.py and make
> ENABLE_PSQL = False in server mode.
>
>>
>> Then it just becomes a case of "this can be dangerous in server mode, as
>> your user will be able to run arbitrary commands on the server. Enable at
>> your own risk".
>>
>
> The above note will go into the config.py as a comment or in the
> documentation.
>

>>
>>> I am referring to the https://www.postgresql.org/docs/12/app-psql.html
>>> link for finding the commands.
>>> PFA patch V10.
>>>
>>>
>>> On Mon, Jun 14, 2021 at 6:48 PM Dave Page <dpage(at)pgadmin(dot)org> wrote:
>>>
>>>> Hi
>>>>
>>>> On Mon, Jun 14, 2021 at 2:13 PM Nikhil Mohite <
>>>> nikhil(dot)mohite(at)enterprisedb(dot)com> wrote:
>>>>
>>>>> Hi Akshay,
>>>>>
>>>>> Please find the updated patch for psql, Updated invalid command logic
>>>>> added 3 more commands in it.
>>>>> 1. \copy or \COPY
>>>>> 2. \e or \E
>>>>> 3. \ef or \EF
>>>>>
>>>>
>>>> Should they just be case insensitive?
>>>>
>>> \ commands are case sensitive.
>>>
>>
>> So what are \E and \EF? I can't see them in the pg13 docs.
>>
> These commands open the vi editor on macOS and notepad on windows for
writing the commands.

>
>>
>>>
>>>>
>>>>>
>>>>> Now user can't execute these commands if allow shell commands is set
>>>>> to False.
>>>>>
>>>>
>>>> What about \lo_import and \lo_export? I think those are potentially
>>>> dangerous too. Also, \echo.
>>>>
>>> added this as well.
>>>
>>>>
>>>>
>>>>>
>>>>> If required any changes please let me know.
>>>>>
>>>>> Regards,
>>>>> Nikhil Mohite
>>>>>
>>>>> On Mon, Jun 14, 2021 at 11:48 AM Akshay Joshi <
>>>>> akshay(dot)joshi(at)enterprisedb(dot)com> wrote:
>>>>>
>>>>>> Thanks, the patch applied.
>>>>>>
>>>>>> On Mon, Jun 14, 2021 at 10:55 AM Nikhil Mohite <
>>>>>> nikhil(dot)mohite(at)enterprisedb(dot)com> wrote:
>>>>>>
>>>>>>> Hi Team,
>>>>>>>
>>>>>>> PFA patch v8
>>>>>>>
>>>>>>> On Mon, Jun 14, 2021 at 10:25 AM Nikhil Mohite <
>>>>>>> nikhil(dot)mohite(at)enterprisedb(dot)com> wrote:
>>>>>>>
>>>>>>>> Hi Team,
>>>>>>>>
>>>>>>>> Please find the updated patch for the PSQL tool, Added check if the
>>>>>>>> Windows version is not supporting the ConPty or WinPty disable the PSQL for
>>>>>>>> that version. (PSQL option will not visible to the user).
>>>>>>>>
>>>>>>>> I have raised the issue with "pywinpty"
>>>>>>>> https://github.com/spyder-ide/pywinpty/issues/161
>>>>>>>>
>>>>>>>> Regards,
>>>>>>>> Nikhil Mohite
>>>>>>>>
>>>>>>>> On Fri, Jun 11, 2021 at 6:25 PM Dave Page <dpage(at)pgadmin(dot)org>
>>>>>>>> wrote:
>>>>>>>>
>>>>>>>>> Hi
>>>>>>>>>
>>>>>>>>> On Fri, Jun 11, 2021 at 1:45 PM Fahar Abbas <
>>>>>>>>> fahar(dot)abbas(at)enterprisedb(dot)com> wrote:
>>>>>>>>>
>>>>>>>>>> Hi Dave,
>>>>>>>>>>
>>>>>>>>>> On Fri, Jun 11, 2021 at 1:39 PM Dave Page <dpage(at)pgadmin(dot)org>
>>>>>>>>>> wrote:
>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> On Fri, Jun 11, 2021 at 9:35 AM Fahar Abbas <
>>>>>>>>>>> fahar(dot)abbas(at)enterprisedb(dot)com> wrote:
>>>>>>>>>>>
>>>>>>>>>>>> Sure Dave, I will create a new VM with the latest iso.
>>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> Not the latest. 1809 please. And if that doesn't work, 1909. We
>>>>>>>>>>> need to understand exactly what versions work and which don't.
>>>>>>>>>>>
>>>>>>>>>>> pgadmin4 is working fine on Windows 10 x64(1809).
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>> Great, thanks!
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> Do I also need to test pgadmin4 on 1909 iso?
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>> I don't think so. I'm 99% certain that what we're seeing is that
>>>>>>>>> it works on the versions of Windows that support conpty, but not the older
>>>>>>>>> ones where the pywinpty library is *supposed* to fall back to using winpty.
>>>>>>>>>
>>>>>>>>> Hopefully Nikhil can get to the bottom of the problem with the
>>>>>>>>> pywinpty folks.
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>> Nikhil is not able to reproduce the issue on Windows 10 with
>>>>>>>>>>>> the latest version. So the issue is reproducible with older versions.
>>>>>>>>>>>>
>>>>>>>>>>>> Kind Regards,
>>>>>>>>>>>> On Fri, Jun 11, 2021 at 1:28 PM Dave Page <dpage(at)pgadmin(dot)org>
>>>>>>>>>>>> wrote:
>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>> On Fri, Jun 11, 2021 at 9:25 AM Fahar Abbas <
>>>>>>>>>>>>> fahar(dot)abbas(at)enterprisedb(dot)com> wrote:
>>>>>>>>>>>>>
>>>>>>>>>>>>>> Windows 10 Enterprise 2016 LTSB.
>>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>> Hmm, I assume that is version 1607. Can you test with 1809
>>>>>>>>>>>>> please? And if that doesn't work, try 1909.
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> On Fri, Jun 11, 2021 at 1:12 PM Dave Page <dpage(at)pgadmin(dot)org>
>>>>>>>>>>>>>> wrote:
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> Hi
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> On Fri, Jun 11, 2021 at 7:44 AM Fahar Abbas <
>>>>>>>>>>>>>>> fahar(dot)abbas(at)enterprisedb(dot)com> wrote:
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>> Hi,
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>> pgadmin4 is failing on following operating systems :
>>>>>>>>>>>>>>>> Windows 2016
>>>>>>>>>>>>>>>> Windows 2012
>>>>>>>>>>>>>>>> Windows 07
>>>>>>>>>>>>>>>> Windows 2008R2
>>>>>>>>>>>>>>>> Windows 10 64
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> What version of Windows 10? I *think* conpty was introduced
>>>>>>>>>>>>>>> in 1809.
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>> pgadmin4 is working fine in following operating system:
>>>>>>>>>>>>>>>> Windows 2019
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>> On Thu, Jun 10, 2021 at 7:51 PM Dave Page <
>>>>>>>>>>>>>>>> dpage(at)pgadmin(dot)org> wrote:
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>> On Thu, Jun 10, 2021 at 2:22 PM Nikhil Mohite <
>>>>>>>>>>>>>>>>> nikhil(dot)mohite(at)enterprisedb(dot)com> wrote:
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>> Hi Dave,
>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>> On Thu, Jun 10, 2021 at 6:37 PM Dave Page <
>>>>>>>>>>>>>>>>>> dpage(at)pgadmin(dot)org> wrote:
>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>> Hi
>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>> On Thu, Jun 10, 2021 at 2:00 PM Nikhil Mohite <
>>>>>>>>>>>>>>>>>>> nikhil(dot)mohite(at)enterprisedb(dot)com> wrote:
>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>> Hi Dave,
>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>> On Thu, Jun 10, 2021 at 5:22 PM Dave Page <
>>>>>>>>>>>>>>>>>>>> dpage(at)pgadmin(dot)org> wrote:
>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>> Hi
>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>> On Thu, Jun 10, 2021 at 11:08 AM Nikhil Mohite <
>>>>>>>>>>>>>>>>>>>>> nikhil(dot)mohite(at)enterprisedb(dot)com> wrote:
>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>> Hi Dave/ Team,
>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>> We are facing an issue with winpty.dll on Windows
>>>>>>>>>>>>>>>>>>>>>> server 2016 and Windows 7(these are platforms on which we have tested).
>>>>>>>>>>>>>>>>>>>>>> Files required for winpty are present in the
>>>>>>>>>>>>>>>>>>>>>> site-packages but still, it is unable to load the winpty.dll file on these
>>>>>>>>>>>>>>>>>>>>>> specific platforms. We have tested it on Windows 10 pro and Windows server
>>>>>>>>>>>>>>>>>>>>>> 2019 and it is working fine. (Also tried building the local pywinpty but
>>>>>>>>>>>>>>>>>>>>>> unable to build it.) ref link for winPty
>>>>>>>>>>>>>>>>>>>>>> https://github.com/rprichard/winpty#:~:text=winpty%20is%20a%20Windows%20software,in%20a%20Cygwin%2FMSYS%20pty.
>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>> Does winpty.exe run, if executed from the command line
>>>>>>>>>>>>>>>>>>>>> instead of pgAdmin? If not, does dependency walker show any missing
>>>>>>>>>>>>>>>>>>>>> libraries that are required?
>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>> Not found the winpty.exe in site packages, but as per
>>>>>>>>>>>>>>>>>>>> the winpty documents "winpty-agent.exe" will start the process with a new,
>>>>>>>>>>>>>>>>>>>> hidden console window. It is not showing any error while installing the
>>>>>>>>>>>>>>>>>>>> package. I tried to use it outside the pgAdmin but still facing the same
>>>>>>>>>>>>>>>>>>>> error "Exception in import winpty DLL load failed while importing winpty:
>>>>>>>>>>>>>>>>>>>> The specified procedure could not be found."(created a separate python
>>>>>>>>>>>>>>>>>>>> environment for this)
>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>> 1. if try to run winpty-agent.exe from the command
>>>>>>>>>>>>>>>>>>>> line, it shows the user entered inputs on the same terminal, not showing
>>>>>>>>>>>>>>>>>>>> any errors.
>>>>>>>>>>>>>>>>>>>> [image: image.png]
>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>> 2. I found winpty.exe in the other installed app (Git
>>>>>>>>>>>>>>>>>>>> command line: C:\Program Files (x86)\Git\usr\bin) if try to run it through
>>>>>>>>>>>>>>>>>>>> the command line not getting any error, it is showing user entered inputs
>>>>>>>>>>>>>>>>>>>> on the same terminal. (It is not related to pgAdmin but just tried to check
>>>>>>>>>>>>>>>>>>>> winpty.exe throwing any error or not)
>>>>>>>>>>>>>>>>>>>> [image: image.png]
>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>> Hmmm, that sounds oddly similar to an issue I had with
>>>>>>>>>>>>>>>>>>> Kerberos on Windows when I was mucking around with that. I can't remember
>>>>>>>>>>>>>>>>>>> the exact details, but as a test, does the problem go away if winpty.dll is
>>>>>>>>>>>>>>>>>>> copied into the Windows system32 directory?
>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>> Tried it but still facing the same issue.
>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>> OK, I had a fiddle around, and see the same thing. I can't
>>>>>>>>>>>>>>>>> do a simple import of winpty. On 2019 it's fine. I tried various tricks
>>>>>>>>>>>>>>>>> that I've used in the past to make sure libraries are found etc.
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>> Could this be because it's trying to use the
>>>>>>>>>>>>>>>>> conpty interface in Windows 10 and above?
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>> If we can't get this to work tomorrow, I think it's fine
>>>>>>>>>>>>>>>>> to simply hide the menu options and button if the server detects it's
>>>>>>>>>>>>>>>>> running on an unsupported version of Windows. Of course, this should be
>>>>>>>>>>>>>>>>> documented. We also need to make sure we know *exactly* what versions it
>>>>>>>>>>>>>>>>> does and does not work on.
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>> --
>>>>>>>>>>>>>>>>> Dave Page
>>>>>>>>>>>>>>>>> Blog: https://pgsnake.blogspot.com
>>>>>>>>>>>>>>>>> Twitter: @pgsnake
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>> EDB: https://www.enterprisedb.com
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>> --
>>>>>>>>>>>>>>>> Fahar Abbas
>>>>>>>>>>>>>>>> EnterpriseDB Corporation
>>>>>>>>>>>>>>>> Phone Office: +92-51-835-8874
>>>>>>>>>>>>>>>> Phone Direct: +92-51-8466803
>>>>>>>>>>>>>>>> Mobile: +92-333-5409707
>>>>>>>>>>>>>>>> Skype ID: *live:fahar.abbas*
>>>>>>>>>>>>>>>> Website: www.enterprisedb.com
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> --
>>>>>>>>>>>>>>> Dave Page
>>>>>>>>>>>>>>> Blog: https://pgsnake.blogspot.com
>>>>>>>>>>>>>>> Twitter: @pgsnake
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> EDB: https://www.enterprisedb.com
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> --
>>>>>>>>>>>>>> Fahar Abbas
>>>>>>>>>>>>>> EnterpriseDB Corporation
>>>>>>>>>>>>>> Phone Office: +92-51-835-8874
>>>>>>>>>>>>>> Phone Direct: +92-51-8466803
>>>>>>>>>>>>>> Mobile: +92-333-5409707
>>>>>>>>>>>>>> Skype ID: *live:fahar.abbas*
>>>>>>>>>>>>>> Website: www.enterprisedb.com
>>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>> --
>>>>>>>>>>>>> Dave Page
>>>>>>>>>>>>> Blog: https://pgsnake.blogspot.com
>>>>>>>>>>>>> Twitter: @pgsnake
>>>>>>>>>>>>>
>>>>>>>>>>>>> EDB: https://www.enterprisedb.com
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>> --
>>>>>>>>>>>> Fahar Abbas
>>>>>>>>>>>> EnterpriseDB Corporation
>>>>>>>>>>>> Phone Office: +92-51-835-8874
>>>>>>>>>>>> Phone Direct: +92-51-8466803
>>>>>>>>>>>> Mobile: +92-333-5409707
>>>>>>>>>>>> Skype ID: *live:fahar.abbas*
>>>>>>>>>>>> Website: www.enterprisedb.com
>>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> --
>>>>>>>>>>> Dave Page
>>>>>>>>>>> Blog: https://pgsnake.blogspot.com
>>>>>>>>>>> Twitter: @pgsnake
>>>>>>>>>>>
>>>>>>>>>>> EDB: https://www.enterprisedb.com
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> --
>>>>>>>>>> Fahar Abbas
>>>>>>>>>> EnterpriseDB Corporation
>>>>>>>>>> Phone Office: +92-51-835-8874
>>>>>>>>>> Phone Direct: +92-51-8466803
>>>>>>>>>> Mobile: +92-333-5409707
>>>>>>>>>> Skype ID: *live:fahar.abbas*
>>>>>>>>>> Website: www.enterprisedb.com
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> --
>>>>>>>>> Dave Page
>>>>>>>>> Blog: https://pgsnake.blogspot.com
>>>>>>>>> Twitter: @pgsnake
>>>>>>>>>
>>>>>>>>> EDB: https://www.enterprisedb.com
>>>>>>>>>
>>>>>>>>>
>>>>>>
>>>>>> --
>>>>>> *Thanks & Regards*
>>>>>> *Akshay Joshi*
>>>>>> *pgAdmin Hacker | Principal Software Architect*
>>>>>> *EDB Postgres <http://edbpostgres.com>*
>>>>>>
>>>>>> *Mobile: +91 976-788-8246*
>>>>>>
>>>>>
>>>>
>>>> --
>>>> Dave Page
>>>> Blog: https://pgsnake.blogspot.com
>>>> Twitter: @pgsnake
>>>>
>>>> EDB: https://www.enterprisedb.com
>>>>
>>>> Regards,
>>> Nikhil Mohite
>>>
>>
>>
>> --
>> Dave Page
>> Blog: https://pgsnake.blogspot.com
>> Twitter: @pgsnake
>>
>> EDB: https://www.enterprisedb.com
>>
>>
>
> --
> *Thanks & Regards*
> *Akshay Joshi*
> *pgAdmin Hacker | Principal Software Architect*
> *EDB Postgres <http://edbpostgres.com>*
>
> *Mobile: +91 976-788-8246*
>
Regards,
Nikhil Mohite