[Date Prev][Date Next] [Thread Prev][Thread Next] [Date Index] [Thread Index]

Re: RFC: best practice creating database

On Thu, 2004-10-07 at 23:36, sean finney wrote:
> On Thu, Oct 07, 2004 at 03:38:59PM +0200, Philipp Matthias Hahn wrote:
> > What is consideres best practice when a package uses a SQL database
> > (mysql, postgresql) and needs to create its own catalog and/or tables?

I am the postgresql maintainer.  I am replying to this because I did not
see the original post.

> this is a very good question, which has not been conclusively answered
> and is still the subject of debate.  there have been a few threads about
> it, but i don't imagine anything solid will come from them while
> everyone's still focused on sarge.

There are a lot of issues related to database access and security.

What machine is the database on?  You should not assume that it is on
the local machine.  (That has implications for dependencies too; if the
database can be remote, you don't want your package to depend on
postgresql, but only on libpq3 (or possibly postgresql-client).)

What port are you to connect to? (The default is 5432, but there could
be multiple PostgreSQL postmasters on different ports.)

Which user is to access the database during the package installation? 
That user must be (or have been) defined as a database user.  Is the
database administrator the same person as the system administrator?  If
not, should the sysadmin create a new database user? (Obviously, he has
the power to assume the necessary id to do this, at least on the local
machine.)  If a database is to be created, the user needs database
creation privilege.

Which users will use the database?  If the database security is well
implemented, they will have to be GRANTed suitable access to every
object (preferably by adding them to a group and giving access to that
group).  Does the package provide a method of doing this?

What is the access method?  PostgreSQL's default authentication will
reject anyone attempting to access under any name other than the Unix
login id, which automatically excludes web-server based access.  We do
not want to have packages rewriting the database authentication setup,
especially not when some recommend using "trust" authentication to get
round access problems.

It seems to me that we need some kind of policy for database-using
packages to follow.  Do others agree about that?

> > [ ] Disable the package until someone has manually setup the database?
> > [ ] Ask a lot of questions via debconf and try to setup in postconf?
> i'd suggest the latter if it's not too complicated.  a few things to
> keep in mind though:
> - ask if they want to delete the database on purge 
> - make a backup of the database during upgrades, "Just In Case"

...using the correct utility: pg_dump for PostgreSQL.

> - don't store the pw in debconf, or at least ask the admin first
> if you think that it would be too complicated/flaky, i'd add a debconf
> note (of _low_ priority!) and put something in README.Debian.

Oliver Elphick                                          olly@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
     "Let no man say when he is tempted, I am tempted of 
      God; for God cannot be tempted with evil, neither 
      tempteth he any man; But every man is tempted, when he
      is drawn away of his own lust, and enticed."          
                                       James 1:13,14 

Reply to: