Thread: running postgresql on a private machine accessing it from public web pages

We have a private machine that runs postgresql and have created a
database. We want to access that database from the web for plotting
using php. The web server and pages are on a public (different) machine
that does not have postgresql. Nor will they install it for is. The
postgresql database is also on the public machine (which the private
machine can access).

So, the question is, can someone go through these (public) web pages and
access the postgresql database if the postgresql server is running on
the private machine?

We have other data in the postgresql and would like to only have to use
one database types, i.e., postgresql.

Thanx,
keith

--
"Nonviolence is not a cover for cowardice, but it is the supreme virtue of the brave."  Mohandas Karamchand Gandhi
=======================================================
Keith D. Evans
Joint Center for Earth Systems Technology/UMBC
(301) 614-6282 (M,Tu)
(410) 455-5751 (W,Th,F)
http://www.jcet.umbc.edu/bios/evanmain.html
========================================================

Any opinions expressed in this email are not those of
NASA, or the Goddard Space Flight Center, or the Joint
Center for Earth Systems Technology or the University
of Maryland Baltimore County.

========================================================

> So, the question is, can someone go through these (public) web pages and
> access the postgresql database if the postgresql server is running on
> the private machine?
>
> We have other data in the postgresql and would like to only have to use
> one database types, i.e., postgresql.

If you are using proper host control then yes you can make it so that
the PHP user is only able to access the public data in the private
database. See here:

http://www.postgresql.org/docs/8.3/static/client-authentication.html
http://www.postgresql.org/docs/8.3/static/user-manag.html

Sincerely,

Joshua D. Drake

--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
   Consulting, Development, Support, Training
   503-667-4564 - http://www.commandprompt.com/
   The PostgreSQL Company, serving since 1997

Joshua,

Let me rephrase what I wrote. The database is on a public machine
(Linux), but the postgresql postmaster runs on a private machine
(Solaris 10).  Can we access the postgresql database through the public
machine, even though the postmaster is running from a (different)
private machine?

thanx
keith

Joshua D. Drake wrote:
>> So, the question is, can someone go through these (public) web pages and
>> access the postgresql database if the postgresql server is running on
>> the private machine?
>>
>> We have other data in the postgresql and would like to only have to use
>> one database types, i.e., postgresql.
>>
>
> If you are using proper host control then yes you can make it so that
> the PHP user is only able to access the public data in the private
> database. See here:
>
> http://www.postgresql.org/docs/8.3/static/client-authentication.html
> http://www.postgresql.org/docs/8.3/static/user-manag.html
>
> Sincerely,
>
> Joshua D. Drake
>
>

--
"Nonviolence is not a cover for cowardice, but it is the supreme virtue of the brave."  Mohandas Karamchand Gandhi
=======================================================
Keith D. Evans
Joint Center for Earth Systems Technology/UMBC
(301) 614-6282 (M,Tu)
(410) 455-5751 (W,Th,F)
http://www.jcet.umbc.edu/bios/evanmain.html
========================================================

Any opinions expressed in this email are not those of
NASA, or the Goddard Space Flight Center, or the Joint
Center for Earth Systems Technology or the University
of Maryland Baltimore County.

========================================================

On Thu, 2009-05-21 at 13:49 -0400, Keith D. Evans wrote:
> Joshua,
>
> Let me rephrase what I wrote. The database is on a public machine
> (Linux), but the postgresql postmaster runs on a private machine
> (Solaris 10).  Can we access the postgresql database through the public
> machine, even though the postmaster is running from a (different)
> private machine?

A postmaster must be running on whatever database you are trying to
access. You could use replication such as Slony to mirror the linux
database to the solaris one.

Joshua D. Drake

--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
   Consulting, Development, Support, Training
   503-667-4564 - http://www.commandprompt.com/
   The PostgreSQL Company, serving since 1997

On 21/05/2009 18:49, Keith D. Evans wrote:

> The database is on a public machine (Linux), but the postgresql
> postmaster runs on a private machine (Solaris 10).

That doesn't make a lot of sense, unless you've got *two* postmasters
running, one on each machine, or maybe you've created a tablespace over
NFS or some such.

Can you explain more clearly what you mean by the above?


Ray.


------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

We usually run postgresql on a private machine (gravity) but due to
space, we have moved the database to a different machine through afs (a
local network at the university). We do not want the private machine to
be accessible to outside users, so our web pages are on the university's
public web pages. They have mysql, but won't install postgresql, which
we've been using on gravity for years.

We want to add a database where users can get and plot data using php
over the internet. But since the public computers don't have postgresql,
can we have postgresql running on gravity allowing users accessing
through the internet on the public pages to access the data?

thanx,
keith


Raymond O'Donnell wrote:
> On 21/05/2009 18:49, Keith D. Evans wrote:
>
>
>> The database is on a public machine (Linux), but the postgresql
>> postmaster runs on a private machine (Solaris 10).
>>
>
> That doesn't make a lot of sense, unless you've got *two* postmasters
> running, one on each machine, or maybe you've created a tablespace over
> NFS or some such.
>
> Can you explain more clearly what you mean by the above?
>
>
> Ray.
>
>
> ------------------------------------------------------------------
> Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
> rod@iol.ie
> Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
> ------------------------------------------------------------------
>
>
>

--
"Nonviolence is not a cover for cowardice, but it is the supreme virtue of the brave."  Mohandas Karamchand Gandhi
=======================================================
Keith D. Evans
Joint Center for Earth Systems Technology/UMBC
(301) 614-6282 (M,Tu)
(410) 455-5751 (W,Th,F)
http://www.jcet.umbc.edu/bios/evanmain.html
========================================================

Any opinions expressed in this email are not those of
NASA, or the Goddard Space Flight Center, or the Joint
Center for Earth Systems Technology or the University
of Maryland Baltimore County.

========================================================

On Thu, May 21, 2009 at 12:23 PM, Keith D. Evans <evans@umbc.edu> wrote:
> We usually run postgresql on a private machine (gravity) but due to space,
> we have moved the database to a different machine through afs (a local

Wait, the whole database, including a postmaster running on the other
machine?  Or just that you're storing the database files there.  Where
the files are stored is inconsequential, except as applies to
reliability.  Where the postmaster lives is all that matters.

> network at the university). We do not want the private machine to be
> accessible to outside users, so our web pages are on the university's public
> web pages.

I assume you have php on the public web servers.

> They have mysql, but won't install postgresql, which we've been
> using on gravity for years.

Fine, there's really no great reason to install PostgreSQL on the same
machines that are running web pages anyway.  As long as those machines
can reach you pgsql server you're gold.

> We want to add a database where users can get and plot data using php over
> the internet. But since the public computers don't have postgresql, can we
> have postgresql running on gravity allowing users accessing through the
> internet on the public pages to access the data?

Yeah, you want some kind of connection opened up between those
machines that let's pgsql get through.  You can use an ssh tunnel, a
vpn, or something else if you can't get a direct route opened up by
the network guys for security reasons.  If you'll have to go through a
machine to get to the pgsql machine, look up two hop ssh tunnel, it's
pretty easy.

But I wonder why they won't install pgsql for you.  Maybe they're
afraid of it raising their workload too much?  If you offer to feed it
and take care of it, maybe they'll let you.

On Thu, May 21, 2009 at 1:04 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Thu, May 21, 2009 at 12:23 PM, Keith D. Evans <evans@umbc.edu> wrote:
>> We usually run postgresql on a private machine (gravity) but due to space,
>> we have moved the database to a different machine through afs (a local
>
> Wait, the whole database, including a postmaster running on the other
> machine?  Or just that you're storing the database files there.  Where
> the files are stored is inconsequential, except as applies to
> reliability.  Where the postmaster lives is all that matters.
>
>> network at the university). We do not want the private machine to be
>> accessible to outside users, so our web pages are on the university's public
>> web pages.
>
> I assume you have php on the public web servers.
>
>> They have mysql, but won't install postgresql, which we've been
>> using on gravity for years.
>
> Fine, there's really no great reason to install PostgreSQL on the same
> machines that are running web pages anyway.  As long as those machines
> can reach you pgsql server you're gold.
>
>> We want to add a database where users can get and plot data using php over
>> the internet. But since the public computers don't have postgresql, can we
>> have postgresql running on gravity allowing users accessing through the
>> internet on the public pages to access the data?
>
> Yeah, you want some kind of connection opened up between those
> machines that let's pgsql get through.  You can use an ssh tunnel, a
> vpn, or something else if you can't get a direct route opened up by
> the network guys for security reasons.  If you'll have to go through a
> machine to get to the pgsql machine, look up two hop ssh tunnel, it's
> pretty easy.
>
> But I wonder why they won't install pgsql for you.  Maybe they're
> afraid of it raising their workload too much?  If you offer to feed it
> and take care of it, maybe they'll let you.

P.s. I'd still build a pgsql machine that did nothing but pgsql and
ran in the hosting center if I was gonna have pgsql there.

Keith D. Evans wrote:
> We usually run postgresql on a private machine (gravity) but due to
> space, we have moved the database to a different machine through afs (a
> local network at the university). We do not want the private machine to
> be accessible to outside users, so our web pages are on the university's
> public web pages. They have mysql, but won't install postgresql, which
> we've been using on gravity for years.
>
> We want to add a database where users can get and plot data using php
> over the internet. But since the public computers don't have postgresql,
> can we have postgresql running on gravity allowing users accessing
> through the internet on the public pages to access the data?
>
> thanx,
> keith
>

gravity = postgresql host = private
webpages = university web server = public

so, user accesses webpage on university web server, the webpage ( webapp )
has/or creates a connection to the postgresql server on gravity --> yes, user
can access webpage on public server and pull data from postgresql server on
private server ( as long as postgresql is configured to accept the connection
from the webserver and network connectivity exists between public and private
server )

Does this answer what you are asking?