how can I call nzsql or nzload from script without disclosing sensitive password?

Question

I don't see this in the Netezza documentation.

We have lots of Linux scripts where we do this:

NZ_PASSWD_ENCRYPTED=29TY20T98=
NZ_PASSWD_CLEAR=`DecryptFunc $NZ_PASSWD_ENCRYPTED`
NzResult=$(nzsql -db ${NZ_DATABASE}  -u ${NZ_USER}  -pw $NZ_PASSWD_CLEAR -h $NZ_HOST \
              -qc "select 'hi there folks;'")

I don't like leaving the password hanging there in the command environment where it can be hacked.

Is there any kind of function to handle the password differently in the nzsql script? Did I miss something in the documentation? Or any tricks in Linux scripting I could use?

Thanks in advance....

Answer 1

Three options:

• export it to the environment.
• put it in the script
• use nzpassword

the examples are off the top of my head, I don't have an instance to test it out right now, you might need to tweak them a bit.

exporting it to the environment:

NZ_PASSWD_ENCRYPTED=29TY20T98=
export NZ_PASSWORD=`DecryptFunc $NZ_PASSWD_ENCRYPTED`
NzResult=$(nzsql -db ${NZ_DATABASE}  -u ${NZ_USER} -h $NZ_HOST \
              -qc "select 'hi there folks;'")
unset NZ_PASSWORD

Anyone who can read the environment variables set for your shell can still retrieve the password, but it won't show in the process list visible to everyone on the system

putting it in a script:

put the query in a sql file, add something like this at the top of it:

\set STOP_ON_ERROR TRUE
\echo *** connecting to MY_DATABASE ***
\connect MY_DATABASE MY_USER MY_PASSWORD
select 'hi there folks;'

Then use nzsql to execute this file. You may need to specify a login user to nzsql as well, but any user should do (even one that can only has no other rights besides log in)
Anyone who can read the sql file will be able to see the password, but I don't think it will show up anywhere else

using nzpassword

There is a tool to store passwords encrypted in a hidden ~/.nzpassword file, for later use, with:

nzpassword add -u user -pw password -host hostname

see: http://www-01.ibm.com/support/knowledgecenter/SSULQD_7.1.0/com.ibm.nz.adm.doc/c_sysadm_encrypted_passwords.html