Python connect to postgresql with ssh

How to Query PostgreSQL using Python (with SSH) in 3 Steps

Often data is housed within databases like PostgreSQL on remote servers, which can make it difficult for data analysts to access the data quickly. Sometimes, there are intermediate teams that assist analysts with retrieving the data from the database. In this story we’ll talk about how you can directly query the data using Python, without the need for intermediates.

If you know how to use Python (mainly for analytics and visualizations) but don’t have experience with databases or how to interact with them, then this post is for you!

There are a variety of tools at our disposal that will enable you to interact/retrieve data yourself from a database and spend more time extracting insights.

• STEP 1: Install all required packages.
• STEP 2: Import or paste query.py contents into your Notebook.
• STEP 3: Start querying!

This story assumes the following:

• You have Python already installed on your local environment
• You are using a live-code environment like Jupyter Notebooks
• Have been given the necessary credentials to SSH into the remote server (.pem certificate) and query the PostgreSQL database (username and password).

STEP 1: Install all required packages:

First we’ll need to install several packages from terminal.

pip3 install paramiko
pip3 install sshtunnel
pip3 install SQLAlchemy
pip3 install pandas

STEP 2: Import or paste query.py contents into your notebook

Next, you’ll need to either save the query.py file from the repo to your working directory (where your Jupyter Notebook file is) or simply copy the contents of the file into your Notebook directly.

If you are placing the query.py file in your working directory, then include the following import line in your Notebook:

Alternatively, simply copy and paste the code below into a code cell in your Notebook:

STEP 3: Query!

Now were ready to start querying! The defined class only provides a handful of basic functions. Let’s walk through how to use the class and what we can do with it.

First, we’ll need to specify our PostgreSQL connection arguments, and SSH arguments (if SSH tunneling is required to access the remote server).

We define pgres as our connection to simplify each time we want to query the database or explore the organizational structure of the database. You will also be prompted for your PostgreSQL username and password, which are stored as temporary variables (best-practice is to save these variables as environment variables instead).

Next, we can explore the schemas of our given database named ‘database_name’ to find our schema of interest using the .schemas() function.

If we want to explore the schema named ‘schema_name’, we can return the names of the tables within the schema using the .tables() function.

Finally, we can use .query() to run standard SQL queries (for PostgreSQL). In this example we’re querying the column names and data types from the table named ‘ey_test_table’

Try replacing the contents of sql_statement with your own query, and have fun!

Источник

Connecting to remote PostgreSQL database over SSH tunnel using Python

I scratched myself the head for a while too to do it within Python code and avoid SSH external tunnels, we need to thank developers that wrap complex libraries into simple code! Will be simple, generate a tunnel to port 5432 in localhost of remote server from a local port then you use it to connect via localhost to the remote DB. Question: I have a problem with connecting to a remote database using ssh tunnel (now I’m trying with Paramiko).

Connecting to remote PostgreSQL database over SSH tunnel using Python

I have a problem with connecting to a remote database using ssh tunnel (now I’m trying with Paramiko). Here is my code:

#!/usr/bin/env python3 import psycopg2 import paramiko import time #ssh = paramiko.SSHClient() #ssh.load_system_host_keys() #ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy()) #ssh.connect('pluton.kt.agh.edu.pl', 22, username='aburban', password='pass') t = paramiko.Transport(('pluton.kt.agh.edu.pl', 22)) t.connect(username="aburban", password='pass') c = paramiko.Channel(t) conn = psycopg2.connect(database="dotest") curs = conn.cursor() sql = "select * from tabelka" curs.execute(sql) rows = curs.fetchall() print(rows) 

A problem is that the program always tries to connect to the local database. I tried with other SSH tunnels and there was the same situation. Database on remote server exists and works fine using «classical» SSH connection via terminal.

You can try using sshtunnel module that uses Paramiko and it’s Python 3 compatible.

Hopefully it helps you. I scratched myself the head for a while too to do it within Python code and avoid SSH external tunnels, we need to thank developers that wrap complex libraries into simple code!

Will be simple, generate a tunnel to port 5432 in localhost of remote server from a local port then you use it to connect via localhost to the remote DB.

This will be a sample working code for your needs:

#!/usr/bin/env python3 import psycopg2 from sshtunnel import SSHTunnelForwarder import time with SSHTunnelForwarder( ('pluton.kt.agh.edu.pl', 22), ssh_password="password", ssh_username="aburban", remote_bind_address=('127.0.0.1', 5432)) as server: conn = psycopg2.connect(database="dotest",port=server.local_bind_port) curs = conn.cursor() sql = "select * from tabelka" curs.execute(sql) rows = curs.fetchall() print(rows) 

Postgresql — SSH tunnel to a postgres database, Currently I develop on a remote dev server. I SSH into the dev server (using SSH keys) as. ssh -p 22222 user@devbox.com. Then from the dev server, I connect to the database over the local network. psql -U postgres -h psqldb -d my_database. where psqldb points to 10.0.0.202 on the dev server’s /etc/hosts file. I want to …

Robot Framework: Connect to PostgreSQL Database via SSH Tunnel

i’m facing the following problem. I’ve got a PostgreSQL database on a remote server. In order to access it, i need to pass through a SSH Tunnel.

So, the steps of my test are:

I’m able to create the tunnel by using SSHLibrary, and it works:

SSHLibrary.Open Connection 10.xxx.xxx.xx SSHLibrary.Login MySSH_User MySSH_Password delay=1 SSHLibrary.Create Local SSH Tunnel 9191 $ 22 

So i go on by using DatabaseLibrary to perform the connection (and i’ve checked all the fields are right):

DatabaseLibrary.Connect To Database psycopg2 $ $ $ $ $

but i get the following error:

OperationalError: could not connect to server: Connection timed out (0x0000274C/10060) Is the server running on host "10.xxx.xxx.xx" and accepting TCP/IP connections on port 5432? 

How can i solve it? I think i need to specify in some way that the database connection must be done by using the tunnel but i don’t know how to do it. Here’s the complete test code:

*** Settings *** Library DatabaseLibrary Library SSHLibrary *** Variables *** $ 10.xxx.xxx.xx $ 5432 $ MyNameDB $ MySchemaDB $ MyUserDB $ MyPasswordDB *** Keywords *** *** Test Cases *** Connect To DB Via SSH SSHLibrary.Open Connection 10.xxx.xxx.xx SSHLibrary.Login MySSH_User MySSH_Password delay=1 SSHLibrary.Create Local SSH Tunnel 9191 $ 22 Sleep 2s DatabaseLibrary.Connect To Database psycopg2 $ $ $ $ $

I would like to do it by using RF, Anyone can help? Thank’s a lot

SSH tunnels should work in a way that that you establish SSH connection to another host and expose a PORT in localhost that will then tunnel a traffic from that port in localhost to a IP/PORT in the end point of the tunnel ..

So, lets say you have a host at 192.168.100.10 where your robotframework is running and it needs to connect to a database in 192.168.50.100:

If the postgres is accessible via ssh directly:

Connect To DB Via SSH $= 192.168.50.100 $= 5432 SSHLibrary.Open Connection $ SSHLibrary.Login MySSH_User MySSH_Password delay=1 SSHLibrary.Create Local SSH Tunnel $ $ $ DatabaseLibrary.Connect To Database psycopg2 $ $ $ 127.0.0.1 $

Now, if you have to use ssh jump host, eg, connect to a SSH server that will then connect to another server where postgres is running, all you need to do is change the tunnel ip address where the traffic will be forward to:

Connect To DB Via SSH $= 192.168.50.100 $= 192.168.50.1 $= 5432 SSHLibrary.Open Connection $ SSHLibrary.Login MySSH_User MySSH_Password delay=1 SSHLibrary.Create Local SSH Tunnel $ $ $ DatabaseLibrary.Connect To Database psycopg2 $ $ $ 127.0.0.1 $

Postgresql — Setting up pglogical over ssh tunnel, Network setup: On-premise postgres < jumphost > <-- ssh tunnel -->AWS RDS internal-ip 10.2.0.8 db01.some.tld. I have verified that the ssh tunnel is working: I can connect to the on-premise postgres from AWS using psql connecting to the tunnel. I can even connect to the on-premise postgres from AWS RDS …

PgAdmin 4 — How to connect to database via ssh tunnel as posgres when its password is empty?

I am trying to connect to a Postgres database at my DigitalOcean instance via PgAdmin 4.

I create an SSH tunnel as root, provide the PgAdmin with my identify file. I want to login into database as postgres user which doesn’t have a password.

So PgAdmin doesn’t let me log in and requires the password for the postgres user.

Does user postgres have to have a password if I want to connect via SSH tunnel? Is it possible? And what should I do in order to connect?

[

Does user postgres have to have a password if I want to connect via SSH tunnel? Is it possible? And what should I do in order to connect?

Your SSH tunnel is configured so that the remote SSH server connects to PostgreSQL through a TCP connection to localhost . And generally, a default pg_hba.conf maps this type of connection to the md5 authentication scheme, for which a password is required. Otherwise any user with a shell account could connect to PostgreSQL, which would be wrong as a default configuration.

The more common way to use PostgreSQL through a SSH tunnel is to set a password to the PostgreSQL databases accounts that you need to connect to. Use the \password command inside psql or the ALTER USER SQL statement.

Set up SSH tunnel with PgAdmin 4, Add the following to your SSH config (~/.ssh/config): Host prod Hostname myorg.org.uk User sshusername IdentityFile idef.pem LocalForward 9999 localforward.amazonaws.com:8888 Now you can tunnel your way through to PostgreSQL: ssh -N prod And now psql et al can connect (You must open a new …

Connect to database PostSQL using ssh tunnel in R

Objective is to connect R to PostgreSQL using an SSH tunnel.

Just had unsuccessful tries with libraries RPostgreSQL and DBI . I’m not finding a way to pass along SSH tunneling parameters (proxy host, proxy user, and private key).

I wonder if there’s a way to somehow specify those SSH parameters along in db query string? Maybe another way out?

Here’s a code sample that I’ve used:

First, setup an ssh tunnel — this would be for an AWS EC2 instance:

ssh -i 'YOURKEY.pem' -N -L 1234:YOURDATABASEHOST:5432 YOURUSER@YOURAWSINSTANCE 

As you can see, the key is to setup a tunnel between a remote instance of some kind and the remote database. Then you use this locally hosted tunnel (hence, 127.0.0.1 as your host) and the specified port (1234 in this case).

What PostgreSQL admin GUI interface support SSH, EMS SQL Manager for PostgreSQL includes SSH tunneling in paid version. I’ve used it for years, works reliably and even supports key-based authentication. There is also free version, that is in many ways superior product compared to PgAdmin, but for some reason not very well known in PostgreSQL community. NB! Shortcut …

Источник

Подключение к базе данных PostgreSQL через SSH-туннель в Python

Я пытаюсь удаленно подключиться к серверу, а затем получить доступ к его локальной базе данных с помощью Python. Я успешно подключаюсь к серверу, хотя не могу подключиться к базе данных на сервере. Мой код ниже:

import psycopg2 from sshtunnel import SSHTunnelForwarder try: with SSHTunnelForwarder( ('', 22), ssh_private_key="", ssh_username="", remote_bind_address=('localhost', 5432)) as server: print "server connected" conn = psycopg2.connect(database="",port=server.local_bind_port) curs = conn.cursor() print "database connected except: print "Connection Failed" 

Это фрагменты кода, которые я нашел в Интернете и собрал вместе. Я также пробовал приведенные ниже операторы подключения вместо приведенного выше кода:

params = < 'database': '', 'user': '', 'password': '', 'host': 'localhost', 'port': 5432 > conn = psycopg2.connect(**params) 

Я знаю, что могу подключиться к базе данных, потому что на моем компьютере; Я могу использовать sqlectron для туннелирования и правильного подключения. На всякий случай неясно, что я пытаюсь сделать сверху, мне нужно ssh-туннель на мой удаленный сервер, используя закрытый ssh-ключ на моем компьютере (работает правильно), а затем мне нужно подключиться к базе данных PostgreSQL, которая является на localhost в порту 5432 . В настоящее время я получаю текущее сообщение об ошибке для обоих способов подключения:

2016-01-23 11:16:10,978 | ERROR | Tunnel: 0.0.0.0:49386 <> localhost:5432 error: (9, 'Bad file descriptor') 

Источник