nine.ch provides a command line tool for managing databases and database users. nine-manage-databases can be used via SSH. Currently, MySQL / MariaDB and PostgreSQL are supported.

Help

The following command lists all available options:

$ sudo nine-manage-databases -h

Database Management

nine-manage-databases is capable of listing, creating, and removing databases.

Choose the Database Type (DBMS)

If multiple database types are installed, you need to provide the DBMS with the option --database-type (or -t).

Available DBMS types:

• mysql (MySQL / MariaDB)
• postgresql (PostgreSQL)

$ sudo nine-manage-databases --database-type=postgresql database list

This command lists all the PostgreSQL databases created by nine-manage-databases.

Usually, there is only one DBMS installed, which makes the usage of this option obsolete.

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_user1 nmd_database1

This command creates a database named nmd_database1. 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_user1 which will automatically be granted 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 -p option.

If you want to create an additional database for an existing user, you can use the --database-only option for this purpose.

Delete a Database

$ sudo nine-manage-databases database drop nmd_database1

This command drops the database nmd_database1. Before deletion, a confirmation is required. To skip the confirmation, use the option --force. This will drop the database immediately.

Note that this command will also delete all the users which were created for this database.

User Management

List Users

$ sudo nine-manage-databases user list --database=nmd_database1

This command lists all users of the database nmd_database1. Without the --database option, all users are listed.

Create a User

$ sudo nine-manage-databases user create --database=nmd_database1 --read-only -p nmd_user2

You can create as many users for a database as you want. You can also omit the database (--database option), because the permissions can be granted separetely.

The above command creates the user nmd_user2, the password will be asked for interactively (option -p. You can omit the password option -p to create a random one.

This user will have read-only access to the nmd_database1 database (option --read-only). By default, users have read-write access.

Grant Database Access to User

One user can be granted access to multiple databases:

$ sudo nine-manage-databases user grant_rights nmd_user2 --database=nmd_database2 --read-only

nmd_user2 user has now read-only access to the database nmd_database2.

To revoke this permission, use the user revoke_rights command:

$ sudo nine-manage-databases user revoke_rights nmd_user2 --database=nmd_database2

Change a Database Users Password

$ sudo nine-manage-databases user update nmd_user1 update -p --database=nmd_database1

This command updates the password of the user nmd_user1 and asks for a new password interactively (option -p).

Delete a User

$ sudo nine-manage-databases user drop nmd_user1

This command drops the user nmd_user1.