Re: Embedding postgresql in my application

On Sat, December 16, 2006 06:25, Murray Cumming wrote:
> I'd like my application (Glom) to contain its own PostgreSQL instance,
> either by linking directly to a copy of the PostgreSQL code or, maybe
> more ideally, by starting its own instance of PostgreSQL as a separate
> process.

Then you'll have to go with "starting its own instance." There is a big
difference between databases that are designed to be embedded (like
sqlite) and ones that run stand-alone (like postgres).

It's not very hard. The only real complication is in being a good
neighbour if there already is a working postgres installation on the
system. What you do is ensure that the server is installed on the system
normally, then create your own instance that runs independently of any
other instances on the system.

It's probably easiest if you do this under its own user identity. You'll
probably want to create a dedicated user for the application that runs a
single, shared server instance for all users (and put the data in
someplace like /var/lib/myapp/data). You don't really need a dedicated
user, but it's safer and it means you can't accidentally mess up the rest
of the system while you experiment. Apart from installing postgres, you
don't need superuser rights at all.

Let's say you're in a shell running under the user identity that will own
the database, and you have a directory DBDIR that that user owns. You'll
want to set up the database with a local, Unix-domain socket. The basics
of getting things running are:

1. When installing your application, set up its database instance.

mkdir -p "$DBDIR/data"
initdb -D "$DBDIR/data"

See the initdb manpage for details; you may want to specify the database's
encoding for example. Bear in mind that "ASCII" really means "ASCII"--if
you choose that as an encoding, you can only safely use the basic 7-bit
ASCII character set. You're more likely to want unicode.

Another useful option to initdb is how local users will be able to connect
to the database's socket. The most useful choices for you are probably
"ident sameuser" (always use system user name as database user name as
well) or "trust" (allow any user to log into the database). You may also
want to restrict access rights to the socket itself.

2. Start the database server. You need to do this both for the next
installation step and when your application is run. Run the server under
your dedicated user identity, which also owns the database files. Do this
in a separate process, e.g. by running it in the background.

In practice you'll want to wrap this in some pidfile logic to keep track
of whether the database is running. That way you can keep the database
running after your application exits, and you won't need to start it up
again if your application is run anew.

postmaster -k "$DBDIR" -h ''

The -k option says to create a socket in "$DBDIR". The socket's file name
will start with a dot, so most directory viewer programs will hide it from
view by default. The -h option (followed by an empty string!) sets an
empty hostname. This tells the postmaster not to listen on any network
interfaces. Not only is this more secure; it also prevents conflicts if
there is already a postgres instance running on its default TCP port.

3. Create a database, to be managed by your server instance. You can have
a single database for your application, or separate per-user databases, or
a combination where some data is in a shared database and other data is
kept in per-user databases. This connects to the server instance to do
its work, so make sure your postmaster is running.

createdb myappdb -h "$DBDIR/data"

The -h option specifies the host name to connect to. Of course
$DBDIR/data is not a hostname, but it will begin with a slash and this is
recognized as a special case, meaning "connect to the local socket in this
directory."

4. Create database user identities if needed. If your application always
runs under its own dedicated user identity, choose "ident sameuser"
authentication in initdb above and you won't need to create any users
here. If your application can run under many user identities but will
always log into the database under the same user identity (probably not a
good idea though), use "trust" authentication. But if your application
can run under many different user identities and each should have its own
database user, use "ident sameuser" and create a database user for every
system user when that user first runs the application:

createuser -h "$DBDIR/data" \
--no-superuser \
--no-createrole \
--no-createdb \
$USERNAME

If you want to give each of these user identities its own database, create
that at the same time:

createdb -h "$DBDIR/data" \
-O $USERNAME
$USERNAME

You'll still have to run both of these commands under your dedicated user
identity, by the way.

5. Connect to your database! Once your application is running, it can
connect to your database by specifying "$DBDIR/data" as a host name for
the connection. You won't need to specify a user name unless you want to
log into the database under a different name than the user identity the
application is running under. If you set up authentication the way I've
described, there's no need to provide a password.

HTH,

Jeroen