This guide outlines how to add service accounts for the 3 most common systems PO Box is connected to: MySQL Databases, PostGREs Databases, and Active Directory. This guide also outlines additional considerations such as ensuring remote access is enabled and firewalls are properly configured.

Prerequisites

Zilla will require a service account to have read access to the database(s) that you want to manage accounts on. See Configuration below for more details.

Configuration

There are 3 query types that PO Box supports out of the box: MySQL, PostGREs, and LDAP queries. This section of the guide outlines how to create service accounts for zilla to use in all 3 query languages.

Note: if there are brackets inside of a code snippet, you will need to replace it with a value. For instance, If there is code that says to enter CREATE USER {username}, you will need to replace {username} with the proper value.

Creating a Service Account for a MySQL-Based Application/Database

To create a service account for Zilla, login as the root user, or a user that can create new users and grant privileges to users. The command you can use to perform this is:

mysql -u root -p

Once you enter this, you’ll be prompted to enter your admin password, and will be logged in as an admin.

Once logged in as an admin, run the following query to create a user. The zilla-service account name can be changed if you would like to have a different naming convention. Additionally, you will need to add the IP of the serou will need Just ensure that you change the query to grant access with the new name:

CREATE USER 'zilla-service'@'%' IDENTIFIED WITH mysql_native_password BY '{Designate a Password Here}';

Ensure you store the username and password somewhere. You will need to add this to your secrets manager in the next phase.

Also, ensure that you are associating the account with all the different servers you wish to connect PO-Box to. This is done by modifying everything after the @ sign on the user. The command above will allow for the zilla-service user to connect from any hostname. You can use this documentation as reference to try and limit this scope down further.

Next, Grant read (SELECT) access to the user's table:

GRANT SELECT ON mysql.user TO 'zilla-service'@'%' WITH GRANT OPTION;

Note: If you also want to monitor the access on a specific database, you will need to give the service account read access to all the databases that you will be adding to Zilla. To do this, you can run the command below to grant read access to these databases.

GRANT SELECT ON {Database Name}.* TO 'zilla-service'@'%';

Once you finish granting access to the service account, you will then need to refresh the user’s table with the following command:

FLUSH PRIVILEGES;

After you create a service account, also ensure that mysql is setup to allow for remote access. Namely, you will need to ensure that:

Inbound connections over port 3306 are allowed
you have changed the bind-request configuration to be 0.0.0.0 instead of 127.0.0.1

This document walks through these steps for Ubuntu. Generally, the configuration will be the same for other linux instances as well.

To check that you are able to access the database from your PO Box instance, you can run the command below on the terminal of the machine that you are running PO Box on:

sudo mysql -u 'zilla-service'@'{IP of Database}' -p -D {Database Name} -h {IP of Database}

Creating a Service Account for a PostGREs-Based Application/Database

Login as an admin to your PostGREs Database.
Create a new user with the following command:

CREATE USER 'zilla_service' WITH PASSWORD '{Add a Password Here}';

Take note of the username and password. This will be needed when you add the application to Zilla.
Grant read access to the database that you wish to monitor using the following command:

GRANT USAGE ON {The Name of Your Database} TO 'zilla_service';
GRANT SELECT ON ALL TABLES ON {schema name} TO 'zilla_service';

Note that if you have multiple schemas on your database, you will need to grant SELECT access for the zilla-service account on all those schemas

type exit to leave the PostGREs console
We will now want to ensure remote access is enabled for that user on postgres. This guide outlines this process in detail, but the steps are also outlined below
Open the following file with Nano:

sudo nano /etc/postgresql/*/main/postgresql.conf

ensure you have changed the config #listen_addresses to be:

listen_addresses = '*'

Save the file and exit
Open the following file with nano:

sudo nano /etc/postgresql/*/main/pg_hba.conf

Add the following to the bottom of the file:

host    all     zilla_service     {IP of PO Box Server}/32     md5

Ensure that inbound connections over port 5432 are allowed. This can be checked by running:

sudo ufw status

If port connections inbound to 5432 isn’t allowed, then run this command to allow this:

sudo ufw allow 5432/tcp

Restart the server:

sudo service postgresql restart

Once this is done, try logging in remotely from your PO Box instance to ensure that you can make calls from that machine. You can do this by installing psql onto the machine running PO Box

sudo apt install postgresql-client

Then run this command:

sudo psql -U zilla-service {database name} -p 5432 -h {IP or hostname of PSQL Server}

enter the password. If you aren’t able to connect, double check you have followed steps 6-13.

Creating a Service Account for Active Directory

In Active Directory, you will need to create a domain user service account. This video outlines how to do this:

https://www.youtube.com/watch?v=HRB8quKWTv0

Once done, save the username and password. This will be needed when adding information into Zilla. Please see this guide for more details to do this.

You will also need to ensure that inbound connections over port 389 is open on your Windows Server Firewall.

Next Steps

Once you have your service accounts, you will need to add config information either to Zilla, to AWS secrets manager, or to the zinc-config file that is attached to the container. Please see this guide for details.

Creating Service Accounts For PO Box