SQL Maestro Group / Products / PostgreSQL / PostgreSQL Data Sync / Documentation

PostgreSQL Data Sync online Help

PostgreSQL Data Sync allows you to connect to PostgreSQL directly, or via Secure SHell (SSH) tunnel, or HTTP tunnel.

•	Direct connection

It is the most natural and the most preferable connection mode. Use it each time it is possible.

•	SSH tunnel connection

If your PostgreSQL server does not allow direct connections from your remote workstations, you can establish connection to an allowed intermediate SSH server and forward all PostgreSQL commands through the Secure SHell (SSH) tunnel.

•	HTTP tunnel connection

HTTP tunneling is a technique used in conditions of restricted network connectivity including firewalled networks, networks behind proxy servers, and NATs. It is the slowest way and is recommended to use if the others are impossible.

Irrespectively of a connection mode you should specify common credentials as follows:

Host

The host name of the PostgreSQL server.

Port number

The TCP/IP port to use. Default PostgreSQL port is 5432.

SSL Options

It is possible for both the client and server to provide SSL certificates to each other. It takes some extra configuration on each side, but this provides stronger verification of identity than the mere use of passwords.

More about SSL options

To connect to a database, specify the SSL Mode to be used:

Disable
Allow
Prefer
Require
Verify-CA	The server will verify that the client's certificate is signed by one of the trusted certificate authorities.
Verify-Full	The server will not only verify the certificate chain, but it will also check whether the username or its mapping matches the cn (Common Name) of the provided certificate.

files containing the server certificate and private key must exist. By default, these files are expected to be named server.crt and server.key, respectively, in the server's data directory, but other names and locations can be specified using the configuration parameters ssl_cert_file and ssl_key_file.:

Client certificate

Client certificate key

Root certificate

Certificate revocation list

Specifies the name of the file containing the SSL server certificate revocation list (CRL). Relative paths are relative to the data directory. This parameter can only be set in the postgresql.conf file or on the server command line. The default is empty, meaning no CRL file is loaded (unless ssl_crl_dir is set).

SSL compression

Note: Using SSL requires that OpenSSL is installed on both client and server systems and that support in PostgreSQL is enabled at build time.

User name

The username used to connect to PostgreSQL.

Password

The password for the user account on server.

More about SSH tunnel connection

To establish connection to intermediate SSH server and forward all PostgreSQL commands through the secure tunnel, you need to:

1.	Check I can connect to the server directly or via SSH tunneling.

2.	Follow the Configure SSH options link to open the SSH Options window.

3.	Check Connect through the Secure Shell (SSH) tunnel and complete the following fields:

Host name

Specify the host name or IP of your site. Note, that PostgreSQL host name always should be set relatively to the SSH server. For example, if both of PostgreSQL and SSH servers are located on the same computer, you should specify localhost as Host name instead of server's external host name or IP address.

Port number

Enter the port number for the SSH server.

4. Enter valid User name for the remote server and select the Authentication method and set corresponding credentials.

Password-based

Set the password corresponding to the specified user.

Key-based

Specify the path to the Private key file with the corresponding Passphrase to log in to the remote server. PostgreSQL Data Sync accepts keys in ssh.com or OpenSSH formats. To convert a private key from PuTTY's format to one of the formats supported by our software, use the PuTTYgen utility that can be freely downloaded from the PuTTY website.

Keyboard interactive

Keyboard authentication is the advanced form of password authentication, aimed specifically at the human operator as a client. During keyboard authentication zero or more prompts (questions) is presented to the user. The user should give the answer to each prompt (question). The number and contents of the questions are virtually not limited, so certain types of automated logins are also possible.

More about connection via HTTP tunnel

To connect to a remote server using an HTTP tunnel, you need to:

1.	Upload the connection PHP script to your website. The script is named pgsql_tunnel.php and can be found under the installation folder, usually C:\Program Files\SQL Maestro Group\PostgreSQL Data Sync.

2.	Select the I have to use HTTP tunneling radio button.

Enter the connection PHP script URL, e.g. www.yoursite.com/files/pgsql_tunnel.php. You can test the connection before the profile is created. Just use Test script using default browser to open connection script in your browser, enter all the required connection parameters and click the Test connection button.

4.	In case using of a proxy server use Configure tunnelling options to open the HTTP tunnelling options window and specify your proxy server connection parameters and HTTP authentication.

Note: You are actually connecting to your database through the PHP script on the server, so in most cases the host/server name is "localhost" unless the target database server is not installed on the same computer as the Web server.

PostgreSQL Data Sync online Help

Connecting to the target database