Currently, Roundup attempts to drop a database when you call the 'init'
command in the admin console if the database has already been created.
This requires additional permissions to database user/role, which will
only really be used once.

This does not take into account that the database may have been
intentionally created in advance or that the database user may not have
the necessary privileges. In fact, the same logic is used for server
based databases and for sqlite/anydbm.

This discussion was started in issue2551299. A new issue has been opened
because the database creation bypass is not specific to postgresql.

> John Rouillard added the comment:
> 
>>On the one hand, it
>>is better to be able to permanently disable database creation. On the
>>other hand, the ability to temporarily disable this feature may also be
>>important, and in my case I could just as easily use a flag.
> 
> I don't like to build too much magic into the code. What do you think
> of Roundup trying to nuke() the db. If it finds that the db exists but
> fails to delete it because of missing permissions it skips the
> delete/create and continues with the rest of the schema setup, data
> load etc.

As I mentioned earlier, creating a database is not usually a feature of
a web application. Of course, Roundup is more than just a web
application, but the first thing people expect from an issue tracking
system is that it can be interacted with using a web interface.

So even if a web application doesn't create the database by default, I
think it's okay. If an application skips database creation if it doesn't
have permissions, that's okay too.

At least this applies to server based databases. I assume that Roundup
inherits the same logic for them as for anydbm and sqlite. But these are
slightly different situations that need to be handled differently.

> The delete error raises psycopg2.errors.InsufficientPrivilege. I don't
> need to parse the exception args/message. One problem is what happens
> if the db isn't empty. I think it will fail trying to create
> tables. So the data will be ok but the error message will point to a
> table creation issue and not an inability to drop/create the db.

I think we can look at what web applications typically do when they
successfully connect to a database. Yes, they are trying to create
tables where they will write data. Some tables may be created by plugins
(if the application uses plugins), so the main application may not know
about them and need not do anything with them.

> It is possible to have other tables in the Roundup db and work with
> them from Roundup using psycodb2 (via the db connection parameter)
> directly. Roundup shouldn't touch those tables. I suppose using the
> schema table we could figure out what tables should exist, but indexes
> and other things would be left around. So actively trying to drop
> tables to clean the db doesn't seem like a good idea.

I completely agree with you.

Just FYI I have dusted off issue2550852 which adds first class support for
using PostgreSQL schemas to segment databases. I've updated the patch and
am working on getting the postgresql test suite working for it.

With this patch, specifying RDBMS_NAME = roundup.myschema will not create
the roundup database, instead init will delete/create the defined schema.

So if you are working in an environment where the DB is pre-created and the
roundup db user is granted create permissions on the db you should be all set.
The schema will be created and populated.

----

Getting this to work with a pg_service.conf file is still a work in progress
because there is no good way to discover the schema without parsing a string.

----

My thought is to change the logic for init when using MySQL or a schemaless PostgreSQL db
is to:

  1) try to drop the db
  2) if this fails with permission error (hopefully the db driver for MySQL and
     other db's return a suitably narrow exception) or a database does not exist
     error
  3) try to create tables and load data
  4) if create table or anything else fails, exit reporting that it must have an
     empty database to start with.

Any other failure in step 2 raises the failure error.

I would like to keep the ability to create a database for 2 reasons:

  1) Historically this has worked well when using a dedicated server for multiple
     roundup db's. Create the user and everything else is SQLless.

  2) There is a parallel between creating the database and creating the schema
     in PostgreSQL. In neither case is the roundup db instance creation done using
     SQL. Not having to use SQL to do things has been mentioned before as a win.
  

The documentation for creating users and setting grants/permissions is lacking for
both MySQL and PostgreSQL. They both discuss db native setup commands under running the
test suite. Nothing is said about commands to run for a production deployment.

Database directions to:

  1) create a user
  2) add/remove CREATEDB rights
  3) (PG) grant the user the ability to create a schema in a db
  4) (mysql) the grant to create/drop tables

should be linked to (or incorporated into) installation.txt. The existing installtion.txt
points to mysql.txt and postgres.txt so these are the likely places to put it.

Ivanov does this sound ok?