Thread: EMBEDDED PostgreSQL

Tope Akinniyi wrote:
> If I may ask, is there any plan for embedded PostgreSQL database

No.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Do you need something from PostgreSQL that Firebird doesn't have?  My
experience has been that Firebird/Interbase is a fairly complete, ACID
compliant, low/no maintenance back end for single workstation applications.

On the other hand, you can make any RDBMS into an embedded database with
enough behind the scenes scripting, cron jobs, etc.  The autovacuum feature
is a big help in the regard.  You can tune once, deploy many times.  You
can throw away anything you don't need to make smaller distribution media.

I've done this with Oracle, believe it or not, including automatic
replication when two workstations see each other on the LAN.  I've haven't
had a chance to try it with PostgreSQL because no customer has accepted a
proposal yet.  If it can be done with Oracle, it can be done with
PostgreSQL.

Rick



                     
                      Tope Akinniyi
                     
                      <topeakinniyi@yahoo.co.        To:       pgsql-general@postgresql.org
                     
                      uk>                            cc:
                     
                      Sent by:                       Subject:  [GENERAL] EMBEDDED PostgreSQL
                     
                      pgsql-general-owner@pos
                     
                      tgresql.org
                     

                     

                     
                      01/25/2005 07:21 AM
                     

                     

                     




Hi,

If I may ask, is there any plan for embedded PostgreSQL database as we have
it in Firebird database?  In Firebird embedded a compact engine of the
database that can only accept connections from localhost (127.0.0.1) and
easily distributable with single user applications exists.

Any plan for such?


ALL-NEW Yahoo! Messenger - all new features - even more fun!

Martha Stewart called it a Good Thing when topeakinniyi@yahoo.co.uk (Tope Akinniyi) wrote:
> If I may ask, is there any plan for embedded PostgreSQL database as
> we have it in Firebird database?  In Firebird embedded a compact
> engine of the database that can only accept connections from
> localhost (127.0.0.1) and easily distributable with single user
> applications exists.

You can do that with PostgreSQL by configuring pg_hba.conf to only
accept connections from localhost.

And there is no problem with the notion of creating a database in a
local directory.

None of this requires any change.
--
output = reverse("moc.liamg" "@" "enworbbc")
http://www3.sympatico.ca/cbbrowne/postgresql.html
Signs of a Klingon Programmer #11: "This machine is a piece of GAGH! I
need dual Pentium processors if I am to do battle with this code!"

The trick is making database administration invisible to the user.  Since
Firebird requires no administration, it's easy.  The single file database
architecture in Firebird is also easy since you generally have only one
drive.

The only administration item presented to the user should be a dialog that
displays available backup media, the databases (user named) on the media,
and an archive and restore button.  Supporting flash keys and CD burners as
archive devices is what Martha would definitely call a Good Thing.  They
usually have enough space for a compressed database used by a workstation
application without splitting, though you can support that too (e.g.
"Please insert disk two").

Rick



                      Christopher Browne
                      <cbbrowne@acm.org>             To:       pgsql-general@postgresql.org
                      Sent by:                       cc:
                      pgsql-general-owner@pos        Subject:  Re: [GENERAL] EMBEDDED PostgreSQL
                      tgresql.org


                      01/25/2005 08:44 AM






Martha Stewart called it a Good Thing when topeakinniyi@yahoo.co.uk (Tope
Akinniyi) wrote:
> If I may ask, is there any plan for embedded PostgreSQL database as
> we have it in Firebird database?  In Firebird embedded a compact
> engine of the database that can only accept connections from
> localhost (127.0.0.1) and easily distributable with single user
> applications exists.

You can do that with PostgreSQL by configuring pg_hba.conf to only
accept connections from localhost.

And there is no problem with the notion of creating a database in a
local directory.

None of this requires any change.
--
output = reverse("moc.liamg" "@" "enworbbc")
http://www3.sympatico.ca/cbbrowne/postgresql.html
Signs of a Klingon Programmer #11: "This machine is a piece of GAGH! I
need dual Pentium processors if I am to do battle with this code!"

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

On Jan 25, 2005, at 8:44 AM, Christopher Browne wrote:
> You can do that with PostgreSQL by configuring pg_hba.conf to only
> accept connections from localhost.
>
> And there is no problem with the notion of creating a database in a
> local directory.
>
> None of this requires any change.


But on Windows 8.0 you can't run the postmaster with an administrative
account, correct? I really wish this was configurable in the PostgreSQL
settings (of course, defaulting to the way it is now).


John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

desoi@pgedit.com (John DeSoi) writes:
> On Jan 25, 2005, at 8:44 AM, Christopher Browne wrote:
>> You can do that with PostgreSQL by configuring pg_hba.conf to only
>> accept connections from localhost.
>>
>> And there is no problem with the notion of creating a database in a
>> local directory.
>>
>> None of this requires any change.
>
> But on Windows 8.0 you can't run the postmaster with an
> administrative account, correct? I really wish this was configurable
> in the PostgreSQL settings (of course, defaulting to the way it is
> now).

I haven't the foggiest idea what you can do on Windows 8.0; I thought
they called it Windows XP or Windows 2000.

I'm making the Unix-flavoured assumptions that it's cheap and easy to
create an extra directory and to spawn an extra process for a
postmaster in a user's own process space.

That may vary somewhat for the more VMS-like model of Windows NT...
--
"cbbrowne","@","ca.afilias.info"
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)

On Jan 25, 2005, at 1:55 PM, Christopher Browne wrote:

> I haven't the foggiest idea what you can do on Windows 8.0; I thought
> they called it Windows XP or Windows 2000.

Sorry, I meant version 8.0 of PostgreSQL on Windows (any variant it
works on).

>
> I'm making the Unix-flavoured assumptions that it's cheap and easy to
> create an extra directory and to spawn an extra process for a
> postmaster in a user's own process space.
>
> That may vary somewhat for the more VMS-like model of Windows NT...


In general, it should be possible to just copy the right files and do
the same thing on Windows but the pgInstaller FAQ says

==
2.3) Why do I need a non-administrator account to  run PostgreSQL under?

When a hacker gains entry to a computer using a software bug in a
package, she gains the permissions of the user account under which the
service is run. Whilst we do not know of any such bugs in PostgreSQL,
we enforce the use of a non-administrative service account to minimise
the possible damage that a hacker could do should they find and utilise
a bug in PostgreSQL to hack the system.

This has long been common practice in the Unix world, and is starting
to  become standard practice in the Windows world as well as Microsoft
and  other vendors work to improve the security of their systems.
==

Again, I think this is fine as the default, but it would be nice if it
could be changed with a setting (rather than recompiling the source).
Not all Windows users are dummies about security and need PostgreSQL to
enforce security measures beyond those implemented on other platforms.


John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

John DeSoi <desoi@pgedit.com> writes:
>> 2.3) Why do I need a non-administrator account to  run PostgreSQL under?

> Again, I think this is fine as the default, but it would be nice if it
> could be changed with a setting (rather than recompiling the source).
> Not all Windows users are dummies about security and need PostgreSQL to
> enforce security measures beyond those implemented on other platforms.

Sorry, but any Windows user who thinks he doesn't need security measures
equivalent to (not "beyond") minimum Unix practice is a dummy about
security.  Take a look at this LOAD vulnerability we're in the midst of
patching, and ask yourself whether you aren't glad that it can't be used
to get admin privileges on your Windows box.

(John Heasman pointed out to me off-list that the LOAD hole *is* remotely
exploitable on Windows; details left as an exercise for the reader.)

            regards, tom lane

>>> 2.3) Why do I need a non-administrator account to  run
>PostgreSQL under?
>
>> Again, I think this is fine as the default, but it would be
>nice if it
>> could be changed with a setting (rather than recompiling the
>source).
>> Not all Windows users are dummies about security and need
>PostgreSQL to
>> enforce security measures beyond those implemented on other
>platforms.
>
>Sorry, but any Windows user who thinks he doesn't need
>security measures
>equivalent to (not "beyond") minimum Unix practice is a dummy about
>security.  Take a look at this LOAD vulnerability we're in the midst of
>patching, and ask yourself whether you aren't glad that it
>can't be used
>to get admin privileges on your Windows box.
>
>(John Heasman pointed out to me off-list that the LOAD hole
>*is* remotely
>exploitable on Windows; details left as an exercise for the reader.)

Actually, if you configure your box for high security, it's not.
Granted, not everybody does. But if you do, you're fine. It relies on
SMB connection out from your box, which can be disabled in several ways
(one of which is putting a firewall in front of your server, which
really isn't such a bad idea).

//Magnus

<snip>

>This has long been common practice in the Unix world, and is starting
>to  become standard practice in the Windows world as well as Microsoft
>and  other vendors work to improve the security of their systems.
>==
>
>Again, I think this is fine as the default, but it would be nice if it
>could be changed with a setting (rather than recompiling the source).

That can always be argued :-)


>Not all Windows users are dummies about security and need
>PostgreSQL to
>enforce security measures beyond those implemented on other platforms.

First of all, it does *not* enforce anything beyond what's enforced on
Unix. On Unix, it doesn't run as root. On Windows, it doesn't run as
Administrator.

If your users are running as administrators, then you *are* very naive
about security on your systems (I won't say dummy, but clearly not
making a significant effort). That's where you should fix the problem.

For an embedded database, one can argue that it's much less of an issue.
And if it was possible without making it a major hack, it would seem
reasonable to permit running it as administrator as long as only
localhost connections are provided (not by default, but possible. Not by
default because a ASP page or whatever still turns anything into a root
hole, but it could be configurable). But I don't think that can be done
in a non-intrusive way. And it'd just be a workaround the real issue
anyway.


//Magnus

John DeSoi wrote:
> But on Windows 8.0 you can't run the postmaster with an administrative
> account, correct? I really wish this was configurable in the PostgreSQL
> settings (of course, defaulting to the way it is now).

I think there have been several threads debating this issue in the past
(on whether Postgres should allow running as root if the user wants to).
And in Windows case it can also be argued that Windows XP is a "single
user" OS.

But then again running as root/Administrator is a _really bad_ idea, so
bad that I'm glad there are software like Apache or Postgres which
proactively discourage this practice by flat-out refusing to run as
root/Administrator.

In Windows, if the installing user is an administrator, I don't think
it's that hard to add several lines of code in your app's installer to
create a normal user for Postgres to run as.

--
dave

Richard_D_Levine@raytheon.com wrote:
> The trick is making database administration invisible to the user.  Since
> Firebird requires no administration, it's easy.  The single file database
> architecture in Firebird is also easy since you generally have only one
> drive.

The decision not to create an embedded Postgres version is never about
administration issue. If we want to, we can make Postgres administration
and configuration as minimum as possible by creating, say, a pg_autotune
daemon that monitors the OS, db activity, and usage pattern, and
automatically adjusts the kernel and/or db parameters. I think there's
something like this in Oracle 10g and perhaps someday there will be too
in Postgres.

It's about reliability. Running the app and dbms in the same process
space will not guarantee that bugs in app will not mess up the database.

And after all, the "Firebird requires no administration" statement is
more of a marketing gimmick anyway. Is it really 100% DBA-free? Can
Firebird automatically connect to newegg.com and buy an extra harddisk
if it runs out of disk space? :-)

To me Postgres is already pretty low in administrative demand as it is.
Not that it cannot be improved, of course.

--
dave

Hi Tom,

On Jan 25, 2005, at 4:35 PM, Tom Lane wrote:

> Sorry, but any Windows user who thinks he doesn't need security
> measures
> equivalent to (not "beyond") minimum Unix practice is a dummy about
> security.  Take a look at this LOAD vulnerability we're in the midst of
> patching, and ask yourself whether you aren't glad that it can't be
> used
> to get admin privileges on your Windows box.

So a vulnerability exists on Windows even if PostgreSQL is only
accepting local connections?

The poster asked about embedded databases and that is what I'm trying
to address. I realize this has been thoroughly hashed about in the
archives, but I don't recall any discussion of PotgreSQL 8 on Windows.

Thanks,


John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

On Jan 25, 2005, at 5:02 PM, Magnus Hagander wrote:

>> Again, I think this is fine as the default, but it would be nice if it
>> could be changed with a setting (rather than recompiling the source).
>
> That can always be argued :-)

I had a feeling it would be :)

>
>
>> Not all Windows users are dummies about security and need
>> PostgreSQL to
>> enforce security measures beyond those implemented on other platforms.
>
> First of all, it does *not* enforce anything beyond what's enforced on
> Unix. On Unix, it doesn't run as root. On Windows, it doesn't run as
> Administrator.

OK, perhaps I'm not comparing apples to apples. On OS X I have an
administrative account and I can run PostgreSQL just fine. So what you
are saying is an administrative account on Windows is more like root on
Unix.

>
> If your users are running as administrators, then you *are* very naive
> about security on your systems (I won't say dummy, but clearly not
> making a significant effort). That's where you should fix the problem.

Again, I was merely pointing out the issue for the original poster who
wanted an embedded database. On Windows there is currently no way to
drag any kind folder with PostgreSQL to the hard drive and run (local
connections only) if the user is an administrative user. And my guess
is that anyone that buys a Windows machine and sets it up themselves
has one account which is an administrative user.

Personally, I have no users administrative or otherwise. And the
Windows machine I typically use is not even connected to the internet
:).


John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

> > Sorry, but any Windows user who thinks he doesn't need security
> > measures equivalent to (not "beyond") minimum Unix practice
> is a dummy
> > about security.  Take a look at this LOAD vulnerability
> we're in the
> > midst of patching, and ask yourself whether you aren't glad that it
> > can't be used to get admin privileges on your Windows box.
>
> So a vulnerability exists on Windows even if PostgreSQL is
> only accepting local connections?

No. You need an *authenticated* connection to the database. If your web
interface is open to SQL Injection, you can get in thruogh that, but
else you need some kind of account and connecting permissions to the
database server.
pg_hba also protects you even if you allow connections elsewhere.


//Magnus

> >> Not all Windows users are dummies about security and need
> PostgreSQL
> >> to enforce security measures beyond those implemented on other
> >> platforms.
> >
> > First of all, it does *not* enforce anything beyond what's
> enforced on
> > Unix. On Unix, it doesn't run as root. On Windows, it
> doesn't run as
> > Administrator.
>
> OK, perhaps I'm not comparing apples to apples. On OS X I
> have an administrative account and I can run PostgreSQL just
> fine. So what you are saying is an administrative account on
> Windows is more like root on Unix.

Really? I'd call that a bug in the OSX port, but I don't know enough to
actually say that's really so. For example, what an "administrative
account" means on OSX. If the account has permissions to write all over
the place, then it should not be permitted tos tart the backend as that
user.

Administrator on windows is almost as powerful as root on unix, yes. And
it's trivial to get from administrator to local system, which has
everything root has.


> > If your users are running as administrators, then you *are*
> very naive
> > about security on your systems (I won't say dummy, but clearly not
> > making a significant effort). That's where you should fix
> the problem.
>
> Again, I was merely pointing out the issue for the original
> poster who wanted an embedded database. On Windows there is
> currently no way to drag any kind folder with PostgreSQL to
> the hard drive and run (local connections only) if the user
> is an administrative user. And my guess is that anyone that
> buys a Windows machine and sets it up themselves has one
> account which is an administrative user.

It definitly does not hold true for a corporate environment, where
almost nobody should be. As for home installations, that has long been
the case. But IIRC, if you install XP Home it recommends you create a
*non-admin* user. Suer, many don't, but at least it recommends you to do
so.


> Personally, I have no users administrative or otherwise. And
> the Windows machine I typically use is not even connected to
> the internet :).

Now *that* certainly helps security :-)

//Magnus

On Jan 26, 2005, at 17:11, Magnus Hagander wrote:

>> OK, perhaps I'm not comparing apples to apples. On OS X I
>> have an administrative account and I can run PostgreSQL just
>> fine. So what you are saying is an administrative account on
>> Windows is more like root on Unix.
>
> Really? I'd call that a bug in the OSX port, but I don't know enough to
> actually say that's really so. For example, what an "administrative
> account" means on OSX. If the account has permissions to write all over
> the place, then it should not be permitted tos tart the backend as that
> user.

Administrator accounts on Mac OS X are more powerful than Standard
accounts (for example, they can sudo, but not run root commands
directly), but they are definitely not root. Root, by default, is
disabled on Mac OS X.

Michael Glaesemann
grzm myrealbox com

On 25 Jan 2005 at 8:45, Richard_D_Levine@Raytheon.com wrote:

> Do you need something from PostgreSQL that Firebird doesn't have?  My
> experience has been that Firebird/Interbase is a fairly complete, ACID
> compliant, low/no maintenance back end for single workstation
> applications.

...except that it lacks a native boolean data type....I use Firebird a good
bit, but it's a pain having to work around this lack every time I create a
new database. I see on the Firebird website that it's been requested
as a feature, but I'm not holding my breath!

--Ray

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

> Running the app and dbms in the same process
> space will not guarantee that bugs in app will not mess up the database.

By embedded I thought we were saying *invisible to the user*.  I agree that
having the database back end linked into user processes is dangerous.  I
don't know if you can still do this in Firebird, but it was a link option
in Interbase 4.  We tried it and had all kinds of problems with mapping of
shared memory and Unix signal conflicts.  The application could go nuts and
trash the RDBMS' shared memory, or accidentally block its signals.  Debug
that!

Rick



                     
                      David Garamond
                     
                      <lists@zara.6.isreserve        To:       pgsql-general@postgresql.org
                     
                      d.com>                         cc:
                     
                      Sent by:                       Subject:  Re: [GENERAL] EMBEDDED PostgreSQL
                     
                      pgsql-general-owner@pos
                     
                      tgresql.org
                     

                     

                     
                      01/25/2005 06:15 PM
                     

                     

                     




Richard_D_Levine@raytheon.com wrote:
> The trick is making database administration invisible to the user.  Since
> Firebird requires no administration, it's easy.  The single file database
> architecture in Firebird is also easy since you generally have only one
> drive.

The decision not to create an embedded Postgres version is never about
administration issue. If we want to, we can make Postgres administration
and configuration as minimum as possible by creating, say, a pg_autotune
daemon that monitors the OS, db activity, and usage pattern, and
automatically adjusts the kernel and/or db parameters. I think there's
something like this in Oracle 10g and perhaps someday there will be too
in Postgres.

It's about reliability. Running the app and dbms in the same process
space will not guarantee that bugs in app will not mess up the database.

And after all, the "Firebird requires no administration" statement is
more of a marketing gimmick anyway. Is it really 100% DBA-free? Can
Firebird automatically connect to newegg.com and buy an extra harddisk
if it runs out of disk space? :-)

To me Postgres is already pretty low in administrative demand as it is.
Not that it cannot be improved, of course.

--
dave

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match