Hello @jrobell,

As this is a request for a new user to have access, it will require authorization from Lisa. Have you sent the request along yet and gotten approval?

@Dwisehaupt I haven't reached out to Lisa yet, it may be helpful if you could send an email to her and cc me, like you did with Jerrie. Is that OK? Thank you! Erin is eager to get started :)

Access request sent to Lisa. It was missing the subject line as my tea hasn't kicked in yet. :)

Request for yubikey sent to techsupport.

Key is still on the way. Should arrive by COB today.

Key has arrived.

@EYener, I'm still awaiting the access approval, but we can get you started on some of the steps so I can move quickly one the approval comes in.

The two things we need to do at this point are to have you generate an ssh keypair and to get the public side of your yubikey for our system.

Instructions for generating the ssh keypair are here: https://collab.wikimedia.org/wiki/Fundraising_ssh_access

When you have generated the keypair, please post the contents of the public side of the key (ie: fr_id_rsa.pub) in this ticket.

As for the yubikey, there are 2 options for getting us the public side of the key:

1. Visit https://directory.corp.wikimedia.org/yubikey.php logging in with your 'OIT' ldap credentials like you would for email, and clicking on the yubikey in the text box. It will then trim the code for you and provide the public side.
2. In a text editor, just repeatedly press the button on the yubikey. You will notice there are 12 characters at the beginning of the output that don't change. That is the private side of the key that you can then send on to us.

Once we have this information and the approval I can move forward..

Dallas

Hi Dallas,

Thanks for the instructions! I'm excited to get started. Here is my public key:

cccccclihugr

Best,
Erin

Access approval granted.

From: Lisa Gruwell <lgruwell@wikimedia.org>
To: Dallas Wisehaupt <dwisehaupt@wikimedia.org>
Cc: Jessica Robell <jrobell@wikimedia.org>
Subject: Re: Fundraising access request for Erin Yener

Yes, approved. 

@EYener Thanks, I'll get that in for that entered for the Yubikey. Let me know if you have any questions about the ssh key setup portion also.

@Dwisehaupt there are a few things I want to check on for access.

Some people don't automatically get access to temp table creation in frdev1001 @EYener needs that.

I also want to make sure she get a civi cert for UI access as well.

SSL client cert has been created and emailed to @EYener and the password has been sent via SMS.

@Dwisehaupt here is the public SSH key:

ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAACAQDT9UuxZVRsuuyg1qFvPyCUir9LhFYaBdJKo0UtSfglWm7u2bNOzmzMfsEloYD6LULSMV65pB61ZcZ6ZB1L3e4+X3ByySsbBFogYO4Y6L4tjNQoQyQGwmCcCosLRj7+d6Shc1qubvGFFf2MZ4jSY1L5sOiqBlVV4x62O9wf4N1BfFOA9YwbiARNTxCEpTcqAszZncHWjdt6uwcRf+XoxQisON9ZAHE8WjnvUYiORlceQHalRoMfcRePPMAZq1zuoxTjeDfgzJ3au+guCm182nZiEuGgOvbpp/QiG2dtHXK7KNqNiuX/4x/WLViYOKG6j8M5F2ZW1/mIe6wa6oyzvLTBa5Yi4mcIWHxmKIm3by+84086d6FvoEJTN415Vy1zaOCsLtIuzJrNnnGUL67HFEE+jUWD6sluLqouS0Ijd2KfWqpUcu6mhrxEfHBoKqWAnYxdsmqT4UBPn/HoJ/E324UD6PUUAvcJ39CSFe/edJ0j33LVEDxBMslbB068D0FAmIURg3cmIuOWOjoQNLc+6U3SMUDXv0/Gos+tuoNp92EhLbvtVz9jsePSbDQwhx42aQLvQLBQEIIOtRRw/tCEiVLrmfOXRLkrjKcrDu7qexWowEeUBQDQRTSIQfWm50bEyYTHtfyAD7yZnijH/OYF+VwnUhGqk2YLIQCeuJMnn8OjFw== eyener-ctr@wikimedia.org

In T232982#5512009, @DStrine wrote:

@Dwisehaupt there are a few things I want to check on for access.

Some people don't automatically get access to temp table creation in frdev1001 @EYener needs that.

I also want to make sure she get a civi cert for UI access as well.

@DStrine Thanks. I'll note that for her.

User account and mysql accounts set up for eyener. Public ssh and yubikey files in place. Mysql grants created and run on development host. Not all commits tracked in puppet emails but here are the ones that were for those pushes.

[frack::puppet::private] 4135a4c Adding ssh public key for eyener
[frack::puppet] 7d0d86cb Adding user account for eyener

Hello,

What credentials do I use to log into Civi (username/password). My ldap credentials don't seem to be correct. Please let me know who I would ask for this information.

My best,
Erin

Hello team,

To add more context, I tried to request a new password via email address lookup in Civi and received this message:

Best,
Erin

Opened a ticket with OIT (19436) and they should be contacting @EYener to see if they can diagnose why the certificate is not working properly in the MacOS keychain.

Hi @EYener, I just set up an account in Civi for you. You should be able to use the 'request new password' link to get in. Welcome to the team!

Thank you very much @Ejegg - I have logged in now. It looks like I am able to view reports and navigate around the Civi UI. How can I query the Civi database?

@EYener to make DB queries you need to ssh into frdev1001 as eyener, authenticating with your ssh key and the yubikey (first your keychain manager will prompt you to unlock your ssh key, then when you see a password prompt in the terminal use the yubikey) . From there you should be able to use the mysql command-line client to connect to the following databases

• civicrm (a real-time, read-only replica of the main civicrm database)
• drupal (a real-time, read-only replica of the main drupal database)
• fredge (a real-time, read-only replica of the main anti-fraud database)
• dev_ prefixed versions of the above three, which are writable (by the appropriate users) and power the staging copy of civi. The dev_ copies have real donor data, but are out of date, generally by a few months.
• pgehres (a realtime replica of the main db containing banner and landing page views, named after a former analyst)
• silverpop (a database used to stage data exports to our bulk mailing house) Has some potentially interesting totals aggregated by email address, but only for donors who haven't opted out of bulk mailings.
• eyener (your own personal DB, where you can create tables)

Some documentation of the databases is here: https://www.mediawiki.org/wiki/Fundraising_tech/Database_schema
And some potentially interesting example queries are here (you may need to ask for an account on collab-wiki): https://collab.wikimedia.org/wiki/Fundraising/Engineering/Fun_SQL_Queries

Thanks for the instructions - I'm encountering some issues and can't identify which step in the process I'm missing/mis-applying. Would you be able to jump on a screen share at some point tomorrow (Monday) to assist?

After speaking with @jrobell, I will also need access to:
Turnilo
Banner data
Pageview data

Thank you!

Hi @EYener,

If your issues are around connecting to frdev1001, using the proper ssh passphrases/yubikey clicks, or mysql login actions, I can definitely help you.

If the issue is around mysql access or using the db/data, someone else in FR-Tech will need to assist you.

I'm around most of the day for a hangout or screen share, just check my calendar for availability.

Dallas

Hi @Dwisehaupt and thanks for your help today. I can now access the frdev1001 db via ssh shell and wanted to follow up on 2 remaining questions:

1. How to add my password to my keychain
2. Whether it's possible to set up frdev1001 access via MySql Workbench or another SQL gui.

Since I'm now able to access the database, I can certainly open another ticket for gui access over SSH + bastion authentication to get it to the right group.

Hi @EYener, here is some more info

To add your key into the keychain/agent, you would run this command adjusting the key file if the name is different: ssh-add -K ~/.ssh/fr_id_rsa
After that, your ssh process should use the agent to check if it has the key loaded. If it still prompts you for the passphrase, you may need to add the following into your .ssh/config file to explicitly tell it to use the agent. You can add this right at the top of the file:

Host *
  UseKeychain yes

As for access with mysql workbench over the ssh tunnels, we don't anyone with any config like that so I don't have any leads. At this point you will need to use the mysql command line.

@Cstone spent some time this morning with @EYener working with vagrant to get a mysql workbench setup.

Yes! I think we can close this task, as I do have access to the database now. Thank you for your help, @Dwisehaupt and @Cstone! I'll open more specific tickets as questions arise.