4

my local can connect to remote , but it cannot connect to dbserver. remote can connect to dbserver (with psql).

I want to run sql scripts from my local against the db.

I am looking to a way to open a psql on the remote and direct my stdin and stdout to my local with ssh.

Now in the stupid option i do this:

ssh remote
psql -h db report
password
run my commands

I can do it in a script in this way but its ugly and dosnt allow me interactive session with the psql.

PSQL="PGPASSWORD=mypassword psql -h dbserver  -d report"
SQL="SELECT 1"
ssh remote "$PSQL --command 'COPY ($SQL) TO STDOUT WITH CSV;'"

Ps- Python is also OK.

0

2 Answers 2

5

Use an ssh port forward with a backgrounded commandless ssh session:

$ ssh -N -f -L 5433:localhost:5432 remoteserver

with which you can then use:

$ psql -h localhost -p 5433

psql commands like \o, \i, etc will refer to files on the local computer.

If you're trying to do this for scripting purposes then you probably want to use ssh's -n option and a passwordless ssh key with options set in authorized_keys to prevent a shell from being spawned so all you can do is port forward a given port. This is very important for security when using passwordless keys. ssh-agent provides a somewhat more secure alternative but it's clumsier for scripting. Remember that your script will need to record the process ID of the background ssh session it spawned and kill it at the end of the task. It's sometimes easier to use shell job control rather than ssh's own daemonize mode; that way the shell keeps track of the process backgrounded with & and it can be terminated with kill %jobid. See bash job control. (You need to background the port forwarding ssh session so that execution of the script can continue and run psql).

For interactive use you can just invoke the remote psql over ssh, in which case commands like \o, \i, \copy, etc will refer to files local to the server you ssh'd to, not your local computer:

$ ssh remoteserver psql

This works for scripting purposes too, but is somewhat less secure than doing port forwarding since psql is a pretty powerful program that lets you read and write arbitrary files, spawn a shell, etc. You can't really limit what a passwordless ssh key can do if it's allowed to remotely invoke psql. On the other hand, it's a lot simpler to write:

ssh remoteserver psql <<'__END__'
BEGIN;
INSERT INTO blah(a,b,c) VALUES (1,2,3);
UPDATE otherblah SET x = 99, y = 912;
COMMIT;
__END__

than to muck about with background ssh sessions. Really it depends on what your priorities are.

2
  • I want the first option. The problem is that the dbserver is not localhost on the remote. i do ssh to the remote and there i do psql -h dbserver. When the db is local on the remote there is no problem with the solution, However when you need to add -h to the db it dosnt work. Got an error - psql: could not connect to server: Operation timed out Is the server running on host "dbserver" (x.x.x.x) and accepting TCP/IP connections on port 5433? Apr 21, 2013 at 16:16
  • @user1495181 Then change localhost in the ssh port-forward descriptor to the ssh destination's view of the target Pg server's hostname or IP address, eg ssh -L 5433:remotepgservername:5432 or ssh -L 5433:10.1.1.99:5432. Note that I've used local port 5433 to avoid conflicting with a locally running Pg so you have to specify -p 5433 in psql. Apr 21, 2013 at 23:36
0

You can use iptables on the remote machine to redirect postgresql traffic to the db server. For example:

iptables -A PREROUTING -s your_machine_ip -p tcp --dport 5432 -j DNAT --to-destination db_server_ip

Of course, this requires admin privilege on the remote machine. You need also to enable IP forwarding.

From your machine, you just connect to remote machine and it will redirect you to db server. This way you don't need to the shown ssh connection.

As a note, you may need to look at security issues in how you connect to your database system especially if you are doing this remotely over untrusted network.

3
  • I dont want to connect these computers or deal with iptables, just execute some updates on the db. Apr 21, 2013 at 11:56
  • You asked for a way to access database directly from your local machine and this is one way to do it.
    – Khaled
    Apr 21, 2013 at 11:57
  • 1
    Sorry if i wasnt clear . I am looking for a way to direct to psql by ssh . Not connect direct. Apr 21, 2013 at 12:05

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .