Creating database users using Jenkins¶

Prerequisites¶

1. Access to eks-common Jenkins: Jenkins - itops
2. Access to ansible inventories: master (Bitbucket)

Generating hash¶

In order to add user hash for user's password must be generated in advance.

For MySQL users the following script can be used:

$ python -c 'from hashlib import sha1; print("*" + sha1(sha1("VerySecurePassword".encode("utf-8")).digest()).hexdigest().upper())'

Output:

*9F89685D87E35E29D7B5A21DCEE56448997DCDE3

To generate password hash for PostgreSQL users the following algorithm is used:

'str["md5"] + md5[ password + username ]'

For example:

$ echo "md5$(echo -n 'verysecretpasswordJOE' | md5sum | awk '{print $1}')"

Where:

• User ID: JOE

• Password: verysecretpassword

Output:

md5d011966da94d776cf59bf6dbde240e5d

Adding users to MySQL database¶

After generating appropriate hash, a user entry should be added to shared inventories file under appropriate database.

The file is located here: hosts (Bitbucket)

The best way to identify database is look for login_host variable in database entry.

Here is how database entry for statsdb2 MySQL database looks like:

Database address login_host and user list se_users variables marked in red.

So, for example, in order to add a new user we first generate password hash and add appropriate entry to se_users.

Example for the following user:

UID: test

Password: verysecretpassword

1. MySQL hash computation:

$ python -c 'from hashlib import sha1; print("*" + sha1(sha1("verysecretpassword".encode(\'utf-8\')).digest()).hexdigest().upper())'
*2C905879F74F28F8570989947D06A8429FB943E6

1. Adding user entry to inventories:

1. Run Jenkins job to apply changes:

Appropriate Ansible playbook needs to be chosen using dropdown menu.

DRYRUN flag is used to test playbook without actually changing anything.

Adding users to PostgreSQL database¶

After generating appropriate hash, a user entry should be added to shared inventories file under appropriate database.

The file is located here: hosts (Bitbucket)

The best way to identify database is look for login_host variable in database entry.

Here is how database entry for acp_edge_db PostgreSQL database looks like:

Database address login_host and user list users variables marked in red.

So, for example, in order to add a new user we first generate password hash and add appropriate entry to users.

Example for the following user:

UID: test

Password: verysecretpassword

1. PostgreSQL hash computation:

$ echo "md5$(echo -n 'verysecretpasswordtest' | md5sum | awk '{print $1}')"
md599616d7ca51920965b750afe2a6f907c

1. Adding user entry to inventories:

1. Run Jenkins job to apply changes:

Appropriate Ansible playbook needs to be chosen using dropdown menu.

DRYRUN flag is used to test playbook without actually changing anything.

Deleting users¶

In order to delete user one needs to change user's state to absent in ansible inventories for specific database and run appropriate Jenkins job: