nine-manage-databases
Nine 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.
In recent PostgreSQL versions, the default password encryption method switched from md5 to scram-sha-256. While both methods are still supported, all new passwords use scram-sha-256 encryption. To maintain a high level of security, consider updating passwords whenever possible, such as during a major software deployment.
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 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
⚠️ Note that the user grant_rights and user revoke_rights commands are only available for DBMS type 'mysql'.
To manage database access for users on DBMS type 'postgresql', please contact to obtain access to an admin user for your specific purpose.
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.