0

I want to catch the PostGres query results that gets executed in the remote server (passwordless-ssh) via expect command that is used to supply the password for the given PostGres user. Actually, there are two versions of our application, the older version does not require Postgres password where as the new version requires it. So the Testcase has to handle that

In older version (which does not require psql password) the cmd string to execute the query is very simple:

cmd = 'ssh ' + db_ip + ' "psql -h localhost -p 5434 -U perfmon -d network -c \\\"select count(*) from crm_customer_device_info;\\\""'

But for the new version (since password is enabled for psql), I have tried the following approaches, but timeout occurs

Approach 1: executing the query when the prompt appears

#! /usr/bin/python

import subprocess

def execute(cmd):
    proc = subprocess.Popen(cmd, shell=True, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
    _output, _error = proc.communicate()
    _code = proc.returncode
    return _output, _error, _code

def executeQuery(db_ip, db_user, db_name, db_pass, sql_query):
    cmd = '''/usr/bin/expect -c 'spawn -noecho ssh -q {0} "psql -h localhost -p 5434 -U {1} -d {2}"
           expect {{
             -nocase "Password*" {{
                send "{3}\r"
                exp_continue
             }}
             "=# " {{
                send "{4}"
                set var $expect_out(buffer);
                send "\q\n"
              }}
           }}
           '
       '''.format(db_ip, db_user, db_name, db_pass, sql_query)
    return execute(cmd)

db_ip = "192.168.0.1"
db_user = "perfmon"
db_name = "network"
db_pass = "xxx"
sql_query = "select count(*) from crm_customer_device_info;"

_output, _error, return_code = executeQuery(db_ip, db_user, db_name, db_pass, sql_query)

Approach-2: executing the query using postgres -c option and handling timeout scenarios

def executeQuery(db_ip, db_user, db_name, db_pass, sql_query):
    cmd = '''/usr/bin/expect -c 'spawn ssh {o} "psql -h localhost -p 5434 -U {1} -d {2} -c \\\'{4}\\\' "
           expect {{
             -nocase "Password*" {{
                send -- "{3}\r"
                exp_continue
             }}
             -nocase "permission denied" {{
               exit 4
             }}
             -nocase "timed out" {{
               exit 2
             }}
             timeout {{
               exit 1
             }}
           }}
           '
       '''.format(db_ip, db_user, db_name, db_pass, sql_query)
    return execute(cmd)

When I execute the below expect script directly on the server where psql is installed instead of remote ssh, then I manage to get output:

spawn -noecho psql -h localhost -p 5434 -U user -d db;
               expect {
                 "^Password*" {
                   send -- "passwd\r"
                   exp_continue
                }
                "*=# " {
                send -- "select now();\r\n"
                set var $expect_out(buffer);

                send "\\q\r\n"
                exp_continue
               }
               }; #puts $var; #interact;
Community
  • 1
  • 1
Ibrahim Quraish
  • 3,889
  • 2
  • 31
  • 39
  • Why are you connecting from you python script to the psql-client instead of the PostgreSQL-database server? I can't remember that I've ever seen a setup like this, so what's the purpose? – Frank Heikens Jan 14 '21 at 19:24
  • Its all test cases written in Python and there are other databases (redis, impala) and other non-db testcases as well existing in the same file. – Ibrahim Quraish Jan 14 '21 at 19:30
  • Ok, but why don't you / can't you connect directly to the database server? Or are you testing the functionality of the psql-client? – Frank Heikens Jan 14 '21 at 19:43
  • `Frank Heikens` yes you are right? I have just given here the snippet of the TC, but it has to do much more – Ibrahim Quraish Jan 14 '21 at 20:09
  • i know nothing about postgre so cannot give it a try. is the question postgre specific? – pynexj Jan 15 '21 at 07:17
  • `pynexj` You can assume that it can be any database. I could manage to execute the query inside expect script, Now, I just need to get the results from there out to a python variable – Ibrahim Quraish Jan 15 '21 at 10:23
  • then just read the output from pepen() and parse it and save the expected part in a python var. – pynexj Jan 15 '21 at 10:35
  • `pynexj` the output I got is with expect script (not python) and when it is directly executed on the PostGreSQL server. But I have to make a ssh to this server and execute the query and collect the result. This is where I am facing challenges. – Ibrahim Quraish Jan 15 '21 at 18:38

0 Answers0