Thread: Re: hi i need to connect database from my system to another

Hi

I am using windows xp operating system...
I want to know how to connect remote postgresql from my system.. I need
brief description from beginning..
Now I am using pg_HBF conf file to connect remote postgresql in that I
am giving host name , ip-address,mask and trust... but it is showing
error like

Could not connect to server: connection refused(0x0000274d\10061)
Is the server running on host 127.0.0.1 and accepting TCP\IP connection
on port 5432 ?


Regards
penchal

-----Original Message-----
From: Richard Broersma Jr [mailto:rabroersma@yahoo.com]
Sent: Monday, July 03, 2006 5:57 PM
To: Penchalaiah P.
Cc: General Postgresql List
Subject: RE: [SQL] hi i need to connect database from my system to
another system

> If u don't mind can u please tell me clearly.. where I have start -I
or
> how to start postgresql with -I

> > I am using postgresql database server... in my system.. if I want to
> > connect to this database server from other system..Or how can I use
> this
> > database server  in other machine ..
> >
> > How can I do this..if any one have idea about this please give me
> brief
> > description
>
> You need to start the postgresql server with the "-i" option to allow
> TCP/IP connections.  Then
> you need to configure your pg_hba.conf file to set the permissions
about
> which users can connect
> to which databases.
>
> Notice the pg_ctl section for starting postgresql.
> http://www.postgresql.org/docs/8.1/interactive/app-pg-ctl.html
>
> Notice the -o "options" section that can be set. (in this case you
want
> -i)
> http://www.postgresql.org/docs/8.1/interactive/app-postmaster.html
>
> Finally notice, the section on the pg_hba.conf file.
>
http://www.postgresql.org/docs/8.1/interactive/client-authentication.htm
> l#AUTH-PG-HBA-CONF
>
> Also, secure shell also works well when connecting from another
system.

Don't forget to include the pgsql-sql list in your reply's. :-)  The
individuals on this list have
varying degree of experience and knowledge of postgresql.  So if you
only respond to me, you would
be limiting yourself to my meager experience and knowledge. ;-)

To start off with, it would be helpful to know which OS version of
PostgreSQL you are using.  The
windows versions is pre-configured to start with the "-i" option for
allowing TCP/IP connections.

Secondly,  how are you trying to connect to your postgresql server from
another system?  What
error messages are you getting.

Regards,

Richard Broersma Jr.



Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is
intendedfor use only by the individual or the entity to which it is addressed, and may contain information that is
privileged,confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it
appearsthat this mail has been forwarded to you without proper authority, you are notified that any use or
disseminationof this information in any manner is strictly prohibited. In such cases, please notify us immediately at
info.in@infics.comand delete this email from your records. 

> I am using windows xp operating system...
> I want to know how to connect remote postgresql from my system.. I need
> brief description from beginning..
> Now I am using pg_HBF conf file to connect remote postgresql in that I
> am giving host name , ip-address,mask and trust... but it is showing
> error like
> 
> Could not connect to server: connection refused(0x0000274d\10061)
> Is the server running on host 127.0.0.1 and accepting TCP\IP connection
> on port 5432 ?

Actually,  your PostgreSQL server is running on the IP address that is assigned to that computer.

The 127.0.0.1 in the pg_hba.conf file is the initial accepted client IP address that your
postgresql server will accepted connections from.  127.0.0.1 is basically means localhost.  So,
your pg_hba.conf file is initial configured to only allow client connections from itself.

So in this case, if you want to connect from an IP address from a different computer, you will
need to specify that ip address in your pg_hba.conf file as well as the permissions.

But just to get started, you could try editing a line in your pg_hba.conf to this:

#host    all     all         127.0.0.1/32                md5
host    all     all    <your client ip address goes here>/24         trust
host    all     all         127.0.0.1/32                trust

To understand all of this, you will need to read the introduction in your pg_hba.conf file. Also,
keep in mind that the above change is basically an "open door" to everything in your DB server. 
So after you've had a change to get familiar with everything, you will want to tighten up on
how,what, and how access is granted to you system.

"Last but not least", you will need to restart your postgresql service so these changes will take
effect.

This should get you connected.

Regards,

Richard Broersma Jr.

Hi, <br /><br /> Does anyone have experience with storing encrypted data into pgSQL? I have a pgSQL database which
usesUTF8 encoding. I am encrypting plain text in my (GUI) application and sending it to a field (with data type 'text')
inmy database. <br /><br /> I get an error back saying "invalid byte sequence for encoding UTF8; oxd733"<br /><br /> I
amusing blowfish at 128bit to encrypt the string.<br /><br /> The encrypted string comes out looking like this<br /><br
/><fontface="Arial" size="-1">Salted__ë±9r™LZPÃOG¤u–¨ékTz~6Ëpi™|O +LÏ°‹r°ô9•mµ�á%€©õP{:‚Àªd<br />
îX@ûñÙÁl£ðŒ…¢53P9×ð“á6kUÆ¢÷ÍìMNÉA•—ä
![ˆj”Ø|¨\X–ÛÜu�ªÅÜ-ÎáBæºñȦbF
>\Žv/ÞÎ.&ó«[=bný�Kr°óË2U×ZI©âVc°sn=¤Õg«^q0Ã8ý
<Êh�;”¨"�[Šç+u[£   
–è—=‘±ú3³�ì¯,Za@LU‘T¸œÖgœËPÛ<…H—8ÃbÙÉŽNÙí>ÇL]í¶²–âŒÿ/Ínz¶å<br/>
Õ?Îcµˆ$í¿W-™óà¢ey÷àÞ¬œ¤CðÏjX”ÃGÄ„·º?ë0L‘هƞ‹&ˆ{ªìëá/Ç}k9?ÄX1⋲T½Hõ5Ôgÿ§yÈy…_—ú$lA~©î1úëùññðÑÝŠžgÕþÊ/«¦…8
�¯þ   
>ü‚ƒ€–aÈÉ„iG‰}¹²#À</font><br/><br /> I enclose that in single quotes and send it in an insert statement to pgSQL.
<br/><br /> The statement has been working, until I got to a string that generated the above encrypted data, then it
broke.<br/><br /> Alternatively, can anyone suggest an encryption algorithm that will only return characters that are
validUTF8 encoded?<br /><br /> Thanks in advance<br /><br /> John T<br /><br /><br /> 

On Mon, Jul 17, 2006 at 09:11:12AM +0800, John Tregea wrote:
> Does anyone have experience with storing encrypted data into pgSQL? I 
> have a pgSQL database which uses UTF8 encoding. I am encrypting plain 
> text in my (GUI) application and sending it to a field (with data type 
> 'text') in my database.
> 
> I get an error back saying "invalid byte sequence for encoding UTF8; oxd733"

Use bytea instead of text for binary data; alternatively, base64
encode ("armor") the encrypted data and store it as text.  If you
insert binary data then you'll need to escape non-printable characters
or use an API that can pass binary data to the backend (e.g., libpq's
PQexecParams() or some language's interface to that function).  Some
APIs might automatically escape data if you use placeholders; check
your API's documentation.

http://www.postgresql.org/docs/8.1/interactive/datatype-binary.html
http://www.postgresql.org/docs/8.1/interactive/libpq-exec.html

-- 
Michael Fuhr

Hi Michael,

Thanks for your advice. I was looking at the bytea data type in the 
PostgreSQL book I bought (Korry and Susan Douglas, second edition). I 
was concerned that if I have to escape certain characters like the 
single quote or the backslash, how do I guarantee that the escaped 
version does not already appear in the encrypted string?

Should I use the octal value to escape the single quote (\047) and 
backslash (\\134)?

Those character sequences are extremely unlikely to occur in an 
encrypted string.

Is the the right approach ?

Also... I note that I cannot change the data type of my field from text 
to bytea (I am using PGADMIN III). Do you know why?

Regards

John T

Michael Fuhr wrote:
> On Mon, Jul 17, 2006 at 09:11:12AM +0800, John Tregea wrote:
>   
>> Does anyone have experience with storing encrypted data into pgSQL? I 
>> have a pgSQL database which uses UTF8 encoding. I am encrypting plain 
>> text in my (GUI) application and sending it to a field (with data type 
>> 'text') in my database.
>>
>> I get an error back saying "invalid byte sequence for encoding UTF8; oxd733"
>>     
>
> Use bytea instead of text for binary data; alternatively, base64
> encode ("armor") the encrypted data and store it as text.  If you
> insert binary data then you'll need to escape non-printable characters
> or use an API that can pass binary data to the backend (e.g., libpq's
> PQexecParams() or some language's interface to that function).  Some
> APIs might automatically escape data if you use placeholders; check
> your API's documentation.
>
> http://www.postgresql.org/docs/8.1/interactive/datatype-binary.html
> http://www.postgresql.org/docs/8.1/interactive/libpq-exec.html
>
>   

On Mon, Jul 17, 2006 at 02:05:32PM +0800, John Tregea wrote:
> Thanks for your advice. I was looking at the bytea data type in the 
> PostgreSQL book I bought (Korry and Susan Douglas, second edition). I 
> was concerned that if I have to escape certain characters like the 
> single quote or the backslash, how do I guarantee that the escaped 
> version does not already appear in the encrypted string?
> 
> Should I use the octal value to escape the single quote (\047) and 
> backslash (\\134)?

Yes, you'll need to escape certain characters like backslashes.
Your client API might provide an escaping function so you don't
have to worry about the details, or, if you're using placeholders,
the API might automatically escape binary data or transfer it to
the backend as binary.  What client interface will you be using to
insert this data?

> Those character sequences are extremely unlikely to occur in an 
> encrypted string.

They're as likely to occur as any other.  A desirable property of
a cipher is that its output should be indistinguishable from random
data, so any character or sequence of characters is equally likely
to occur.

> Also... I note that I cannot change the data type of my field from text 
> to bytea (I am using PGADMIN III). Do you know why?

I don't use pgAdmin so I don't know what it can or can't do.  Have
you tried it in psql?  What version of PostgreSQL are you using?
The ability to alter a column's type was added in 8.0; in previous
versions you can use the method shown in the FAQ:

http://www.postgresql.org/docs/faqs.FAQ.html#item4.3

-- 
Michael Fuhr

On 7/17/06, John Tregea <john@debraneys.com> wrote:
==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
==================================================================

Hi Aaron,

I found that your suggestion worked well. For some reason the IDE I use 
(Revolution) put a return character every 73rd character when it did the 
base64encode, but I strip those out and there no further problems. I 
don't even have to put them back later to decode it.

Thanks

John T

Aaron Bono wrote:
> On 7/17/06, *John Tregea* <john@debraneys.com 
> <mailto:john@debraneys.com>> wrote:
>
>     Hi Michael,
>
>     Thanks for your advice. I was looking at the bytea data type in the
>     PostgreSQL book I bought (Korry and Susan Douglas, second edition). I
>     was concerned that if I have to escape certain characters like the
>     single quote or the backslash, how do I guarantee that the escaped
>     version does not already appear in the encrypted string?
>
>     Should I use the octal value to escape the single quote (\047) and
>     backslash (\\134)?
>
>     Those character sequences are extremely unlikely to occur in an
>     encrypted string.
>
>     Is the the right approach ?
>
>     Also... I note that I cannot change the data type of my field from
>     text
>     to bytea (I am using PGADMIN III). Do you know why? 
>
>
>  
> When I have data like this, I do a Base64 encoding.  The string ends 
> up longer but I no longer have to worry about special characters 
> mucking things up.
>
> ==================================================================
>    Aaron Bono
>    Aranya Software Technologies, Inc.
>    http://www.aranya.com
> ================================================================== 

On 7/19/06, John Tregea <john@debraneys.com> wrote:
==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
==================================================================

Hi Aaron,

I removed them because when I bring the data back into my GUI, the 
returns are treated as a record break by the software and I cannot 
decrypt them or display the rest of the record that is not encrypted.

Regards

John T

Aaron Bono wrote:
> On 7/19/06, *John Tregea* <john@debraneys.com 
> <mailto:john@debraneys.com>> wrote:
>
>     Hi Aaron,
>
>     I found that your suggestion worked well. For some reason the IDE
>     I use
>     (Revolution) put a return character every 73rd character when it
>     did the
>     base64encode, but I strip those out and there no further problems. I
>     don't even have to put them back later to decode it.
>
>
>  
> I usually leave the return characters where they are.
>
> I am curious, why did you decide to remove the return characters?
>
> ==================================================================
>    Aaron Bono
>    Aranya Software Technologies, Inc.
>    http://www.aranya.com
> ==================================================================