Re: [Gnumed-devel] PostgreSQL questions

[Karsten Hilbert]

> > Is there any correspondence between a system account "postgres" and a
> user
> > (within postgreSQL) called "postgres"?

There is on a typical system but there doesn't have to be. The system
account is usually used for owning the on-disk database *files* and
running the PostgreSQL demon processes.

Another use on several Linux variants is to set up the system account
"postgres" such that

a) only local connections are possible
b) direct (no mapping) IDENT over domain sockets is
   used for authentication

That way unattended, passwordless, but still pretty secure running
of maintenance tasks, say via cron, becomes possible.

> > Can postgres tolerate (function with)
> > - a postgres user "postgres" in the absence of a system user (account)
> > postgres?

That should be doable but would require a bit more
administrational overhead.

> > - absence of a postgres user "postgres"?

No. Someone must own system schema objects. But it could be any other
name. Which would require fiddling with PostgreSQL source code, though.

> Not really, essentially the "postgres" user has to be the owner of the
> database

The GNUmed databases are (logically) owned by "gm-dbo". (The
files on disk are still owned by system user postgres, though.)

(Furthermore, gm-dbo is NOT granted the superuser right IIRC.)

>, in otherwords you could have a user named jbusser owning the db
> then you would have to have a postgres user named jbusser as the system
> account

A gm-dbo system account is not required. Still, gm-dbo owns the database
inside PG. It doesn't own the files at the OS level, however.

> > There was mention (in the postings about pgAdmin III) that the
> postgreSQL
> > account "postgres" would be unable to access the gnumed databases, on
> > account of some settings in one of the postgres configuration files.

In fact it would probably work anyway. I also advised against
doing it, however, as it could exert overly broad powers.

> > - isolating the managers of the "postgres" account from accidental or
> > inappropriate meddling with the gnumed schemas and

Not using "postgres" but rather some other more appropriately empowered
PG account would alleviate the risk of damaging something *other* than
the GNUmed schema.

Not using "gm-dbo" either would provide even greater protection but
will not give access to any and all aspects of the GNUmed schema.

Those gm-dbo only aspects are, however, far and few between and
probably rarely of relevance. I can't even cite them from memory.

> You can give another user the ability to create databases, and then you
> can
> create a user which can own the database but not create databases
> (implies being able to drop them as well.)

While I assume true I am not certain on the advantage over the
current setup with gm-dbo owning the entirety of the gnumed
schema and granting specific rights to other users. None other
can meddle with the db itself or the schema objects and such.

> > - such isolation depends on said managers either lacking (sudo) access
> to
> > the postgres config files or, if given said access, would agree by
> social
> > policy not to bypass the constraints?
> >
> > I generally set up pg so that the owner of the db does not have drop
> > privileges.

In GNUmed gm-dbo can drop the GNUmed databases. She can also create
databases. That way we can avoid having to use "postgres" for *anything*
other than properly creating gm-dbo (which does NOT have superuser
equivalence).

Karsten
-- 
Jetzt kostenlos herunterladen: Internet Explorer 8 und Mozilla Firefox 3 -
sicherer, schneller und einfacher! http://portal.gmx.net/de/go/atbrowser