utility script for use in periodic account audits

[ahelsing]

To run periodic audits of shib-idp accounts, we need a script that:

• takes a date as an argument
• returns a list containing

username: Firstname Lastname <[email protected]>

with one line for each user whose account is active right now, and was created before the date given by the argument.

Imported from trac ticket #52, created by chaos on 10-03-2013 at 17:19, last modified: 10-04-2013 at 11:26

[tcmitchell]

This needs work, but here's a query that comes pretty close:

select username_requested || ': ' || first_name || ' ' || last_name || ' <' || email || '>'
  from idp_account_request
  where request_state = 'APPROVED'
    and created_ts < '2013-09-15';

|| is the string concatenation operator. If we wrap this in a short shell script and eliminate the column header, this might do the job.

Trac comment by tmitchel (github user: tcmitchell) on 10-03-2013 at 17:51

[tcmitchell]

Tidying up a bit for executing as a shell command, with output to stdout (apologies for the long line here):

psql -U accreq -h localhost -t \
  -c "select username_requested || ': ' || first_name || ' ' || last_name || ' <' || email || '>' from idp_account_request where request_state = 'APPROVED' and created_ts < '2013-09-15'"

The -t flag disables column header printing. The -c flag takes a single query to execute.

Trac comment by tmitchel (github user: tcmitchell) on 10-03-2013 at 17:56

[ahelsing]

Hmm... that should work, but all the historical entries have created_ts null. Is that expected?

Trac comment by chaos on 10-03-2013 at 17:59

[ahelsing]

Perhaps the timestamps got loaded into request_ts by the conversion script? Here's a partial select from aslund (staging) with the passwords blobbed out:

accreq=> select * from idp_account_request;
 id | first_name |  last_name  |         email          | username_requested |    phone     |             password_hash              |   organization   |      title       | url |                        reason                         |         request_ts         | username_assigned |         created_ts         | request_state 
----+------------+-------------+------------------------+--------------------+--------------+----------------------------------------+------------------+------------------+-----+-------------------------------------------------------+----------------------------+-------------------+----------------------------+---------------
  2 | Tom        | Mitchell    | [email protected]       | tmitchel           | 617-873-3905 | {SSHA}................................ | GPO Lab          | HISTORIC         |     | HISTORIC                                              | 2013-10-01 17:49:49.788806 |                   |                            | APPROVED
  3 | Niky       | Riga        | [email protected]          | inki               | 617-873-3160 | {SSHA}................................ | GPO Lab          | HISTORIC         |     | HISTORIC                                              | 2013-10-01 17:49:49.792791 |                   |                            | APPROVED
  4 | Aaron      | Helsinger   | [email protected]      | ahelsing           | 617-873-4643 | {SSHA}................................ | GPO Lab          | HISTORIC         |     | HISTORIC                                              | 2013-10-01 17:49:49.796594 |                   |                            | APPROVED
...

Hmm, no, i think those timestamps represent when i ran the [https://shib-idp1.gpolab.bbn.com/manage/fix_accounts.html] script, not when those accounts were initially created.

So, i think for the historical entries, the accurate timestamps wound up in idp_account_actions, and perhaps did not wind up in idp_account_request. That's why i had understood that this script would be a little more complicated, because we'd want to grab account status from idp_account_request, and accurate times from idp_account_actions.

Trac comment by chaos on 10-03-2013 at 18:04

[tcmitchell]

Can you attach an export of the database for us to work with? We don't have a rich enough dataset to properly create the script.

Trac comment by tmitchel (github user: tcmitchell) on 10-04-2013 at 09:40

[ahelsing]

I put a copy of the shib-idp1 database dump in my home directory on macomb:

/home/chaos/aslund.20131004-01.sql

By the way, i had been thinking of a solution for this ticket more like the python module that backs geni-ops-report in the proto-ch world, because i expect that (as i've done more than once in the proto-ch world), once we have that module, we'll be able to use it to easily solve other problems we have, e.g. nightly status/audit reports we may find we need as we go forward. There's only two tables, so i would think that would be pretty simple, and would make it easy to solve more problems in the future. What do you think?

Trac comment by chaos on 10-04-2013 at 11:26