https with node.js local environment

Do it step-by-step:
https://www.freecodecamp.org/news/how-to-get-https-working-on-your-local-development-environment-in-5-minutes-7af615770eec/

Or just use someone else's scripts (made by same author above (Daksh Shah)):
https://github.com/dakshshah96/local-cert-generator

There's a caveat by the author that his steps should not be used for production (I've yet to know why):

"In an Express app written in Node.js, here’s how you would do it. Make sure you do this only for your local environment. Do not use this in production."

The following steps are basically a copy of the Daksh's steps for creating SSL certificate. Just making sure in any case that those links become unavailable, I will still be able to generate SSL certificate for my local environment.


Let's follow and see what happens on each step.


This generates rootCA.key file:

Developers-iMac:experiment-nodejs-https dev$ openssl genrsa -des3 -out rootCA.key 2048
Generating RSA private key, 2048 bit long modulus
............................................................................................................+++
...................................+++
e is 65537 (0x10001)
Enter pass phrase for rootCA.key:
Verifying - Enter pass phrase for rootCA.key:
Developers-iMac:experiment-nodejs-https dev$ ls -la
total 768
drwxrwxrwx  1 dev  staff  131072 Jul  6 18:25 .
drwxrwxrwx  1 dev  staff  131072 May 30 08:47 ..
-rwxrwxrwx  1 dev  staff    1751 Jul  6 18:30 rootCA.key

This generates rootCA.pem file:

Developers-iMac:experiment-nodejs-https dev$ openssl req -x509 -new -nodes -key rootCA.key -sha256 -days 1024 -out rootCA.pem
Enter pass phrase for rootCA.key:
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) []:PH
State or Province Name (full name) []:
Locality Name (eg, city) []:
Organization Name (eg, company) []:
Organizational Unit Name (eg, section) []:
Common Name (eg, fully qualified host name) []:
Email Address []:
Developers-iMac:experiment-nodejs-https dev$ ls -la
total 1024
drwxrwxrwx  1 dev  staff  131072 Jul  6 18:25 .
drwxrwxrwx  1 dev  staff  131072 May 30 08:47 ..
-rwxrwxrwx  1 dev  staff    1751 Jul  6 18:30 rootCA.key
-rwxrwxrwx  1 dev  staff     956 Jul  6 18:31 rootCA.pem

Create server.csr.cnf file with this content:

[req]
default_bits = 2048
prompt = no
default_md = sha256
distinguished_name = dn

[dn]
C=PH
ST=RandomState
L=RandomCity
O=RandomOrganization
OU=RandomOrganizationUnit
emailAddress=hello@example.com
CN = localhost

Create v3.ext file with this content:

authorityKeyIdentifier=keyid,issuer
basicConstraints=CA:FALSE
keyUsage = digitalSignature, nonRepudiation, keyEncipherment, dataEncipherment
subjectAltName = @alt_names

[alt_names]
DNS.1 = localhost

Files:

Developers-iMac:experiment-nodejs-https dev$ ls -la
total 1536
drwxrwxrwx  1 dev  staff  131072 Jul  6 18:25 .
drwxrwxrwx  1 dev  staff  131072 May 30 08:47 ..
-rwxrwxrwx  1 dev  staff    1751 Jul  6 18:30 rootCA.key
-rwxrwxrwx  1 dev  staff     956 Jul  6 18:31 rootCA.pem
-rwxrwxrwx  1 dev  staff     214 Jul  6 18:33 server.csr.cnf
-rwxrwxrwx  1 dev  staff     200 Jul  6 18:34 v3.ext

This generates server.csr and server.key files:

Developers-iMac:experiment-nodejs-https dev$ openssl req -new -sha256 -nodes -out server.csr -newkey rsa:2048 -keyout server.key -config server.csr.cnf
Generating a 2048 bit RSA private key
.........................................+++
...............+++
writing new private key to 'server.key'
-----
Developers-iMac:experiment-nodejs-https dev$ ls -la
total 2048
drwxrwxrwx  1 dev  staff  131072 Jul  6 18:25 .
drwxrwxrwx  1 dev  staff  131072 May 30 08:47 ..
-rwxrwxrwx  1 dev  staff    1751 Jul  6 18:30 rootCA.key
-rwxrwxrwx  1 dev  staff     956 Jul  6 18:31 rootCA.pem
-rwxrwxrwx  1 dev  staff    1098 Jul  6 18:35 server.csr
-rwxrwxrwx  1 dev  staff     214 Jul  6 18:33 server.csr.cnf
-rwxrwxrwx  1 dev  staff    1704 Jul  6 18:35 server.key
-rwxrwxrwx  1 dev  staff     200 Jul  6 18:34 v3.ext

This generates rootCA.srl and server.crt files:

Developers-iMac:experiment-nodejs-https dev$ openssl x509 -req -in server.csr -CA rootCA.pem -CAkey rootCA.key -CAcreateserial -out server.crt -days 500 -sha256 -extfile v3.ext
Signature ok
subject=/C=PH/ST=RandomState/L=RandomCity/O=RandomOrganization/OU=RandomOrganizationUnit/emailAddress=hello@example.com/CN=localhost
Getting CA Private Key
Enter pass phrase for rootCA.key:
Developers-iMac:experiment-nodejs-https dev$ ls -la
total 2560
drwxrwxrwx  1 dev  staff  131072 Jul  6 18:25 .
drwxrwxrwx  1 dev  staff  131072 May 30 08:47 ..
-rwxrwxrwx  1 dev  staff    1751 Jul  6 18:30 rootCA.key
-rwxrwxrwx  1 dev  staff     956 Jul  6 18:31 rootCA.pem
-rwxrwxrwx  1 dev  staff      17 Jul  6 18:36 rootCA.srl
-rwxrwxrwx  1 dev  staff    1306 Jul  6 18:36 server.crt
-rwxrwxrwx  1 dev  staff    1098 Jul  6 18:35 server.csr
-rwxrwxrwx  1 dev  staff     214 Jul  6 18:33 server.csr.cnf
-rwxrwxrwx  1 dev  staff    1704 Jul  6 18:35 server.key
-rwxrwxrwx  1 dev  staff     200 Jul  6 18:34 v3.ext

Open Keychain Access:

Then import rootCA.pem file using File > Import Items, the certificate will be added to Keychain Access:

Create app.js:

var fs = require('fs');
var express = require('express');
var https = require('https');

var certOptions = {
  key: fs.readFileSync('server.key'),
  cert: fs.readFileSync('server.crt')
};

var app = express();

app.get('/message', (req, res) => {
    res.json({message: 'Great'});
})

var server = https.createServer(certOptions, app).listen(443);

Run app.js:

Developers-iMac:experiment-nodejs-https dev$ nodemon app.js
[nodemon] 1.19.0
[nodemon] to restart at any time, enter `rs`
[nodemon] watching: *.*
[nodemon] starting `node app.js`

Open https://localhost/message on your browser:

Safari:

To prevent that error from happening, double-click the certificate, then change the Trust settings to Always Trust:

Exit Keychain Access dialog, it will then prompt you with your super user password:

Reload the page:

Safari:

For some reasons, Firefox is uncooperative :)

Session-less roles authorization with Passport using Authorization header + JWT

const express = require('express');

const bodyParser = require('body-parser');
const jwt = require('jsonwebtoken');
const randtoken = require('rand-token');

const passport = require('passport');
const { Strategy: JwtStrategy, ExtractJwt } = require('passport-jwt');

const refreshTokens = {};
const SECRET = "sauce";

const options = {   
    jwtFromRequest: ExtractJwt.fromAuthHeaderAsBearerToken(),
    secretOrKey: SECRET
};

passport.use(new JwtStrategy(options, (jwtPayload, done) => 
{
    const expirationDate = new Date(jwtPayload.exp * 1000);
    if (new Date() >= expirationDate) {
        return done(null, false);
    }

    const user = jwtPayload;
    done(null, user);
}));


const app = express();
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({extended: true}));
app.use(passport.initialize());

app.post('/login', (req, res, next) => 
{
    const { username, password } = req.body;
    
    const accessToken = generateAccessToken(username, getRole(username));    
    const refreshToken = randtoken.uid(42);

    refreshTokens[refreshToken] = username;

    res.json({accessToken, refreshToken});
});

function generateAccessToken(username, role, expiresInSeconds = 60)
{
    const user = {
        username,
        role
    };

    const accessToken = jwt.sign(user, SECRET, { expiresIn: expiresInSeconds });
    
    return accessToken;
}


function getRole(username)
{
    switch (username) {
        case 'linus':
            return 'admin';
        default:
            return 'user';        
    }
}


app.post('/token', (req, res) => 
{
    const { username, refreshToken } = req.body;
    
    if (refreshToken in refreshTokens && refreshTokens[refreshToken] === username) {        
        const accessToken = generateAccessToken(username, getRole(username));
        res.json({accessToken});
    }
    else {
        res.sendStatus(401);
    }
});

app.delete('/token/:refreshToken', (req, res, next) => 
{
    const { refreshToken } = req.params;
    if (refreshToken in refreshTokens) {
        delete refreshTokens[refreshToken];
    }

    res.send(204);
});

app.post('/restaurant-reservation', passport.authenticate('jwt', {session: false}), (req, res) => 
{
    const { user } = req;
    const { guestsCount } = req.body;

    res.json({user, guestsCount});
});

app.get('/user-accessible', authorize(), (req, res) => 
{
    res.json({message: 'for all users', user: req.user});
});

app.get('/admin-accessible', authorize('admin'), (req,res) => 
{
    res.json({message: 'for admins only', user: req.user});
});


function authorize(roles = []) 
{
    if (typeof roles === 'string') {
        roles = [roles];
    }

    return [
        passport.authenticate('jwt', {session: false}),

        (req, res, next) => 
        {
            if (roles.length > 0 && !roles.includes(req.user.role)) {
                return res.status(403).json({message: 'No access'});
            };

            return next();
        }
    ];
}

app.listen(8080);

Test login for non-admin:

$ curl -i -H "Content-Type: application/json" --request POST --data '{"username": "richard", "password": "stallman"}' http://localhost:8080/login

Output:

HTTP/1.1 200 OK
X-Powered-By: Express
Content-Type: application/json; charset=utf-8
Content-Length: 253
ETag: W/"fd-eFe0WyxYJMwm+IYU2RknNmwLN7c"
Date: Thu, 04 Jul 2019 11:18:34 GMT
Connection: keep-alive

{"accessToken":"eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJ1c2VybmFtZSI6InJpY2hhcmQiLCJyb2xlIjoidXNlciIsImlhdCI6MTU2MjIzOTExNCwiZXhwIjoxNTYyMjM5MTc0fQ.eVMIVLoq66wwnvX7R7_YE_Va5uUIupcWqZFJIql2VOo","refreshToken":"ZExtyNqF19XwCF8htNABs9rzwDV5lltlEQxGAGVaeV"}

Test non-admin role against user-accessible resource:

$ curl -i -H "Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJ1c2VybmFtZSI6InJpY2hhcmQiLCJyb2xlIjoidXNlciIsImlhdCI6MTU2MjIzOTExNCwiZXhwIjoxNTYyMjM5MTc0fQ.eVMIVLoq66wwnvX7R7_YE_Va5uUIupcWqZFJIql2VOo" --request GET  http://localhost:8080/user-accessible

Output:

HTTP/1.1 200 OK
X-Powered-By: Express
Content-Type: application/json; charset=utf-8
Content-Length: 103
ETag: W/"67-cGYUdSCA9Hfxbr3kmo6EfcwJGFk"
Date: Thu, 04 Jul 2019 11:19:09 GMT
Connection: keep-alive

{"message":"for all users","user":{"username":"richard","role":"user","iat":1562239114,"exp":1562239174}}

Test non-admin role against admin-accessible resource:

$ curl -i -H "Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJ1c2VybmFtZSI6InJpY2hhcmQiLCJyb2xlIjoidXNlciIsImlhdCI6MTU2MjIzOTExNCwiZXhwIjoxNTYyMjM5MTc0fQ.eVMIVLoq66wwnvX7R7_YE_Va5uUIupcWqZFJIql2VOo" --request GET  http://localhost:8080/admin-accessible

Output:

HTTP/1.1 403 Forbidden
X-Powered-By: Express
Content-Type: application/json; charset=utf-8
Content-Length: 23
ETag: W/"17-wr3eIgD4+9Bp6mVHZCD0DXWfISk"
Date: Thu, 04 Jul 2019 11:19:16 GMT
Connection: keep-alive

{"message":"No access"}

Test login for admin:

$ curl -i -H "Content-Type: application/json" --request POST --data '{"username": "linus", "password": "torvalds"}' http://localhost:8080/login

Output:

HTTP/1.1 200 OK
X-Powered-By: Express
Content-Type: application/json; charset=utf-8
Content-Length: 251
ETag: W/"fb-ffoTYONCm1BVpI+gaFqCXe7AX2g"
Date: Thu, 04 Jul 2019 11:23:05 GMT
Connection: keep-alive

{"accessToken":"eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJ1c2VybmFtZSI6ImxpbnVzIiwicm9sZSI6ImFkbWluIiwiaWF0IjoxNTYyMjM5Mzg1LCJleHAiOjE1NjIyMzk0NDV9.hU09-ESu5VADYgC12R-CBtLga4lmGnpGC1AAwxg7t_Y","refreshToken":"8RItXk4v6kV8W68paZk3av34vj3oV5z1vnQdSLAZG7"}

Test admin role against admin-accessible resource:

$ curl -i -H "Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJ1c2VybmFtZSI6ImxpbnVzIiwicm9sZSI6ImFkbWluIiwiaWF0IjoxNTYyMjM5Mzg1LCJleHAiOjE1NjIyMzk0NDV9.hU09-ESu5VADYgC12R-CBtLga4lmGnpGC1AAwxg7t_Y" --request GET  http://localhost:8080/admin-accessible

Output:

HTTP/1.1 200 OK
X-Powered-By: Express
Content-Type: application/json; charset=utf-8
Content-Length: 104
ETag: W/"68-MXNiAugqialVCopW9uv3AMKWHjU"
Date: Thu, 04 Jul 2019 11:23:42 GMT
Connection: keep-alive

{"message":"for admins only","user":{"username":"linus","role":"admin","iat":1562239385,"exp":1562239445}}

And of course user-accessible resource is available to admin role too:

$ curl -i -H "Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJ1c2VybmFtZSI6ImxpbnVzIiwicm9sZSI6ImFkbWluIiwiaWF0IjoxNTYyMjM5Mzg1LCJleHAiOjE1NjIyMzk0NDV9.hU09-ESu5VADYgC12R-CBtLga4lmGnpGC1AAwxg7t_Y" --request GET  http://localhost:8080/user-accessible

Output:

HTTP/1.1 200 OK
X-Powered-By: Express
Content-Type: application/json; charset=utf-8
Content-Length: 102
ETag: W/"66-fLBX3f6AfyVZNVcfBNUakwJko5w"
Date: Thu, 04 Jul 2019 11:23:51 GMT
Connection: keep-alive

{"message":"for all users","user":{"username":"linus","role":"admin","iat":1562239385,"exp":1562239445}}

Session-less authentication with Passport using Authorization header + JWT

const express = require('express');

const bodyParser = require('body-parser');
const jwt = require('jsonwebtoken');
const randtoken = require('rand-token');

const passport = require('passport');
const { Strategy: JwtStrategy, ExtractJwt } = require('passport-jwt');

const refreshTokens = {};
const SECRET = "sauce";

const options = {   
    jwtFromRequest: ExtractJwt.fromAuthHeaderAsBearerToken(),
    secretOrKey: SECRET
};

passport.use(new JwtStrategy(options, (jwtPayload, done) => 
{
    const expirationDate = new Date(jwtPayload.exp * 1000);
    if (new Date() >= expirationDate) {
        return done(null, false);
    }

    const user = jwtPayload;
    done(null, user);
}));


const app = express();
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({extended: true}));
app.use(passport.initialize());

app.post('/login', (req, res, next) => 
{
    const { username, password } = req.body;
    
    const accessToken = generateAccessToken(username, getRole(username));    
    const refreshToken = randtoken.uid(42);

    refreshTokens[refreshToken] = username;

    res.json({accessToken, refreshToken});
});

function generateAccessToken(username, role, expiresInSeconds = 60)
{
    const user = {
        username,
        role
    };

    const accessToken = jwt.sign(user, SECRET, { expiresIn: expiresInSeconds });
    
    return accessToken;
}


function getRole(username)
{
    switch (username) {
        case 'linus':
            return 'admin';
        default:
            return 'user';        
    }
}


app.post('/token', (req, res) => 
{
    const { username, refreshToken } = req.body;
    
    if (refreshToken in refreshTokens && refreshTokens[refreshToken] === username) {        
        const accessToken = generateAccessToken(username, getRole(username));
        res.json({accessToken});
    }
    else {
        res.sendStatus(401);
    }
});

app.delete('/token/:refreshToken', (req, res, next) => 
{
    const { refreshToken } = req.params;
    if (refreshToken in refreshTokens) {
        delete refreshTokens[refreshToken];
    }

    res.send(204);
});

app.post('/restaurant-reservation', passport.authenticate('jwt', {session: false}), (req, res) => 
{
    const { user } = req;
    const { guestsCount } = req.body;

    res.json({user, guestsCount});
});


app.listen(8080);

Do login:

$ curl -i -H "Content-Type: application/json" --request POST --data '{"username": "linus", "password": "torvalds"}' http://localhost:8080/login

Output:

HTTP/1.1 200 OK
X-Powered-By: Express
Content-Type: application/json; charset=utf-8
Content-Length: 465
ETag: W/"1d1-fL53kZkGhDz1fE7e8SWj1fsPmqk"
Date: Thu, 04 Jul 2019 05:34:50 GMT
Connection: keep-alive

{"accessToken":"eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJ1c2VybmFtZSI6ImxpbnVzIiwicm9sZSI6ImFkbWluIiwiaWF0IjoxNTYyMjE4NDkwLCJleHAiOjE1NjIyMTg1NTB9.BJzSGzbVgy_WZJAABoQ_xCPgf6OZgiezn7KxAiVrkm4","refreshToken":"KPImAwtuR4KMlU6RA7cdoouBJ3JY2XxiRzd4hTu3gU"}

Test authentication middleware:

$ curl -i -H "Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJ1c2VybmFtZSI6ImxpbnVzIiwicm9sZSI6ImFkbWluIiwiaWF0IjoxNTYyMjE4NDkwLCJleHAiOjE1NjIyMTg1NTB9.BJzSGzbVgy_WZJAABoQ_xCPgf6OZgiezn7KxAiVrkm4" \
-H "Content-Type: application/json" \
--request POST --data '{"guestsCount": 7}' \
http://localhost:8080/restaurant-reservation

Output:

HTTP/1.1 200 OK
X-Powered-By: Express
Content-Type: application/json; charset=utf-8
Content-Length: 94
ETag: W/"5e-IgmolWlx16EymTgKApJWpv27g74"
Date: Thu, 04 Jul 2019 05:35:38 GMT
Connection: keep-alive

{"user":{"username":"linus","role":"admin","iat":1562218490,"exp":1562218550},"guestsCount":7}

Do again the command above after 60 seconds. Output:

HTTP/1.1 401 Unauthorized
X-Powered-By: Express
Date: Thu, 04 Jul 2019 05:36:24 GMT
Connection: keep-alive
Content-Length: 12

Unauthorized

As the existing access token expired, we need to get new access token using the refresh token:

$ curl -i -H "Content-Type: application/json" \
--request POST \
--data '{"username": "linus", "refreshToken": "KPImAwtuR4KMlU6RA7cdoouBJ3JY2XxiRzd4hTu3gU"}' \
http://localhost:8080/token

Output:

HTTP/1.1 200 OK
X-Powered-By: Express
Content-Type: application/json; charset=utf-8
Content-Length: 191
ETag: W/"bf-is3S3uVXUUs7vW0DjF+cQ+bzj70"
Date: Thu, 04 Jul 2019 05:37:56 GMT
Connection: keep-alive

{"accessToken":"eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJ1c2VybmFtZSI6ImxpbnVzIiwicm9sZSI6ImFkbWluIiwiaWF0IjoxNTYyMjE4Njc2LCJleHAiOjE1NjIyMTg3MzZ9.bsy2oAm8qU8R6xM5b4SpxqJm8l8Ca4ssRu6VMMtZZq4"}

Test authentication middleware using the new access token:

$ curl -i -H "Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJ1c2VybmFtZSI6ImxpbnVzIiwicm9sZSI6ImFkbWluIiwiaWF0IjoxNTYyMjE4NzY0LCJleHAiOjE1NjIyMTg4MjR9.Fw_HFTJmTc-dOjzExoJF_Wk2QGwYINaG_d6cuoc6fjQ" \
-H "Content-Type: application/json" \
--request POST --data '{"guestsCount": 7}' \
http://localhost:8080/restaurant-reservation

Output:

HTTP/1.1 200 OK
X-Powered-By: Express
Content-Type: application/json; charset=utf-8
Content-Length: 94
ETag: W/"5e-E8689sgC1zfAYBFoQnj/jmIPd+4"
Date: Thu, 04 Jul 2019 05:39:54 GMT
Connection: keep-alive

{"user":{"username":"linus","role":"admin","iat":1562218764,"exp":1562218824},"guestsCount":7}

If we need the user to re-login, just revoke (delete) the refresh token:

$ curl -i -X DELETE http://localhost:8080/token/KPImAwtuR4KMlU6RA7cdoouBJ3JY2XxiRzd4hTu3gU

Output:

HTTP/1.1 204 No Content
X-Powered-By: Express
ETag: W/"a-bAsFyilMr4Ra1hIU5PyoyFRunpI"
Date: Thu, 04 Jul 2019 05:43:43 GMT
Connection: keep-alive

Test getting new access token:

$ curl -i -H "Content-Type: application/json" \
--request POST \
--data '{"username": "linus", "refreshToken": "KPImAwtuR4KMlU6RA7cdoouBJ3JY2XxiRzd4hTu3gU"}' \
http://localhost:8080/token

Output:

HTTP/1.1 401 Unauthorized
X-Powered-By: Express
Content-Type: text/plain; charset=utf-8
Content-Length: 12
ETag: W/"c-dAuDFQrdjS3hezqxDTNgW7AOlYk"
Date: Thu, 04 Jul 2019 05:44:24 GMT
Connection: keep-alive

In For The Win!

Someone on stackoverflow observed that IN is faster than EXISTS on MySQL

select * from `replays` 
where id in (
    select replay_id from `players` 
    where `battletag_name` = 'test') 
order by `id` asc 
limit 100;

select * from `replays` 
where exists (
    select * from `players` 
    where `replays`.`id` = `players`.`replay_id` 
      and `battletag_name` = 'test') 
order by `id` asc 
limit 100;

EXISTS took 70 seconds. IN took 0.4 second.


Aside from IN being faster than EXISTS in most cases, readability of IN is a big win. And also with IN, queries can be made modular, e.g.,

with test_players as 
(
    select replay_id 
    from players 
    where battletag_name = 'test'
) 
select * 
from replays 
where id in (select replay_id from test_players)

Cannot do the above when using EXISTS.

Query Error: Error: ER_NOT_SUPPORTED_YET: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Does not work on MySQL, works on Postgres

select
    t.player, 
    sum(case when t.eventid = 1 then t.points end) as event1,
    sum(case when t.eventid = 2 then t.points end) as event2,
    sum(case when t.eventid = 3 then t.points end) as event3,
    sum(case when t.eventid = 4 then t.points end) as event4,
    
    sum(
        case when t.points >= any(
            select best3.points 
            from tbl best3 
            where best3.player = t.player
            order by best3.points desc 
            limit 3
        ) then 
            t.points
        end
    )             
from tbl t
group by t.player

Output on MySQL 8.0:

Query Error: Error: ER_NOT_SUPPORTED_YET: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Output on Postgres:

| player | event1 | event2 | event3 | event4 | sum |
| ------ | ------ | ------ | ------ | ------ | --- |
| 1      | 25     | 15     | 20     | 20     | 65  |
| 2      | 20     | 13     | 12     | 10     | 45  |

You can even made the code neater on Postgres by using FILTER:

Live test: https://www.db-fiddle.com/f/haMmw4S4f7XMqcBD8CDV7H/1

select
    t.player, 
    sum(t.points) filter(where t.eventid = 1) as event1,
    sum(t.points) filter(where t.eventid = 2) as event2,
    sum(t.points) filter(where t.eventid = 3) as event3,
    sum(t.points) filter(where t.eventid = 4) as event4,
    
    sum(t.points) filter(where
        t.points >= any(
            select best3.points 
            from tbl best3 
            where best3.player = t.player
            order by best3.points desc 
            limit 3
        )
    ) as best3          
from tbl t
group by t.player          

Solution on MySQL 8.0, works on Postgres too:

Live test: https://www.db-fiddle.com/f/4ufuFAXKf7mi5yefNQqoXM/2

with ranking as
(
    select 
        player,       
        rank() over(partition by player order by points desc) as xrank,
        points
    from tbl
)
,pick3 as
(
    select 
        player, 
        sum(points) as best3
    from ranking 
    where xrank <= 3
    group by player
)
select
    t.player, 
    sum(if(t.eventid = 1, t.points,0)) as event1,
    sum(if(t.eventid = 2, t.points,0)) as event2,
    sum(if(t.eventid = 3, t.points,0)) as event3,  
    sum(if(t.eventid = 4, t.points,0)) as event4,
    p.best3            
from tbl t
join pick3 p on t.player = p.player
group by t.player

MySQL 5.7 solution:

Live test: https://www.db-fiddle.com/f/4ufuFAXKf7mi5yefNQqoXM/15

select
    t.player, 
    sum(case when t.eventid = 1 then t.points end) as event1,
    sum(case when t.eventid = 2 then t.points end) as event2,
    sum(case when t.eventid = 3 then t.points end) as event3,
    sum(case when t.eventid = 4 then t.points end) as event4,
    
    sum(
        case when t.points >= (
            select best3.points 
            from tbl best3 
            where best3.player = t.player
            order by best3.points desc 
            limit 1 offset 2
        ) then 
            t.points
        end
    ) as best3            
from tbl t
group by t.player;

Output:

| player | event1 | event2 | event3 | event4 | best3 |
| ------ | ------ | ------ | ------ | ------ | ----- |
| 1      | 25     | 15     | 20     | 20     | 65    |
| 2      | 20     | 13     | 12     | 10     | 45    |

salary > avg(salary) conundrum

Beginners, and I think even some non-beginners would want this to work:

select *
from employees 
where salary > AVG(salary)

The problem is that avg is applied to the filtered rows. It will be circular how avg(salary) will get its value if avg is part of where clause. So to prevent that confusion, SQL standard disallows using aggregate functions in where clause.

Humans are lazy. Programmers are humans. Programmers are lazy.

I think that it is a missed opportunity that SQL didn't impose aliases when tables are referenced, otherwise they could introduce functionality that would have no ambiguity:

select e.*
from employees e
where e.salary > AVG(employees.salary)

Or perhaps a little OOP:

select e.*
from employees e
where e.salary > employees.AVG(salary)

That would mean, get all the employee (denoted by e) from employees whose salary is greater than the average salary of all employees.

Before you scoff that it would be super-duper hard for the RDBMS developers to parse things like that. Consider that, that sort of brevity can be achieved in C#'s Linq:

Live test: https://dotnetfiddle.net/uXJEZF

using System;
using System.Linq;

class Employee 
{
    public string Name { get; set; }
    public decimal Salary { get; set; }
}

public class Simple 
{
    public static void Main () 
    {
        var employees = new Employee[]
        {
            new Employee { Name = "John", Salary = 10 },
            new Employee { Name = "Paul", Salary = 9 },
            new Employee { Name = "George", Salary = 2 },
            new Employee { Name = "Ringo", Salary = 1 },
        };

        Console.WriteLine ("Average salary: {0}", employees.Average (x => x.Salary));

        var query = 
            from e in employees
            where e.Salary > employees.Average(x => x.Salary)
            select e;

        foreach (var e in query)
        {
            Console.WriteLine ("{0} {1}", e.Name, e.Salary);
        }
    }
}

Output:

Average salary: 5.5
John 10
Paul 9

If RDBMS have OOP and Linq syntax, it can prevent unusual request:



Live test: https://dotnetfiddle.net/PiANMM

Dynamic unpivoting: SQL Server said, Postgres said

SQL Server version:

declare @tx table(
    id int identity(1, 1) not null,
    data varchar(100),
    column1 int,
    column2 int,
    column3 int
);

insert into
    @tx(data, column1, column2, column3)
values
    ('data1', 1, 2, 3),
    ('data2', 4, 5, 6),
    ('data3', 7, 8, null);

select
    a.id,
    a.data,
    c.item,
    c.value 
from
    @tx a
    cross apply (
        values
            (
                cast(
                    (
                        select
                            a.* for xml raw
                    ) as xml
                )
            )
    ) as b(xmldata)
    cross apply (
        select
            item  = xattr.value('local-name(.)', 'varchar(100)'),
            value = xattr.value('.', 'int')
        from
            b.xmldata.nodes('//@*') as xnode(xattr)
        where
            xnode.xattr.value('local-name(.)', 'varchar(100)') not in 
                ('id', 'data', 'other-columns', 'to-exclude')
    ) c

SQL Server can't include null values though.

Postgres version:

Note: Should run this first before being able to use hstore functionality: create extension hstore

create temporary table tx(
    id int generated by default as identity primary key,
    data text,
    column1 int,
    column2 int,
    column3 int
) on commit drop;

insert into
    tx(data, column1, column2, column3)
values
    ('data1', 1, 2, 3),
    ('data2', 4, 5, 6),
    ('data3', 7, 8, null);
    
with a as (
    select
        id,
        data,
        each(hstore(tx.*) - 'id'::text - 'data'::text) as h
    from
        tx
)
select
    id,
    data,
    (h).key as item,
    (h).value::int as value
from
    a 
-- this would work too:
-- where (h).key not in ('id', 'data', 'other-columns', 'to-exclude')

No problem with Postgres, it include nulls


If the number of columns to unpivot is not so dynamic, can do this in Postgres:

select tx.id, tx.data, x.*
from tx
join lateral (
 values
     ('column1', column1), 
     ('column2', column2), 
     ('column3', column3)
) as x(item, value) on true

Equivalent to SQL Server:

select tx.id, tx.data, x.*
from @tx tx
cross apply (  
    values
     ('column1', column1), 
     ('column2', column2), 
     ('column3', column3)    
) as x(item, value);

Both Postgres and SQL Server include nulls in result

https://stackoverflow.com/questions/55731155/combined-semi-transpose-of-a-data/55731461