Thread: Re: pgAccess via ssh?

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@sbcglobal.net> wrote:
> I know that after ssh you can use psql without any problems.
>
> What are you using for postgresql - pgadmin..
>

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:
> 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@postgresql.org)

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@yahoo.com <operationsengineer1@yahoo.com>
Date: Thu, 2 Sep 2004 08:41:54 -0700 (PDT)
Subject: Re: [NOVICE] pgAccess via ssh?
To: Mike <1100100@gmail.com>, hbhanujan@sbcglobal.net
Cc: pgsql-novice@postgresql.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@gmail.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:
> 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@postgresql.org)


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

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

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@syscor.com> wrote:
> 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
>
>

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@yahoo.com
Date: Thu, 2 Sep 2004 08:41:54 -0700 (PDT)
Subject: Re: [NOVICE] pgAccess via ssh?
To: Mike <1100100@gmail.com>, hbhanujan@sbcglobal.net
Cc: pgsql-novice@postgresql.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@gmail.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:
> 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@postgresql.org)


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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked o ur 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

Ron St-Pierre wrote:
 > 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.

 > 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

On Fri, 3 Sep 2004 13:09:30 +0200 (CEST), Oliver Fromme
<olli@lurza.secnetix.de> wrote:
 > 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

Ron St-Pierre wrote:
> 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.

> 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@postgresql.org)

operationsengineer1@yahoo.com wrote:
 > [...]
 > 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.

 > 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

now that i've gotten all the problems setting up my
development box out of the way, i need to focus in on
actually programming my applications.

i'm using php/apache/pgsql.  the way i see it, i have
three choices.

1. pg api - all my books cover this approach and it is
kinda cool. ;-)  downside is it locks you into pgsql
(not to big a deal) and you have to code like a
monster to get all your classes and functions
together.

2. pear - has some neat functions and classes pre made
- plug and play, as it were.  however, i'd still have
a lot to learn using this style of programming b/c
i've never used before.  i feel mor comfortable with
pg's api right now.

3. adodb - this just started sounding compelling about
30 minutes ago.  seems to be faster and more portable
than pear.  seems to have some nice functionality,
too.  not sure if it has the same level of
class/function support - need to read up on this.

as a rookie, i appreciate the wisdon gained over
windows broken on second floors as monitors get tossed
out of them... ;-)

if you have something to say that you think is of
value, please chime in.

tia...




__________________________________
Do you Yahoo!?
New and Improved Yahoo! Mail - 100MB free storage!
http://promotions.yahoo.com/new_mail

might as well throw mdb into the mix, too...

http://pear.php.net/package-info.php?package=MDB


--- operationsengineer1@yahoo.com wrote:

> now that i've gotten all the problems setting up my
> development box out of the way, i need to focus in
> on
> actually programming my applications.
>
> i'm using php/apache/pgsql.  the way i see it, i
> have
> three choices.
>
> 1. pg api - all my books cover this approach and it
> is
> kinda cool. ;-)  downside is it locks you into pgsql
> (not to big a deal) and you have to code like a
> monster to get all your classes and functions
> together.
>
> 2. pear - has some neat functions and classes pre
> made
> - plug and play, as it were.  however, i'd still
> have
> a lot to learn using this style of programming b/c
> i've never used before.  i feel mor comfortable with
> pg's api right now.
>
> 3. adodb - this just started sounding compelling
> about
> 30 minutes ago.  seems to be faster and more
> portable
> than pear.  seems to have some nice functionality,
> too.  not sure if it has the same level of
> class/function support - need to read up on this.
>
> as a rookie, i appreciate the wisdon gained over
> windows broken on second floors as monitors get
> tossed
> out of them... ;-)
>
> if you have something to say that you think is of
> value, please chime in.
>
> tia...
>
>
>
>
> __________________________________
> Do you Yahoo!?
> New and Improved Yahoo! Mail - 100MB free storage!
> http://promotions.yahoo.com/new_mail
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please
> send an appropriate
>       subscribe-nomail command to
> majordomo@postgresql.org so that your
>       message can get through to the mailing list
> cleanly
>




__________________________________
Do you Yahoo!?
Yahoo! Mail is new and improved - Check it out!
http://promotions.yahoo.com/new_mail

Hello,

My favourite is ADOdb, mainly as it seems to be the fastest of the DB
classes, has solid transaction support, a convenient quoting/escaping
method, and a whole host of other handy features (like generating a
form dropdown with the results of a query for you).  Caching queries
on the webserver is also nice, as you don't have to make the
round-trip to the database (like you would with MySQL's caching).

Most importantly is the XML goodies that are with the most current
releases of ADOdb, which allow you to reverse-engineer a schema
dynamically and then make alterations to that schema, and then apply
them to your database again.

Quite impressive.

If you do not like the syntax of ADOdb, then PEAR's DB is my second
recommendation, as it has just about everything you would need out of
a DB class.

-- Mitch

Tia,

> 1. pg api - all my books cover this approach and it is

> 3. adodb - this just started sounding compelling about

I'd say these are your two reasonable choices.   Regrettably, the Pear
database classes seem to have lost momentum to adodb lately.

We use our home-rolled stuff based on pg_connect, mostly because it was
written before Pear or Adodb were available.    If I were starting now, I'd
probably use Adodb.

--
Josh Berkus
Aglio Database Solutions
San Francisco

On Fri, 10 Sep 2004 10:30:09 -0700, Josh Berkus <josh@agliodbs.com> wrote:
> I'd say these are your two reasonable choices.   Regrettably, the Pear
> database classes seem to have lost momentum to adodb lately.

Then you need to update your reference to 'lately' ;-)

PEAR::DB has gone through great leaps and bounds (not just
functionality but documentation as well) - you should definitely
compare ADOdb and PEAR::DB before making a decision.

I like ADOdb's query caching, and also make use of the non-db goodies
(like generating HTML dropdown form elements from the result of a
query) as well as generating INSERT statements from form submissions
(without having to test to see if values were submitted or not).
Don't forget ADOdb's XML goodies that allow you to reverse engineer a
database, define changes, and then apply those changes (or repeat on
another platform entirely).

OTOH, PEAR::DB has awesome documentation, very good support for
prepared statements (great for many similar repeated statements), and
has also undergone pretty dramatic performance improvement.  The
maintainer is very friendly and approachable.

Here is another thought - I have seen folks start out thinking "Hey,
I'll just use the native pgsql functions, and wrap them in a class to
simplify the interface."  Next thing you know they are more-or-less
repeating the efforts of the two projects above, and usually with less
resources (and success).  So if you are going to wrap your calls in a
class, why not use one of the leaders?

-- Mitch, hoping this helps