nine.ch provides a command line tool for managing databases and database users.
nine-manage-databases can be used via SSH. Currently, MySQL and PostgreSQL are supported.
The following command lists all available commands:
$ sudo nine-manage-databases -h
nine-manage-databases is capable of listing, creating, and dropping databases.
Choose the database type (DBMS)
If different database types are installed you can switch between the DBMS with the option
$ sudo nine-manage-databases --database-type=postgresql database list
This command lists all the PostgreSQL databases created by
List all databases
The following command lists all created databases:
$ sudo nine-manage-databases database list
Create a database
$ sudo nine-manage-databases database create --user=nmd_farmer nmd_ponyhof
This command creates a database named nmd_ponyhof. Notice the nmd_ prefix. The prefix separates the databases created by nine.ch from databases managed by
nine-manage-databases. The command also creates the user nmd_farmer which will automatically have read-write access to the database.
A secure password for the user will be generated and displayed in the command output. You can provide your own password interactively with the
If a user already exist, it is also possible to only create a database: use the
--database-only flag for this purpose.
Delete a database
$ sudo nine-manage-databases database drop nmd_ponyhof
This command drops the database nmd_ponyhof. Before deletion a confirmation will be required. To skip the confirmation skip it with
--force. This will drop the database immediately.
Note that this command will also delete all the users which were created for this database.
$ sudo nine-manage-databases user -d nmd_ponyhof list
This command lists all users of the database nmd_ponyhof.
$ sudo nine-manage-databases user -d nmd_ponyhof create --read-only -p nmd_mcdonald
You can create as many users for a database as you want. The above command creates a user nmd_mcdonald where the password will be asked interactively. This user will have read-only access to the nmd_ponyhof database.
You can omit the password option to create a random one. By default, users have read-write access.
Grant database access to user
2.0.0, it is now possible for a user to access many databases:
$ sudo nine-manage-databases user grant_rights nmd_mcdonald -d nmd_another_db --read-only
nmd_mcdonald user has now read-only access to the database
To revoke that right, just use the
user revoke_rights command:
$ sudo nine-manage-databases user revoke_rights nmd_mcdonald -d nmd_another_db
$ sudo nine-manage-databases user -d nmd_ponyhof update -p nmd_farmer
This command updates the password of the user nmd_farmer and asks for the password interactively.
$ sudo nine-manage-databases user -d nmd_ponyhof drop nmd_farmer
This command drops the user nmd_farmer of the database nmd_ponyhof.