Re: Embedding postgresql in my application

On Sat, December 16, 2006 18:13, Murray Cumming wrote:

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

Resources and speed. If this is a multi-user application, then starting a
database instance for every user who runs the application is probably
unnecessarily wasteful.

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

You can always just give every user his own database in a single, shared
postgres instance.

There is no issue of ports, really. The way I've described it, you do
*not* need a TCP port. Every instance of postgres is running on its own
socket, which appears in the filesystem in a place of your choosing.

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

That should work fine, but yes, then you do need a separate postgres
instance running for each of those directories.

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

Yes. Except for the createuser: if user X owns these directories and
postgres is started under user identity X, then X is automatically (well,
by default) the superuser for that database. No need to create it.

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

There's the configuration files, of course, in /etc/postgresql*. You'll
have to look up the necessary options to replace those with local
versions.

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

That doesn't mean that you have to have passwords, though. With "ident
sameuser" you simply rely on the system's authentication--if the system
says you're user X, then the database accepts you as X, but if the system
says you're not X, then the database will not accept you as X either.
Overall that's probably safer, not less safe, than requiring the user to
enter a password to log into the database. And it's lots safer than
keeping a password stored in a file somewhere.

Jeroen