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/

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!"

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!

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/&gt;
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