Thread: Two Machines - Question

Two Machines - Question

From
"Paul Nelson"
Date:
Can I run Postgres on  one machine (Linux) and access it from another
machine (Linux / Apache web server)?

Any help would be great.

Thanks.


Re: [GENERAL] Two Machines - Question

From
James Thompson
Date:
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)?
>

I haven't tried the Apache interface but it can't image there being any
troubles.  I myself have used libpg, ecpg, and psql from remote clients
without trouble once the postgresql server is configured to accept remote
connections from that client.

The libpg, ecpg both accept the server name or IP address as part of their
connect string, I bet that the apache module does as well.

psql works like this

psql -h servername.domain.org -u databasename
  -h provides the host name or ip address
  -u forces pgsl to request username/password (I need this since I use
     crypt authentication)

Hope this helps

->->->->->->->->->->->->->->->->->->---<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<
James Thompson    138 Cardwell Hall  Manhattan, Ks   66506    785-532-0561
Kansas State University                          Department of Mathematics
->->->->->->->->->->->->->->->->->->---<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<



Re: [GENERAL] Two Machines - Question

From
Gilles Darold
Date:
Hi Paul,

Of courses, you can access pgsql database via TCP/IP based network. You
just have to run
postmaster with -i options and set your network or used security access.
Just set PGPORT to listen to network SQL queries.

I use Apache and mod_perl to access my database and it works very well.

Have fun with PostgreSQL !

Regards,

Gilles Darold.



Re: [GENERAL] Two Machines - Question

From
Thomas Antepoth
Date:

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 doesn�t 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++



Re: [GENERAL] Two Machines - Question

From
Gregory Maxwell
Date:
Yep, you need to make sure the other computer is allowed in the
pg_hba.conf file.

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)?
>
> Any help would be great.
>
> Thanks.
>
>