Store OAuth tokens in the database instead of asking for them for every single sync.

diff --git a/bin/sync.pl b/bin/sync.pl
index 649cfec71ea07078b5910ecef8a6cefaf32ae706..4d49816163617176807bab17c7a594d639b6b096 100644 (file)
--- a/bin/sync.pl
+++ b/bin/sync.pl
@@ -45,8 +45,15 @@ sub log_timing {
 }
 
 sub get_oauth_bearer_token {
-       my $ua = shift;
+       my ($dbh, $ua) = @_;
        my $now = time();
+
+       # See if the database already has a token we could use, that doesn't expire in a while.
+       my $ref = $dbh->selectrow_hashref('SELECT token FROM oauth_tokens WHERE expiry - (TIMESTAMPTZ \'1970-01-01\' + ? * INTERVAL \'1 second\') > INTERVAL \'1 minute\' ORDER BY expiry DESC LIMIT 1', undef, $now);
+       if (defined($ref->{'token'})) {
+               return $ref->{'token'};
+       }
+
        my $jwt = JSON::XS::encode_json({
                "iss" => $config::jwt_key->{'client_email'},
                "scope" => "https://www.googleapis.com/auth/spreadsheets",
@@ -60,7 +67,10 @@ sub get_oauth_bearer_token {
                'grant_type' => 'urn:ietf:params:oauth:grant-type:jwt-bearer',
                'assertion' => $jws_token ]);
        log_timing($start, '/oauth2/v4/token');
-       return JSON::XS::decode_json($response->decoded_content)->{'access_token'};
+       my $token = JSON::XS::decode_json($response->decoded_content)->{'access_token'};
+       $dbh->do('INSERT INTO oauth_tokens (token, acquired, expiry) VALUES (?, TIMESTAMPTZ \'1970-01-01\' + ? * INTERVAL \'1 second\', TIMESTAMPTZ \'1970-01-01\' + ? * INTERVAL \'1 second\')',
+               undef, $token, $now, $now + 1800);
+       return $token;
 }
 
 sub get_slack_name {
@@ -447,7 +457,7 @@ sub run {
        $dbh = DBI->connect("dbi:Pg:dbname=$config::dbname;host=127.0.0.1", $config::dbuser, $config::dbpass, {RaiseError => 1})
                or die "Could not connect to Postgres: " . DBI->errstr;
        my $ua = LWP::UserAgent->new('SKVidarLang/1.0');
-       my $token = get_oauth_bearer_token($ua);
+       my $token = get_oauth_bearer_token($dbh, $ua);
 
        # Find the newest message, what it is linked to, and what was the one before it (for group diffing).
        # TODO: Support more than one, and test better for errors here.

diff --git a/skvidarlang.sql b/skvidarlang.sql
index e317b3e7f23bb316f9e7ae7591d2519fc0d31b90..e1249c13a978f4bda311d9189e3f5d25984a976f 100644 (file)
--- a/skvidarlang.sql
+++ b/skvidarlang.sql
@@ -39,6 +39,13 @@ CREATE TABLE group_membership_history (
 );
 CREATE INDEX gmh_lookup_idx ON group_membership_history (channel, ts, name, change_seen DESC);
 
+CREATE TABLE oauth_tokens (
+    token VARCHAR NOT NULL,
+    acquired TIMESTAMP WITH TIME ZONE NOT NULL,
+    expiry TIMESTAMP WITH TIME ZONE NOT NULL
+);
+CREATE INDEX oauth_tokens_exp_idx ON oauth_tokens ( expiry );
+
 CREATE VIEW current_reactions AS
   SELECT userid, channel, ts, reaction
     FROM ( SELECT DISTINCT ON (channel, ts, userid, reaction)
@@ -59,3 +66,4 @@ GRANT SELECT,UPDATE,INSERT ON TABLE message_sheet_link TO skvidarlang;
 GRANT SELECT,INSERT ON TABLE users_nagged TO skvidarlang;
 GRANT SELECT,INSERT ON TABLE group_membership_history TO skvidarlang;
 GRANT SELECT ON TABLE current_group_membership_history TO skvidarlang;
+GRANT SELECT,INSERT ON TABLE oauth_tokens TO skvidarlang;