Connecting to a remote PostgreSQL server
Prev Part II.  Plain Usage  Next

Connecting to a remote PostgreSQL server

Jurjen Bokma

February

  1. Preparatory measures

    Create the user you want to connect as (which is generally not the database owner, see example), and the database you want to connect to:

    postgres=# CREATE USER lul_owner WITH NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT;
    postgres=# CREATE DATABASE userlogger WITH OWNER lu_owner;
    postgres=# CREATE USER lu_webadmin WITH NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT PASSWORD 'secret';

    [Note]Note

    Make sure that the script that fills the database grant appropriate access to your connecting user, as in

    GRANT SELECT, INSERT, UPDATE, DELETE ON Session TO lu_webadmin;

  2. Set a record in /etc/postgresql/8.3/main/pg_hba.conf to allow access by this user to this database: 

    hostssl userlogger  lu_webadmin,lu_reader                   10.0.2.13/32      md5

    [Note]Note

    Be careful: entries in pg_hba.conf are hit-or-miss: once the conntype/username/database/host matches a line, that line determines whether your connection succeeds. No other lines are tried.

  3. reload the PostgreSQL config

    A reload is enough:

    /etc/init.d/postgresql-8.3 reload

  4. Try to connect from the remote host:

    psql -d userlogger -h dbhost.intranet -U lu_webadmin
    Password for user lu_webadmin:
    Welcome to psql 8.3.9 (server 8.3.7), the PostgreSQL interactive terminal.

    Type:  \copyright for distribution terms
    \h for help with SQL commands
    \? for help with psql commands
    \g or terminate with semicolon to execute query
    \q to quit

    SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)

    userlogger=>

Prev Up Next
Part II.  Plain Usage  Home Generating plots with GNUplot and sending them via mail