84adam/lnd-postgres-kv-decoder.py

## lnd-postgres-kv-decoder.py
# lnd-postgres-kv-decoder.py

from decouple import config
import psycopg2
import psycopg2.extras
import pandas as pd
import time

tables = ['channeldb_kv', 'decayedlogdb_kv', 'macaroondb_kv',
          'towerclientdb_kv', 'towerserverdb_kv', 'walletdb_kv']

def bytea2bytes(value, cur):
    """
    Typecaster for converting bytea data to bytes
    """
    m = psycopg2.BINARY(value, cur)
    if m is not None:
        return m.tobytes()

# register the BYTE2BYTES type for psycopg2
BYTEA2BYTES = psycopg2.extensions.new_type(psycopg2.BINARY.values, 'BYTEA2BYTES', bytea2bytes)
psycopg2.extensions.register_type(BYTEA2BYTES)

def lnddb_conn():
    """
    Connect to Postgres Database using psycopg2
    - credentials should be stored in an .env file for python-decouple to read from
    """
    dbname = config('DB_NAME_LND')
    user = config('DB_USER_LND')
    password = config('DB_PASSWORD_LND')
    host = config('DB_HOST_LND')
    port = config('DB_PORT_LND', cast=int)
    try:
        conn = psycopg2.connect(dbname=dbname,
                                user=user,
                                password=password,
                                host=host,
                                port=port,
                                sslmode='require'
                               )
    except (Exception, psycopg2.Error) as e:
        print(f"Error while connecting to DB:\n", e)
        conn = None
    return conn

def select_all(conn, table_name):
    """
    Select all records from a given table in the Postgres database
    """
    dict_cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
    dict_cur.execute(f"select key::bytea, value::bytea, parent_id, id, sequence from {table_name} "
                      "where value is not null ");
    rec = dict_cur.fetchall()
    dict_cur.close()
    return rec

def single_table(table_name):
    """
    Select all records from a single table and read them into a Pandas dataframe
    """
    conn = lnddb_conn()
    t = table_name
    rec = select_all(conn, t)
    col_names = ['key', 'value', 'parent_id', 'id', 'sequence']
    df = pd.DataFrame([[row[col] for col in col_names] for row in rec], columns=col_names)
    conn.close()
    return df

def is_alpha_dash(w):
    """
    Check whether a given value can be decoded into an alpha string including dashes
    """
    if not isinstance(w, str):
        try:
            w = w.decode("utf-8")
        except:
            pass
    alphanumdash = ['a','b','c','d','e','f','g','h','i',
                    'j','k','l','m','n','o','p','q','r',
                    's','t','u','v','w','x','y','z','-']
    chars = []
    for c in w:
        if c in alphanumdash:
            chars.append(c)
    chars_w = ''.join(chars)
    if len(chars_w) == len(w):
        return True, chars_w
    else:
        return False, w

def is_alphanum_dash(w):
    """
    Check whether a given value can be decoded into an alphanumeric string including dashes
    """
    if not isinstance(w, str):
        try:
            w = w.decode("utf-8")
        except:
            pass
    alphanumdash = ['a','b','c','d','e','f','g','h','i',
                    'j','k','l','m','n','o','p','q','r',
                    's','t','u','v','w','x','y','z','0',
                    '1','2','3','4','5','6','7','8','9',
                    '-']
    chars = []
    for c in w:
        if c in alphanumdash:
            chars.append(c)
    chars_w = ''.join(chars)
    if len(chars_w) == len(w):
        return True, chars_w
    else:
        return False, w

def main():
    """
    Read data from all tables and print out human-readable, decoded keys and values
    """
    conn = lnddb_conn()
    for t in tables:
        print(f"fetching data from {t}...")
        rec = select_all(conn, t)
        col_names = ['key', 'value', 'parent_id', 'id', 'sequence']
        df = pd.DataFrame([[row[col] for col in col_names] for row in rec], columns=col_names)
        column_names = ['key', 'value']
        for col in column_names:
            unique_entries = []
            for k in df[col]:
                if k != None and len(k) > 0:
                    alphanum_dash, k2 = is_alphanum_dash(k)
                    if alphanum_dash:
                        alpha_dash, k3 = is_alpha_dash(k2)
                        if alpha_dash:
                            if len(k3) > 0:
                                if k3 not in unique_entries:
                                    unique_entries.append(k3)
                    else:
                        k4 = binascii.hexlify(k).decode('utf-8')
                        alphanum_dahsh, k5 = is_alphanum_dash(k4)
                        if alphanum_dash:
                            alpha_dash, k6 = is_alpha_dash(k5)
                            if alpha_dash:
                                if len(k6) > 0:
                                    if k6 not in unique_entries:
                                        unique_entries.append(k6)
            print(f"\nUnique Names Decoded from '{column_name}' Column in '{t}' Table:\n")
            for i in unique_entries:
                  print(i)
        print()
        print("-"*80)
        print()
        time.sleep(2)
    conn.close()
    print()
    print("DONE")

if __name__ == '__main__':
    main()
	# lnd-postgres-kv-decoder.py

	from decouple import config
	import psycopg2
	import psycopg2.extras
	import pandas as pd
	import time

	tables = ['channeldb_kv', 'decayedlogdb_kv', 'macaroondb_kv',
	'towerclientdb_kv', 'towerserverdb_kv', 'walletdb_kv']

	def bytea2bytes(value, cur):
	"""
	Typecaster for converting bytea data to bytes
	"""
	m = psycopg2.BINARY(value, cur)
	if m is not None:
	return m.tobytes()

	# register the BYTE2BYTES type for psycopg2
	BYTEA2BYTES = psycopg2.extensions.new_type(psycopg2.BINARY.values, 'BYTEA2BYTES', bytea2bytes)
	psycopg2.extensions.register_type(BYTEA2BYTES)

	def lnddb_conn():
	"""
	Connect to Postgres Database using psycopg2
	- credentials should be stored in an .env file for python-decouple to read from
	"""
	dbname = config('DB_NAME_LND')
	user = config('DB_USER_LND')
	password = config('DB_PASSWORD_LND')
	host = config('DB_HOST_LND')
	port = config('DB_PORT_LND', cast=int)
	try:
	conn = psycopg2.connect(dbname=dbname,
	user=user,
	password=password,
	host=host,
	port=port,
	sslmode='require'
	)
	except (Exception, psycopg2.Error) as e:
	print(f"Error while connecting to DB:\n", e)
	conn = None
	return conn

	def select_all(conn, table_name):
	"""
	Select all records from a given table in the Postgres database
	"""
	dict_cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
	dict_cur.execute(f"select key::bytea, value::bytea, parent_id, id, sequence from {table_name} "
	"where value is not null ");
	rec = dict_cur.fetchall()
	dict_cur.close()
	return rec

	def single_table(table_name):
	"""
	Select all records from a single table and read them into a Pandas dataframe
	"""
	conn = lnddb_conn()
	t = table_name
	rec = select_all(conn, t)
	col_names = ['key', 'value', 'parent_id', 'id', 'sequence']
	df = pd.DataFrame([[row[col] for col in col_names] for row in rec], columns=col_names)
	conn.close()
	return df

	def is_alpha_dash(w):
	"""
	Check whether a given value can be decoded into an alpha string including dashes
	"""
	if not isinstance(w, str):
	try:
	w = w.decode("utf-8")
	except:
	pass
	alphanumdash = ['a','b','c','d','e','f','g','h','i',
	'j','k','l','m','n','o','p','q','r',
	's','t','u','v','w','x','y','z','-']
	chars = []
	for c in w:
	if c in alphanumdash:
	chars.append(c)
	chars_w = ''.join(chars)
	if len(chars_w) == len(w):
	return True, chars_w
	else:
	return False, w

	def is_alphanum_dash(w):
	"""
	Check whether a given value can be decoded into an alphanumeric string including dashes
	"""
	if not isinstance(w, str):
	try:
	w = w.decode("utf-8")
	except:
	pass
	alphanumdash = ['a','b','c','d','e','f','g','h','i',
	'j','k','l','m','n','o','p','q','r',
	's','t','u','v','w','x','y','z','0',
	'1','2','3','4','5','6','7','8','9',
	'-']
	chars = []
	for c in w:
	if c in alphanumdash:
	chars.append(c)
	chars_w = ''.join(chars)
	if len(chars_w) == len(w):
	return True, chars_w
	else:
	return False, w

	def main():
	"""
	Read data from all tables and print out human-readable, decoded keys and values
	"""
	conn = lnddb_conn()
	for t in tables:
	print(f"fetching data from {t}...")
	rec = select_all(conn, t)
	col_names = ['key', 'value', 'parent_id', 'id', 'sequence']
	df = pd.DataFrame([[row[col] for col in col_names] for row in rec], columns=col_names)
	column_names = ['key', 'value']
	for col in column_names:
	unique_entries = []
	for k in df[col]:
	if k != None and len(k) > 0:
	alphanum_dash, k2 = is_alphanum_dash(k)
	if alphanum_dash:
	alpha_dash, k3 = is_alpha_dash(k2)
	if alpha_dash:
	if len(k3) > 0:
	if k3 not in unique_entries:
	unique_entries.append(k3)
	else:
	k4 = binascii.hexlify(k).decode('utf-8')
	alphanum_dahsh, k5 = is_alphanum_dash(k4)
	if alphanum_dash:
	alpha_dash, k6 = is_alpha_dash(k5)
	if alpha_dash:
	if len(k6) > 0:
	if k6 not in unique_entries:
	unique_entries.append(k6)
	print(f"\nUnique Names Decoded from '{column_name}' Column in '{t}' Table:\n")
	for i in unique_entries:
	print(i)
	print()
	print("-"*80)
	print()
	time.sleep(2)
	conn.close()
	print()
	print("DONE")

	if __name__ == '__main__':
	main()