Make a new database via the client using a predefined template or copy an existing database

Hello! It is my first time in this forum, and I come from the GNUHealth community. My question: Would it be possible to have a feature in the Tryton-client, that would just make a new database via the client, if I am already connected to a database? So that in fact it makes just a copy of the current database, or uses the same template of the current database, that was used during the first installation of that database?
IMHO that would be great and would help a lot for preconfiguring a database and then use the database-copies for different purposes, e.g. demo, production, testing, and so on … As far as I know such a feature existed in the past, but was then deleted due to security concerns. If this is the case, what are these concerns exactly? (Because for me it was possible to make a new database only with the help of experts, and I documented the way on my homepage in German, if somebody is interested.) Thank you very much!

You may be interested in the (unofficial) dbcopy module.

Hope it helps!

1 Like

Thank you, albert, that looks promising!

First copying a production database may have some problems for example you will have the production payment credential (Stripe, Braintree etc.). Also you may send email to real customer with the notification_email by mistake. Etc.

Such feature requires that the database user used by trytond to connect to the PostgreSQL server has the right to manage databases (create, drop etc.) This is not a good practice because if a security issue is found that allow to execute SQL queries, an attacker could drop the database for example.
Another difficulty is that for such service we need a second authentication mechanism the current one is per database. So this means duplication of security code which will not be very much tested nor used (creating database is not a common production task). Also the current authentication mechanism has a lot of protection against attacks which requires to store tentative in the database (which will not be possible for database creation).

This is simply solved by a DB administrator. On PostgreSQL make a copy of a database is pretty simple with createdb <new name> -T <existing database>. But it may requires to cleanup some data after such operation as explained above.

I would not activate such module on production. As far as I can see there is no access right defined for the wizard.

1 Like

This does not usually work because it requires that there are no connections to the existing database.

Yes, we only use if for one customer in a very small install. In larger setups, for me the right approach is to use something using orchestration tools, not Tryton itself. And probably moving to a different server (apart from the cleanup process you mentioned).

1 Like

Thank you, Cedric and Albert, for the discussion and explanation!

My use case for that is first to get familiar with the system and second to define e.g. diferent modules and localizations, especially if there are problems with installation, setup and configuration or localization.

At the moment I try to localize a GNUHealth instance from English to German on a Raspi4 with Tryton 5.0, but that has some issues. An update/upgrade needs nearly 3 hours with the empty database, so it would be great to have some copies where I can start from.

database management by users is a [very] bad idea indeed for any production system, or any online system. Not a matter of how big it is, but how exposed it is.
Using postgresql is very easy, and it just enforces the correct management of rights.
You may also create an application to do it from a web page or from a terminal. I provide a beautiful one on my free hosting but it won’t fit your very specific environment.

1 Like

I always create a SQL-dump from the database with pg_dump and move the dump over to my testserver (if needed). Create a new database with the right user and restore the dump into that database. Together with a new Tryton installatation in a virtual environment and off you go.

I’m using mailtrap for older installations and the new sendria for the newest versions. Just modify the trytond configuration to use the default port 1025 or define another port.

1 Like

Thank you very much to all!
Luis Falcon informed me too that there is the GNU Health Control Center that should also do the job. So I have a lot of possibilities now for trying and playing around … :smiley:

tar -xzf gnuhealth-control-latest.tar.gz
cat gnuhealth-control | psql
cat gnuhealth-control | grep -n pg
193: pg_dump $DB > $BACKDIR/backup_$DB_$BACKDATE || bailout
218: TRYTOND_PIDS=pgrep -f "^.*python.*trytond.*$"

Seems it doesn’t do what you ask for, but easy to complement with your requirements and a restore function.

1 Like

So far it worked nicely…however, Edgar was more thinking about the former ‘Create DB’ function that was dropped from the client some years ago.

1 Like

Yes, IIUC, Edgar is looking for psql -d newbase -f backup.file or better
As gnuhealth-control is very KISS, it is a good candidate to add it as a custom feature.

1 Like