WbSysExec: support set system environment variable

[Franz Mayer]

Hi Thomas,

today, I played around with WbSysExec to import a dump using psql.

It would work, but for psql you need to set the system environment variable PGPASSWORD (see https://www.postgresql.org/docs/9.5/static/libpq-envars.html) in order to provide the password. The default password file didn't work on my Win machine; furthermore the usage of these imports should be as easy and hassle free, so it would be nice to pass the password by just setting PGPASSWORD.

When typing on cmd everything works:
set PGPASSWORD=my_pwd
psql.exe -d sapient -U postgres -f C:/projects/backup.sql -L C:/projects/psql.log -w

I tried following (using a simple command to verify it works when it has been logged the version):
-- TRY 1
WbSysExec -program='cmd.exe' -argument='/c' -argument='set PGPASSWORD=my_pwd/n';
WbSysExec -dir=ZZZPATH2PSQLZZZ
          -program=psql.exe
          -argument='-d sapient -U postgres -c "select version()" -L C:/projects/JLIW/db_dev_636/psql.log -w';

-- TRY 2
WbSysExec -dir=ZZZPATH2PSQLZZZ
          -program='cmd.exe'
          -argument='/c'
          -argument='set PGPASSWORD=my_pwd\n psql.exe -d sapient -U postgres -c "select version()" -L C:/projects/JLIW/db_dev_636/psql.log -w';

It would be nice to pass system variables to WbSysExec, for example:
WbSysExec -sysvar=PGPASSWORD=my_pwd

I tried this in class WbSysExec, but just setting the system variable by System.setProperty does not work.

Have you any idea? Would it be possible to realize this feature?

Thanks in advance,
Franz

[Thomas Kellerer]

> When typing on cmd everything works:
> set PGPASSWORD=my_pwd
> psql.exe -d sapient -U postgres -f C:/projects/backup.sql -L C:/projects/psql.log -w
>
> I tried following (using a simple command to verify it works when it has been logged the version):
> -- TRY 1
> WbSysExec -program='cmd.exe' -argument='/c' -argument='set PGPASSWORD=my_pwd/n';
> WbSysExec -dir=ZZZPATH2PSQLZZZ
>           -program=psql.exe
>           -argument='-d sapient -U postgres -c "select version()" -L C:/projects/JLIW/db_dev_636/psql.log -w';

The above can't work because that will create two processes independent from each other.

> It would be nice to pass system variables to WbSysExec, for example:
> WbSysExec -sysvar=PGPASSWORD=my_pwd

That should be possible as ProcessBuilder supports defining environment variables for the newly started process.

[Franz Mayer]

Hi Thomas,

thanks for the fix. With respect of a special call of WbSysExec it works.

This works:
WbSysExec -dir=ZZZPATH2PSQLZZZ
          -program=psql.exe
          -env='PGPASSWORD=my_pwd'
          -argument='-d'
          -argument='sapient'
          -argument='-U'
          -argument='postgres'
          -argument='-c'
          -argument='select version()'
          -argument='-L'
          -argument='C:/projects/JLIW/db_dev_636/psql.log'
          -argument='-w';

This DOES NOT WORK:
WbSysExec -dir=ZZZPATH2PSQLZZZ
          -program=psql.exe
          -env='PGPASSWORD=my_pwd'
          -argument='-d sapient'
          -argument='-U postgres'
          -argument='-c select version()'
          -argument='-L C:/projects/psql.log'
          -argument='-w';

The error messages says:
psql: FATAL:  Passwort-Authentifizierung für Benutzer » postgres« fehlgeschlagen

Please note the whitespace before username postgres.

Neither DOES THESE WORK:
-- Error message: psql: FATAL:  Passwort-Authentifizierung für Benutzer »version() -L C:/projects/JLIW/db_dev_636/psql.log -w« fehlgeschlagen
WbSysExec -dir=ZZZPATH2PSQLZZZ
          -program=psql.exe
          -env='PGPASSWORD=mypwd'
          -argument='-d sapient -U postgres -c "select version()" -L C:/projects/psql.log -w';

-- Error message: psql: FATAL:  Passwort-Authentifizierung für Benutzer »Franz.M« fehlgeschlagen (Note: "Franz.M" is the OS-User)
WbSysExec -dir=ZZZPATH2PSQLZZZ
          -program=psql.exe
          -env='PGPASSWORD=mypwd'
          -argument='-d sapient -U postgres -c exit -L C:/projects/JLIW/db_dev_636/psql.log -w';

For me, above working WbSysExec call is OK, but for convenience it would be maybe good if other calls are also working - or at least having a hint for why they are not working.

Thanks and best regards,
Franz

[Franz Mayer]

One more thing:

when I call psql by WbSysExec with my import file Workbench always stops after 1.484 (psql-) commands; BUT - in the time of writing this - it turned out, after closing Workbench the import (psql-call) is working further and psql log is written and the import has been done completely. When I call it from command line however it works.

Is there any restriction for ResultLogger? Or might there be another problem?

My WbSysExec call:
-- @WbTag (IMPORT) Import dump using PSQL

WbSysExec -dir=ZZZPATH2PSQLZZZ
          -program=psql.exe
          -env='PGPASSWORD=mypwd'

          -argument='-d'
          -argument='sapient'
          -argument='-U'
          -argument='postgres'

          -argument='-f'
          -argument='C:/projects/sapient_backup.sql'
          -argument='-L'
          -argument='C:/projects/psql.log'
          -argument='-w';

Relevant log entries:
2018-01-10 11:07:04 INFO  ResourceMgr.getResources() Setting default locale to: en
2018-01-10 11:07:04 INFO  WbManager.readParameters() Starting SQL Workbench/J, Build 2.2.1 (build at 10.01.2018 10:32, rev 27519)
2018-01-10 11:07:04 INFO  WbManager.readParameters() Java version=1.8.0_141, java.home=C:\Program Files\Java\jre1.8.0_141, vendor=Oracle Corporation, name=Java HotSpot(TM) 64-Bit Server VM
2018-01-10 11:07:04 INFO  WbManager.readParameters() Operating System=Windows 10, version=10.0, platform=amd64

If you need more information please contact me!

Best regards,

Franz


On Wednesday, January 10, 2018 at 12:12:54 PM UTC+1, Franz Mayer wrote:

[Thomas Kellerer]

I have to investigate this.

I guess it has something to do how ProcessBuider parses/uses the supplied arguments.
(And the mixing of quotes probably doesn't help either)

WbSysExec was never intended for such complicated calls.
I rather expected that anything that goes beyond a simple program call will be put into a batch file/shell script instead.

Thomas

> It would work, but for psql you need to set the system environment variable PGPASSWORD (see https://www.postgresql.org/docs/9.5/static/libpq-envars.html <https://www.postgresql.org/docs/9.5/static/libpq-envars.html>) in order to provide the password. The default password file didn't work on my Win machine; furthermore the usage of these imports should be as easy and hassle free, so it would be nice to pass the password by just setting PGPASSWORD.

[Franz Mayer]

Hi Thomas,

I understand your arguments.

So I tried to put the call into a separate file. By passing environment variable the following would also be possible:

WbSysExec -program=../psql_import.bat
          -env='PGPASSWORD=ZZZSYSTEM_DBA_USRPWZZZ'
          -env='PG_EXE_PATH=ZZZPATH2PSQLZZZ';

psql_import.bat has only one line:
%PG_EXE_PATH%/psql.exe -d sapient -U postgres -f output/sapient_backup.sql -L output/psql.log -w

The call is working, but it has the same effect as when calling it directly via WbSysExec: after around 1400 commands it halts and it'll only finish its work, when closing SQLWorkbench. It's not because of too many or too long log messages as I tried the same with -q flag (quite psql execution).

Best regards,
Franz

[Thomas Kellerer]

Hmm, this is strange.

Usually when the process started through ProcessBuilders seems to "hang" it is caused by that program expecting some user input.

But if psql was already running for some time, I can't think of a reason it suddenly starts waiting for user input.

I wonder if it has something to do with reading standard out through ProcessBuilder.

Does this change if you redirect all output from psql to some file?

Something like:

%PG_EXE_PATH%/psql.exe -d sapient -U postgres -f output/sapient_backup.sql -L output/psql.log -w > out.txt 2> err.txt

Thomas

> >     It would work, but for psql you need to set the system environment variable PGPASSWORD (see https://www.postgresql.org/docs/9.5/static/libpq-envars.html <https://www.postgresql.org/docs/9.5/static/libpq-envars.html> <https://www.postgresql.org/docs/9.5/static/libpq-envars.html <https://www.postgresql.org/docs/9.5/static/libpq-envars.html>>) in order to provide the password. The default password file didn't work on my Win machine; furthermore the usage of these imports should be as easy and hassle free, so it would be nice to pass the password by just setting PGPASSWORD.

> >
> >     When typing on cmd everything works:
> >     set PGPASSWORD=my_pwd
> >     psql.exe -d sapient -U postgres -f C:/projects/backup.sql -L C:/projects/psql.log -w
> >
> >     I tried following (using a simple command to verify it works when it has been logged the version):
> >     -- TRY 1
> >     WbSysExec -program='cmd.exe' -argument='/c' -argument='set PGPASSWORD=my_pwd/n';
> >     WbSysExec -dir=ZZZPATH2PSQLZZZ
> >                -program=psql.exe
> >                -argument='-d sapient -U postgres -c "select version()" -L C:/projects/JLIW/db_dev_636/psql.log -w';
> >
> >     -- TRY 2
> >     WbSysExec -dir=ZZZPATH2PSQLZZZ
> >                -program='cmd.exe'
> >                -argument='/c'
> >                -argument='set PGPASSWORD=my_pwd\n psql.exe -d sapient -U postgres -c "select version()" -L C:/projects/JLIW/db_dev_636/psql.log -w';
> >
> >     It would be nice to pass system variables to WbSysExec, for example:
> >     WbSysExec -sysvar=PGPASSWORD=my_pwd
> >
> >     I tried this in class WbSysExec, but just setting the system variable by System.setProperty does not work.
> >
> >     Have you any idea? Would it be possible to realize this feature?
> >
> >     Thanks in advance,
> >     Franz
>

> --
> You received this message because you are subscribed to the Google Groups "SQL Workbench/J - DBMS independent SQL tool" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sql-workbenc...@googlegroups.com <mailto:sql-workbenc...@googlegroups.com>.
> For more options, visit https://groups.google.com/d/optout.