Joshua Abraham
PBNJ using Postgres Backend
Fri Mar 23 22:46:31 EDT 2007

The following are the steps used to setup an example of Postgres
listening on the network. 

Note the following works with Postgres 7.4 and 8.1. Setting up the
database is the same. However, getting it to listen on the network is
slightly different. 

This documentation was written and tested on Debian Etch Testing

Let me know how it goes.

Regards,
Josh


*********************************************


Reference:
http://www.cyberciti.biz/faq/howto-add-postgresql-user-account/


*********************************************

Step #0: Install Postgres either 7.4 or 8.1

    Install postgres 7.4
    # apt-get install postgresql-7.4


    Install postgres 8.1
    # apt-get install postgresql-8.1
    

*********************************************

Step # 1: Becoming a superuser

You need to login as database super user under postgresql server. Again the
simplest way to connect as the postgres user is to change to the postgres unix
user on the database server using su command as follows:

    # su - postgres

*********************************************

Step #2: Now connect to database server

Type the following command

    $ psql template1

Output:

Welcome to psql 7.4.16, the PostgreSQL interactive terminal.

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

template1=# 

*********************************************

Step #3: Add a user called bob

Type the following command to create a user called bob with a password called
testPassword 

    template1=# CREATE USER bob WITH PASSWORD ‘testPassword’;

*********************************************

Step #4: Add a database called pbnj

Type the following command:

    template1=# CREATE DATABASE pbnj;

Now grant all privileges on database

    template1=# GRANT SELECT,INSERT,UPDATE,CREATE ON DATABASE pbnj to bob;

Type \q to quit:
    
    template1=# \q

*********************************************
    
Step #5: Test bob user login

In order to login as bob you need to type following commands:
    
    # psql -d pbnj -U bob


Welcome to psql 7.4.16, the PostgreSQL interactive terminal.

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

pbnj=> 


*********************************************

Step #6: Install DBD::Pg


# apt-get install libdbd-pg-perl


*********************************************

Step #7: Configure Postgres

The next 2 sections are for setting up postgres 7.4 or 8.1
perform one of the other.


*********************************************

Step #7a: Setting up Postgres 7.4 to run on localhost network


edit  /etc/postgresql/7.4

set tcpip_socket to true

  tcpip_socket true


sudo /etc/init.d/postgresql-7.4 restart

*********************************************

Step #7b: Setting up Postgres 8.1 to run on localhost network


edit  /etc/postgresql/8.1.main/postgres.conf

# listen_address = 'localhost'

to be 

 listen_address = 'localhost'

sudo /etc/init.d/postgresql-8.1 restart


*********************************************

Step #8: Setting up the configuration file


# cd
# mkdir -p .pbnj-2.0
# cp /usr/share/doc/pbnj/examples/pg.yaml config.yaml

# nano config.yaml

Set the following configuration


db: Pg
# for SQLite the name of the file. For mysql the name of the database
database: pbnj
# Username for the database. For SQLite no username is needed.
user: "bob"
# Password for the database. For SQLite no password is needed.
passwd: "testPassword"
# HostName for the database. For SQLite no host is needed.
host: "127.0.0.1"
#Port for the database. For SQLite no port is needed.
port: "5432"



*********************************************

Step #9: Scan and Query for Results

cd ~/
# scanpbnj 172.16.223.128
...snipped...

# outputpbnj -q latestinfo
...snipped...


*********************************************

Step #10: Verify your data is in the database:

# psql -d pbnj
Welcome to psql 8.1.8, 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

pbnj=> select * from machines;
 mid |       ip       | host | localh |     os      | machine_created |        created_on        
-----+----------------+------+--------+-------------+-----------------+--------------------------
   1 | 172.16.223.128 | 0    |      0 | Linux 2.6.X | 1174608496      | Thu Mar 22 20:08:16 2007
(1 row)

pbnj=> select * from services;
 mid | service | state | port | protocol |     version     |     banner      | machine_updated |        updated_on        
-----+---------+-------+------+----------+-----------------+-----------------+-----------------+--------------------------
   1 | rpcbind | up    |  111 | tcp      | 2               | unknown product | 1174608496      | Thu Mar 22 20:08:16 2007
   1 | ident   | up    |  113 | tcp      | unknown version | OpenBSD identd  | 1174608496      | Thu Mar 22 20:08:16 2007
(2 rows)

pbnj=> \q