Re: [GENERAL] Two Machines - Question

On Mon, 15 Feb 1999, Paul Nelson wrote:

> Can I run Postgres on one machine (Linux) and access it from another
> machine (Linux / Apache web server)?

Yes. ;-)

> Any help would be great.

Oh, additional information wanted? No problem:

First you define which host to trust in your network in
your "pg_hba.conf" file.

This looks like this on this host:

host all 192.168.100.0 255.255.255.0 trust

All connections via 192.168.100.X are considered trusted.

Then you open a tcp/ip connection to your database host on
port 5432. You authenticate yourself as a user and you place
a query onto the host. (provided the given user is allowed
to do that)

When the query finished you retrieve the data via
the open socket.

After this you close the socket and process your data.

On this host this is accomplished using a tcl/tk script.
(caution: works with tcl 8.0 only because of the binary
datatype)

== snip ==
#
# to be included into other sources ...
#
# set debugfile [open lalla.bin w]

proc {db_puts} {f len s} {
# Write string s onto channel $f with the length $l
puts -nonewline $f [binary format a$l $s]
}

# connect to a database and return the database communication channel (dbc)
proc {db_connect} {host port} {
set dbc [socket $host $port]
fconfigure $dbc -translation binary
return $dbc
}

# authenticate onto a database "dbname" via the dbc $f with the username and password
proc {db_authenticate} {f dbname username password} {
set dbc $f
# for debugging purposes put the output into a file named moerps.bin
# set dbc [open moerps.bin w+]

# Int32(296)
# The size of the packet in bytes.
puts -nonewline $dbc [binary format I 296]

# Int32
# The protocol version number. The most significant 16 bits are the major version number.
# The least 16 significant bits are the minor version number.
puts -nonewline $dbc [binary format H* 0001]
puts -nonewline $dbc [binary format H* 0000]

# LimString64
# The database name, defaults to the user name if omitted.
puts -nonewline $dbc [binary format a64 $dbname]

# LimString32
# The user name.
puts -nonewline $dbc [binary format a32 $username]

# LimString64
# Any additional command line arguments to be passed to the backend by the postmaster.
puts -nonewline $dbc [binary format H128 00]

# LimString64
# Unused.
puts -nonewline $dbc [binary format H128 00]

# LimString64
# The optional tty the backend should use for debugging messages.
puts -nonewline $dbc [binary format H128 00]
flush $dbc
# close the debugging channel before reading from it
# close $dbc
# puts "getting auth response code ..."
# First get the auth response code pattern (must be R)
binary scan [read $dbc 1] a s
# then read the bytes corresponding to the response code long
if {$s == "R"} {
# read the auth code response
binary scan [read $dbc 4] I s
# Auth. ok
if {$s == 0} { return $s }
# Auth. via KERBEROS V4 required
if {$s == 1} { return $s }
# Auth. via KERBEROS V5 required
if {$s == 2} { return $s }
# Auth. via unencrypted password required
if {$s == 3} { return $s }
# Auth. via encrypted password required
if {$s == 4} {
# fetch the salt to encrypt the password and store it into the result code
set ss $s
binary scan [read $dbc 2] s* s
lappend ss $s
return $ss
}
}
return $s
}

# Send a disconnect char to the database, flush the channel and close it afterwards
proc {db_disconnect} {f} {
puts -nonewline $f X
flush $f
close $f
return
}

# puts rather a command string than a query to a host and returns
# the values in a list of lists. The first (0) element of the list
# contains the field names of the view.
# all following elements contain the data of the corresponding fields.
proc {db_query} {f query} {
set dbc $f
# for debugging purposes print the query onto the screen
# puts $query

puts -nonewline $dbc "Q$query"
puts -nonewline $dbc [binary format H* 00]
flush $dbc
set result {}
set row {}
set feldanz 0
while {1} {
set row {}
# read a byte from the dbc and decide which responsecode is to be handled
# puts -nonewline "Responsecode from dbc is : "
binary scan [read $dbc 1] a s
# puts $s

# Handle an error condition
if {$s=="E"} {
set ss $s
while {$s != "\000"} {
binary scan [read $dbc 1] a s
# puts $s
set ss $ss$s
}
return $ss
}

# Handle an "Authentication request".
if {$s=="R"} {
binary scan [read $dbc 4] I s
# puts -nonewline "Authentication code is: $s"
continue
}
# Message is a Cursor Response
if {$s=="P"} {
# puts "cursor response"
while {$s != 0 } {
# the cursor response is nulled in this version.
# maybe this should be changed but it doesnt harm at the moment.
binary scan [read $dbc 1] H* s
}
continue
}
# Message is a Table description
if {$s=="T"} {
# puts "getting table description"
# get no. of colummns
binary scan [read $dbc 2] S s
set feldanz $s
# puts "no. of fields is: $feldanz"
for {set i 1} {$i<=$feldanz} {incr i} {
# fetch the corresponding fieldname from dbc
# puts "fetching one fieldname ..."
set ss ""
while {$s != "\000"} {
# get one character from the dbc
binary scan [read $dbc 1] a s
if {$s != "\x00"} {
# append the character to $ss
set ss "$ss$s"
}
}
# get the fieldname into $s from $ss
set s $ss

# puts -nonewline "Fieldname : $s"
lappend row $s

# fetch the Objectid from the dbc
binary scan [read $dbc 4] I s
# puts -nonewline " objectid : $s"

# fetch the objectsize from the dbc
binary scan [read $dbc 2] s s
# puts " objectsize : $s"
}
lappend result $row
continue
}
# An ASCII row is to be fetched ...
if {$s=="D"} {
# getting bitfield

set j [expr ($feldanz % 8) ? round(($feldanz / 8) + 0.5) : ($feldanz / 8)]

# puts "Handling a bitfield with $j bytes "
# fetch the bitfield from the dbc to decide which fields are filled with values
set bf ""
for {set i 1} {$i<=$j} {incr i} {
# puts "getting byte # $i"
binary scan [read $dbc 1] B* s
set bf "$bf$s"
# puts "bitfield value is $bf"
}

# puts "fetching $feldanz fields from dbc"
# fetch all fields of the particular row
for {set i 1} {$i<=$feldanz} {incr i} {
# fetch the field contents from the dbc if the corresponding bit in $bf is set
set k [expr $i-1]
if {[string range $bf $k $k]=="1"} {
binary scan [read $dbc 4] I l
# puts "fieldlength is: [expr $l-4]"
set ss ""
for {set j 5} {$j<=$l} {incr j} {
binary scan [read $dbc 1] a s
set ss $ss$s
}
} else {
# puts "NULL-Value detected, field skipped"
set ss {}
}
# puts "$ss "
lappend row $ss
}
lappend result $row
# puts $row
continue
}
# a C states that a command execution is ready and that no more data follows.
if {$s=="C"} {
# puts "Handling C-Order"
while {$s != 0} {
binary scan [read $dbc 1] c s
}
break
}
# Merkmal einer leeren View
if {$s=="I"} {
break
}
}
return $result
}

== snip ==

Then you can do a simple:

set dbc [db_connect $dbhost $dbport]
db_authenticate $dbc $dbname $dbuser $dbuserpw

set l [db_query $dbc "select * from customers;"]

db_disconnect $dbc

In a tcl/tk script to retrieve a list of records into
the variable "l" from a database which resides on the
host "$dbhost". This script has the advantage to be
at least portable to Linux and Win NT. (Both platforms were
tested successfully)

I don't know too much about perl and so on, but the way
to go is always the same. (Ok, you have to define events to trigger
a faulty connection, you have to implement a recovery scheme and so on)

t++