Re: pgAccess via ssh?

#1

Hi Hari,

Yes, I have been using ssh and bash shell to access my postgres
database using psql -d mydatabase.

Now I'd like to try a Gui frontend to aid the learning process and
help visualize some aspects of building sql queries, etc.

I was going to try pgAccess first because it appears to be a very
small, easy to use app. Maybe later on I would try pgAdmin.

Mike

On Thu, 2 Sep 2004 10:36:24 -0500, Hari Bhanujan
<hbhanujan (AT) sbcglobal (DOT) net> wrote:

Quote:

I know that after ssh you can use psql without any problems.

What are you using for postgresql - pgadmin..

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

#2

mike,

fwiw, pgadmin worked well for me and was very easy to set up once i put the "-i" flag in my postmaster call. never tried pgaccess so can't comment.

Mike <1100100 (AT) gmail (DOT) com> wrote:
Hi Hari,

Yes, I have been using ssh and bash shell to access my postgres
database using psql -d mydatabase.

Now I'd like to try a Gui frontend to aid the learning process and
help visualize some aspects of building sql queries, etc.

I was going to try pgAccess first because it appears to be a very
small, easy to use app. Maybe later on I would try pgAdmin.

Mike

On Thu, 2 Sep 2004 10:36:24 -0500, Hari Bhanujan
wrote:

Quote:

I know that after ssh you can use psql without any problems.

What are you using for postgresql - pgadmin..

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

---------------------------------
Do you Yahoo!?
Win 1 of 4,000 free domain names from Yahoo! Enter now.

#3

Hi Op,

Thanks for your response.
Were you able to use it remotely from a winbox like I am trying to do?
And you were using it to connect to your db by ssh too?

Tell me more.......I want to try this ASAP.

Thanks for the guidance.

Mike

----- Original Message -----
From: operationsengineer1 (AT) yahoo (DOT) com <operationsengineer1 (AT) yahoo (DOT) com>
Date: Thu, 2 Sep 2004 08:41:54 -0700 (PDT)
Subject: Re: [NOVICE] pgAccess via ssh?
To: Mike <1100100 (AT) gmail (DOT) com>, hbhanujan (AT) sbcglobal (DOT) net
Cc: pgsql-novice (AT) postgresql (DOT) org

mike,

fwiw, pgadmin worked well for me and was very easy to set up once i
put the "-i" flag in my postmaster call. never tried pgaccess so
can't comment.

Mike <1100100 (AT) gmail (DOT) com> wrote:

Hi Hari,

Yes, I have been using ssh and bash shell to access my postgres
database using psql -d mydatabase.

Now I'd like to try a Gui frontend to aid the learning process and
help visualize some aspects of building sql queries, etc.

I was going to try pgAccess first because it appears to be a very
small, easy to use app. Maybe later on I would try pgAdmin.

Mike

On Thu, 2 Sep 2004 10:36:24 -0500, Hari Bhanujan
wrote:

Quote:

I know that after ssh you can use psql without any problems.

What are you using for postgresql - pgadmin..

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

________________________________
Do you Yahoo!?
Win 1 of 4,000 free domain names from Yahoo! Enter now.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

#4

Mike wrote:

Quote:

Hi Ron,

I've got the puTTY via ssh working well.
So, you're saying I can puTTY via ssh to connect to the linux box and
postgresql server, and then start pgAdminIII and I'll be able to use
the GUI app. with the postgres database.

Once you putty into the box (assuming you are user postgres, or whoever

owns the postmaster), you
can psql into the database eg psql myDB
You don't need to putty into the box to use pgAdmin, you just tell
pgAdmin the ip address, user (postgres),
and password (unless it's trusted). This works as long as you've allowed
access from your remote ip address
eg in /data/pg_hba.conf you might have something like this
host all all 24.233.244.45 255.255.255.255 trust
this line trusts all users connecting from 24.233.244.45, so for example
pgAdmin could gain db access.

Quote:

No Cygwin necessary for this situation?

No cygwin needed.

Quote:

Please let me know if I'm understanding this correctly when you have a moment.

Thanks for your guidance and input.

Mike

Ron

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

#5

Ron,

Shuper-Shweet.
As soon as I can peel myself away from other administrative duties,
I'm going to get this fired up.

Thanks for taking the time.

Mike

On Thu, 02 Sep 2004 10:34:59 -0700, Ron St-Pierre <rstpierre (AT) syscor (DOT) com> wrote:

Quote:

Mike wrote:

Hi Ron,

I've got the puTTY via ssh working well.
So, you're saying I can puTTY via ssh to connect to the linux box and
postgresql server, and then start pgAdminIII and I'll be able to use
the GUI app. with the postgres database.

Once you putty into the box (assuming you are user postgres, or whoever
owns the postmaster), you
can psql into the database eg psql myDB
You don't need to putty into the box to use pgAdmin, you just tell
pgAdmin the ip address, user (postgres),
and password (unless it's trusted). This works as long as you've allowed
access from your remote ip address
eg in /data/pg_hba.conf you might have something like this
host all all 24.233.244.45 255.255.255.255 trust
this line trusts all users connecting from 24.233.244.45, so for example
pgAdmin could gain db access.

No Cygwin necessary for this situation?

No cygwin needed.

Please let me know if I'm understanding this correctly when you have a moment.

Thanks for your guidance and input.

Mike

Ron

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

#6

mike, i have my development db on my actual laptop. however, the pgadmin "login" screen asked for some input... i recall inputting "localhost", "5432" and maybe some other information (actually, this info was defaulted). I'd bet you can configure it so that it did recognize your linux box. maybe "localhost" would have to be changed to the name of your linux box.

i have some some test sql statements that i could e-mail you should you want to peruse them and see if you can figure them out. you can input them into pgadmin (probably pgaccess, but i don't know) and create some tables and then query them.

i'm not sure what all this ssh stuff is about, though. i haven't had to learn about it yet.

Mike <1100100 (AT) gmail (DOT) com> wrote:
Hi Op,

Thanks for your response.
Were you able to use it remotely from a winbox like I am trying to do?
And you were using it to connect to your db by ssh too?

Tell me more.......I want to try this ASAP.

Thanks for the guidance.

Mike

----- Original Message -----
From: operationsengineer1 (AT) yahoo (DOT) com
Date: Thu, 2 Sep 2004 08:41:54 -0700 (PDT)
Subject: Re: [NOVICE] pgAccess via ssh?
To: Mike <1100100 (AT) gmail (DOT) com>, hbhanujan (AT) sbcglobal (DOT) net
Cc: pgsql-novice (AT) postgresql (DOT) org

mike,

fwiw, pgadmin worked well for me and was very easy to set up once i
put the "-i" flag in my postmaster call. never tried pgaccess so
can't comment.

Mike <1100100 (AT) gmail (DOT) com> wrote:

Hi Hari,

Yes, I have been using ssh and bash shell to access my postgres
database using psql -d mydatabase.

Now I'd like to try a Gui frontend to aid the learning process and
help visualize some aspects of building sql queries, etc.

I was going to try pgAccess first because it appears to be a very
small, easy to use app. Maybe later on I would try pgAdmin.

Mike

On Thu, 2 Sep 2004 10:36:24 -0500, Hari Bhanujan
wrote:

Quote:

I know that after ssh you can use psql without any problems.

What are you using for postgresql - pgadmin..

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

________________________________
Do you Yahoo!?
Win 1 of 4,000 free domain names from Yahoo! Enter now.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

#7

Ron St-Pierre wrote:

Quote:

You don't need to putty into the box to use pgAdmin, you just tell
pgAdmin the ip address, user (postgres),
and password (unless it's trusted). This works as long as you've allowed
access from your remote ip address
eg in /data/pg_hba.conf you might have something like this
host all all 24.233.244.45 255.255.255.255 trust
this line trusts all users connecting from 24.233.244.45, so for example
pgAdmin could gain db access.

No Cygwin necessary for this situation?

No cygwin needed.

However, in that case the DB connection will _not_ be
through ssh, so it will be unencrypted and insecure.

From Mike's initial mail I got the impression that he
wanted the connection to the PostgreSQL server to go
through ssh (which is basically a good thing, because
you get ssh's authentication and encryption features).

Best regards
Oliver

--
Oliver Fromme, secnetix GmbH & Co KG, Oettingenstr. 2, 80538 München
Any opinions expressed in this message may be personal to the author
and may not necessarily reflect the opinions of secnetix in any way.

Quote:

Can the denizens of this group enlighten me about what the
advantages of Python are, versus Perl ?
"python" is more likely to pass unharmed through your spelling

checker than "perl".
-- An unknown poster and Fredrik Lundh

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

#8

On Fri, 3 Sep 2004 13:09:30 +0200 (CEST), Oliver Fromme
<olli (AT) lurza (DOT) secnetix.de> wrote:

Quote:

However, in that case the DB connection will _not_ be
through ssh, so it will be unencrypted and insecure.

From Mike's initial mail I got the impression that he
wanted the connection to the PostgreSQL server to go
through ssh (which is basically a good thing, because
you get ssh's authentication and encryption features).

Youch. You're absolutely on the spot. I forgot that by using pgAdmin
directly, I'll be giving up a secured/encrypted connection by way of
ssh. The secured connection is very important. Oh well, I suppose
becoming more proficient with postgresql from the console can't be a
bad thing.

Mike

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

#9

this purports to explain how to connect via ssh (haven't tried it)...

http://www.scosug.org/materials/20020623/
SSH/OpenSSH method:Use the -L option to ssh to create a tunnel between the client system and the remote system. This is transparent to PostgreSQL, which will see the connection as coming from its local system. You must account for this in your pg_hba.conf file.

Example:
First, set up the tunnel in one terminal...
ssh -L 4001:remotehost:5432 user@remotehost

Then, in another terminal, use psql to connect to the local port (4001) specified in the ssh command:
psql -h localhost -p 4001 mydatabase

Assuming this works, can one then use pgaccess to to "tunnel" through the ssh tunnel? Is just changing the port to 4001 instead of 5432 enough to get access to the remote db through pgaccess' login screen?

tia...

Oliver Fromme <olli (AT) lurza (DOT) secnetix.de> wrote:

Ron St-Pierre wrote:

Quote:

You don't need to putty into the box to use pgAdmin, you just tell
pgAdmin the ip address, user (postgres),
and password (unless it's trusted). This works as long as you've allowed
access from your remote ip address
eg in /data/pg_hba.conf you might have something like this
host all all 24.233.244.45 255.255.255.255 trust
this line trusts all users connecting from 24.233.244.45, so for example
pgAdmin could gain db access.

No Cygwin necessary for this situation?

No cygwin needed.

However, in that case the DB connection will _not_ be
through ssh, so it will be unencrypted and insecure.

From Mike's initial mail I got the impression that he
wanted the connection to the PostgreSQL server to go
through ssh (which is basically a good thing, because
you get ssh's authentication and encryption features).

Best regards
Oliver

--
Oliver Fromme, secnetix GmbH & Co KG, Oettingenstr. 2, 80538 München
Any opinions expressed in this message may be personal to the author
and may not necessarily reflect the opinions of secnetix in any way.

Quote:

Can the denizens of this group enlighten me about what the
advantages of Python are, versus Perl ?
"python" is more likely to pass unharmed through your spelling

checker than "perl".
-- An unknown poster and Fredrik Lundh

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

---------------------------------
Do you Yahoo!?
New and Improved Yahoo! Mail - 100MB free storage!

#10

operationsengineer1 (AT) yahoo (DOT) com wrote:

Quote:

[...]
Example:
First, set up the tunnel in one terminal...
ssh -L 4001:remotehost:5432 user@remotehost

Then, in another terminal, use psql to connect to the local port
(4001) specified in the ssh command:
psql -h localhost -p 4001 mydatabase

That's exactly what I explained in a previous message in
this thread.

Quote:

Assuming this works, can one then use pgaccess to to "tunnel" through
the ssh tunnel? Is just changing the port to 4001 instead of 5432
enough to get access to the remote db through pgaccess' login screen?

Yes, you connect to the local port (in the above example it
would be port 4001 on localhost). The ssh tunnel will then
pick it up and forward it to port 5432 on the remote host.
For the PostgreSQL server (which must have been configured
to accept TCP conections), the incoming connection will
appear as if it comes from localhost.

See my earlier message in this thread ...

Best regards
Oliver

--
Oliver Fromme, secnetix GmbH & Co KG, Oettingenstr. 2, 80538 München
Any opinions expressed in this message may be personal to the author
and may not necessarily reflect the opinions of secnetix in any way.

"One of the main causes of the fall of the Roman Empire was that,
lacking zero, they had no way to indicate successful termination
of their C programs."
-- Robert Firth

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster