PostgreSQL

Articles and tips on PostgreSQL.

SSH Tunneling

If you have PostgreSQL databases hosted remotely, SSH tunneling provides a convenient and secure communications mechanism. This approach avoids issues with SSL certificate configuration and obviates the need to modify the pg_hba.conf file to setup host permissions. The primary disadvantage is that some hosting providers do not allow SSH access (or only provide it on more expensive accounts).

Here is an example to setup SSH tunneling for PostgreSQL:

ssh -L 5555:localhost:5432 -l remoteuser myhost.com

This forwards connections made on localhost port 5555 to myhost.com port 5432. The -l parameter is your user name for shell access on the remote system (not your PostgreSQL user name).

So to use pgEdit, pgAdmin, or any other PostgreSQL utility, use localhost and port 5555 along with the same login credentials you would normally provide to connect to the database.

Resources:

Access control functions for PostgreSQL

PostgreSQL provides a number of access control options, but granting privileges en masse can be difficult. For example, the GRANT command requires that each table be explicitly listed in the statement. To overcome this, Andrew Hammond and Tim Goodaire created some SQL functions to simplify this and several other administration tasks related to access control.

Resources:

Subscribe to RSS - PostgreSQL