Re: Embedding postgresql in my application

On Sat, 2006-12-16 at 13:16 +0700, Jeroen T. Vermeulen wrote:
> 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.

What would be the problem with running it as the current user?

That would get a bit difficult if multiple users are running instances
of the application - at the least, the port would have to be chosen
dynamically. But it would keep things simple if the postgres instances
were separate.

> 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.

In this case, I'd actually like the location of the database data to be
different every time. The idea would be that it would all be contained
in a directory (or even a tarball archive, but that's another issue),
along with the .glom file. And opening the .glom file would start a
postgres instance with the database that's in that directory.

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

In my each-database-in-its-directory idea, I guess that I'd do both an
initdb (to create the cluster) and a createdb (to create the database,
in the cluster, after a createuser) once.

Then, each time the file/database is opened, I'd use postmaster to start
a postgres instance that uses the cluster in that directory.

Or do initdb and createdb change anything outside of the specified
directory? I mean, can I really move that directory around and just
start a postgres instance on it after it's moved, without changes. Are
there any absolute filepaths I should watch out for, for instance?

> 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.

Actually, I want passwords. This is not just for the internal use of the
application. I want Glom systems to be usable by multiple users
simultaneously. That's a big reason why I'm not using sqllite for this.

Many thanks for the clues.

--
Murray Cumming
murrayc(at)murrayc(dot)com
www.murrayc.com
www.openismus.com