emqx4.3/apps/emqx_auth_postgresql/test/emqx_authn_postgresql_SUITE.erl

%%--------------------------------------------------------------------
%% Copyright (c) 2020-2024 EMQ Technologies Co., Ltd. All Rights Reserved.
%%
%% Licensed under the Apache License, Version 2.0 (the "License");
%% you may not use this file except in compliance with the License.
%% You may obtain a copy of the License at
%%
%%     http://www.apache.org/licenses/LICENSE-2.0
%%
%% Unless required by applicable law or agreed to in writing, software
%% distributed under the License is distributed on an "AS IS" BASIS,
%% WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
%% See the License for the specific language governing permissions and
%% limitations under the License.
%%--------------------------------------------------------------------

-module(emqx_authn_postgresql_SUITE).

-compile(nowarn_export_all).
-compile(export_all).

-include_lib("../../emqx_postgresql/include/emqx_postgresql.hrl").
-include_lib("emqx_auth/include/emqx_authn.hrl").
-include_lib("eunit/include/eunit.hrl").
-include_lib("common_test/include/ct.hrl").

-define(PGSQL_HOST, "pgsql").
-define(PGSQL_RESOURCE, <<"emqx_authn_postgresql_SUITE">>).
-define(ResourceID, <<"password_based:postgresql">>).

-define(PATH, [authentication]).

-import(emqx_common_test_helpers, [on_exit/1]).

all() ->
    AllTCs = emqx_common_test_helpers:all(?MODULE),
    TCs = AllTCs -- require_seeds_tests(),
    [
        {group, require_seeds}
        | TCs
    ].

groups() ->
    [{require_seeds, [], require_seeds_tests()}].

require_seeds_tests() ->
    [
        t_create,
        t_authenticate,
        t_authenticate_disabled_prepared_statements,
        t_update,
        t_destroy,
        t_is_superuser
    ].

init_per_testcase(_, Config) ->
    emqx_authn_test_lib:delete_authenticators(
        [authentication],
        ?GLOBAL
    ),
    Config.

end_per_testcase(_TestCase, _Config) ->
    emqx_common_test_helpers:call_janitor(),
    ok.

init_per_group(require_seeds, Config) ->
    ok = init_seeds(),
    Config.

end_per_group(require_seeds, Config) ->
    ok = drop_seeds(),
    Config.

init_per_suite(Config) ->
    case emqx_common_test_helpers:is_tcp_server_available(?PGSQL_HOST, ?PGSQL_DEFAULT_PORT) of
        true ->
            Apps = emqx_cth_suite:start([emqx, emqx_conf, emqx_auth, emqx_auth_postgresql], #{
                work_dir => ?config(priv_dir, Config)
            }),
            {ok, _} = emqx_resource:create_local(
                postgresql,
                ?PGSQL_RESOURCE,
                ?AUTHN_RESOURCE_GROUP,
                emqx_postgresql,
                pgsql_config(),
                #{}
            ),
            [{apps, Apps} | Config];
        false ->
            case os:getenv("IS_CI") of
                "yes" ->
                    throw(no_postgres);
                _ ->
                    {skip, no_postgres}
            end
    end.

end_per_suite(Config) ->
    emqx_authn_test_lib:delete_authenticators(
        [authentication],
        ?GLOBAL
    ),
    ok = emqx_resource:remove_local(?PGSQL_RESOURCE),
    ok = emqx_cth_suite:stop(?config(apps, Config)),
    ok.

%%------------------------------------------------------------------------------
%% Tests
%%------------------------------------------------------------------------------

t_create(_Config) ->
    AuthConfig = raw_pgsql_auth_config(),

    {ok, _} = emqx:update_config(
        ?PATH,
        {create_authenticator, ?GLOBAL, AuthConfig}
    ),

    {ok, [#{provider := emqx_authn_postgresql}]} = emqx_authn_chains:list_authenticators(?GLOBAL),
    emqx_authn_test_lib:delete_config(?ResourceID).

%% invalid config which does not pass the schema check should result in an error
t_update_with_invalid_config(_Config) ->
    AuthConfig = raw_pgsql_auth_config(),
    BadConfig = maps:without([<<"server">>], AuthConfig),
    ?assertMatch(
        {error, #{
            kind := validation_error,
            matched_type := "authn:postgresql",
            path := "authentication.1.server",
            reason := required_field
        }},
        emqx:update_config(
            ?PATH,
            {create_authenticator, ?GLOBAL, BadConfig}
        )
    ),
    ok.

%% bad config values may cause connection failure, but should still be able to update
t_update_with_bad_config_value(_Config) ->
    AuthConfig = raw_pgsql_auth_config(),

    InvalidConfigs =
        [
            AuthConfig#{<<"server">> => <<"unknownhost:3333">>},
            AuthConfig#{<<"password">> => <<"wrongpass">>},
            AuthConfig#{<<"database">> => <<"wrongdatabase">>}
        ],

    lists:foreach(
        fun(Config) ->
            {ok, _} = emqx:update_config(
                ?PATH,
                {create_authenticator, ?GLOBAL, Config}
            ),
            emqx_authn_test_lib:delete_config(?ResourceID),
            ?assertEqual(
                {error, {not_found, {chain, ?GLOBAL}}},
                emqx_authn_chains:list_authenticators(?GLOBAL)
            )
        end,
        InvalidConfigs
    ).

t_authenticate(_Config) ->
    ok = lists:foreach(
        fun(Sample) ->
            ct:pal("test_user_auth sample: ~p", [Sample]),
            test_user_auth(Sample)
        end,
        user_seeds()
    ).

test_user_auth(#{
    credentials := Credentials0,
    config_params := SpecificConfigParams,
    result := Result
}) ->
    AuthConfig = maps:merge(raw_pgsql_auth_config(), SpecificConfigParams),

    {ok, _} = emqx:update_config(
        ?PATH,
        {create_authenticator, ?GLOBAL, AuthConfig}
    ),

    Credentials = Credentials0#{
        listener => 'tcp:default',
        protocol => mqtt
    },

    ?assertEqual(Result, emqx_access_control:authenticate(Credentials)),

    emqx_authn_test_lib:delete_authenticators(
        [authentication],
        ?GLOBAL
    ).

t_authenticate_disabled_prepared_statements(_Config) ->
    ResConfig = maps:merge(pgsql_config(), #{disable_prepared_statements => true}),
    {ok, _} = emqx_resource:recreate_local(postgresql, ?PGSQL_RESOURCE, emqx_postgresql, ResConfig),
    on_exit(fun() ->
        emqx_resource:recreate_local(postgresql, ?PGSQL_RESOURCE, emqx_postgresql, pgsql_config())
    end),
    ok = lists:foreach(
        fun(Sample0) ->
            Sample = maps:update_with(
                config_params,
                fun(Cfg) -> Cfg#{<<"disable_prepared_statements">> => true} end,
                Sample0
            ),
            ct:pal("test_user_auth sample: ~p", [Sample]),
            test_user_auth(Sample)
        end,
        user_seeds()
    ).

t_destroy(_Config) ->
    AuthConfig = raw_pgsql_auth_config(),

    {ok, _} = emqx:update_config(
        ?PATH,
        {create_authenticator, ?GLOBAL, AuthConfig}
    ),

    {ok, [#{provider := emqx_authn_postgresql, state := State}]} =
        emqx_authn_chains:list_authenticators(?GLOBAL),

    {ok, _} = emqx_authn_postgresql:authenticate(
        #{
            username => <<"plain">>,
            password => <<"plain">>
        },
        State
    ),

    emqx_authn_test_lib:delete_authenticators(
        [authentication],
        ?GLOBAL
    ),

    % Authenticator should not be usable anymore
    ?assertMatch(
        ignore,
        emqx_authn_postgresql:authenticate(
            #{
                username => <<"plain">>,
                password => <<"plain">>
            },
            State
        )
    ).

t_update(_Config) ->
    CorrectConfig = raw_pgsql_auth_config(),
    IncorrectConfig =
        CorrectConfig#{
            <<"query">> =>
                <<
                    "SELECT password_hash, salt, is_superuser_str as is_superuser\n"
                    "                          FROM users where username = ${username} LIMIT 0"
                >>
        },

    {ok, _} = emqx:update_config(
        ?PATH,
        {create_authenticator, ?GLOBAL, IncorrectConfig}
    ),

    {error, not_authorized} = emqx_access_control:authenticate(
        #{
            username => <<"plain">>,
            password => <<"plain">>,
            listener => 'tcp:default',
            protocol => mqtt
        }
    ),

    % We update with config with correct query, provider should update and work properly
    {ok, _} = emqx:update_config(
        ?PATH,
        {update_authenticator, ?GLOBAL, <<"password_based:postgresql">>, CorrectConfig}
    ),

    {ok, _} = emqx_access_control:authenticate(
        #{
            username => <<"plain">>,
            password => <<"plain">>,
            listener => 'tcp:default',
            protocol => mqtt
        }
    ).

t_is_superuser(_Config) ->
    Config = raw_pgsql_auth_config(),
    {ok, _} = emqx:update_config(
        ?PATH,
        {create_authenticator, ?GLOBAL, Config}
    ),

    Checks = [
        {is_superuser_str, "0", false},
        {is_superuser_str, "", false},
        {is_superuser_str, null, false},
        {is_superuser_str, "1", true},
        {is_superuser_str, "val", false},

        {is_superuser_int, 0, false},
        {is_superuser_int, null, false},
        {is_superuser_int, 1, true},
        {is_superuser_int, 123, true},

        {is_superuser_bool, false, false},
        {is_superuser_bool, null, false},
        {is_superuser_bool, true, true}
    ],

    lists:foreach(fun test_is_superuser/1, Checks).

test_is_superuser({Field, Value, ExpectedValue}) ->
    {ok, _} = q("DELETE FROM users"),

    UserData = #{
        username => "user",
        password_hash => "plainsalt",
        salt => "salt",
        Field => Value
    },

    ok = create_user(UserData),

    Query =
        "SELECT password_hash, salt, " ++ atom_to_list(Field) ++
            " as is_superuser "
            "FROM users where username = ${username} LIMIT 1",

    Config = maps:put(<<"query">>, Query, raw_pgsql_auth_config()),
    {ok, _} = emqx:update_config(
        ?PATH,
        {update_authenticator, ?GLOBAL, <<"password_based:postgresql">>, Config}
    ),

    Credentials = #{
        listener => 'tcp:default',
        protocol => mqtt,
        username => <<"user">>,
        password => <<"plain">>
    },

    ?assertEqual(
        {ok, #{is_superuser => ExpectedValue}},
        emqx_access_control:authenticate(Credentials)
    ).

%%------------------------------------------------------------------------------
%% Helpers
%%------------------------------------------------------------------------------

raw_pgsql_auth_config() ->
    #{
        <<"mechanism">> => <<"password_based">>,
        <<"password_hash_algorithm">> => #{
            <<"name">> => <<"plain">>,
            <<"salt_position">> => <<"suffix">>
        },
        <<"enable">> => <<"true">>,

        <<"backend">> => <<"postgresql">>,
        <<"database">> => <<"mqtt">>,
        <<"username">> => <<"root">>,
        <<"password">> => <<"public">>,

        <<"query">> =>
            <<
                "SELECT password_hash, salt, is_superuser_str as is_superuser\n"
                "                      FROM users where username = ${username} LIMIT 1"
            >>,
        <<"server">> => pgsql_server()
    }.

user_seeds() ->
    [
        #{
            data => #{
                username => "plain",
                password_hash => "plainsalt",
                salt => "salt",
                is_superuser_str => "1"
            },
            credentials => #{
                username => <<"plain">>,
                password => <<"plain">>
            },
            config_params => #{},
            result => {ok, #{is_superuser => true}}
        },

        #{
            data => #{
                username => "md5",
                password_hash => "9b4d0c43d206d48279e69b9ad7132e22",
                salt => "salt",
                is_superuser_str => "0"
            },
            credentials => #{
                username => <<"md5">>,
                password => <<"md5">>
            },
            config_params => #{
                <<"password_hash_algorithm">> => #{
                    <<"name">> => <<"md5">>,
                    <<"salt_position">> => <<"suffix">>
                }
            },
            result => {ok, #{is_superuser => false}}
        },

        #{
            data => #{
                username => "sha256",
                password_hash => "ac63a624e7074776d677dd61a003b8c803eb11db004d0ec6ae032a5d7c9c5caf",
                salt => "salt",
                is_superuser_int => 1
            },
            credentials => #{
                clientid => <<"sha256">>,
                password => <<"sha256">>
            },
            config_params => #{
                <<"query">> =>
                    <<
                        "SELECT password_hash, salt, is_superuser_int as is_superuser\n"
                        "                            FROM users where username = ${clientid} LIMIT 1"
                    >>,
                <<"password_hash_algorithm">> => #{
                    <<"name">> => <<"sha256">>,
                    <<"salt_position">> => <<"prefix">>
                }
            },
            result => {ok, #{is_superuser => true}}
        },

        %% strip double quote support
        #{
            data => #{
                username => "sha256",
                password_hash => "ac63a624e7074776d677dd61a003b8c803eb11db004d0ec6ae032a5d7c9c5caf",
                salt => "salt",
                is_superuser_int => 1
            },
            credentials => #{
                username => <<"sha256">>,
                password => <<"sha256">>
            },
            config_params => #{
                <<"query">> =>
                    <<
                        "SELECT password_hash, salt, is_superuser_int as is_superuser\n"
                        "                            FROM users where username = \"${username}\" LIMIT 1"
                    >>,
                <<"password_hash_algorithm">> => #{
                    <<"name">> => <<"sha256">>,
                    <<"salt_position">> => <<"prefix">>
                }
            },
            result => {ok, #{is_superuser => true}}
        },

        #{
            data => #{
                username => "sha256",
                password_hash => "ac63a624e7074776d677dd61a003b8c803eb11db004d0ec6ae032a5d7c9c5caf",
                cert_subject => <<"cert_subject_data">>,
                cert_common_name => <<"cert_common_name_data">>,
                salt => "salt",
                is_superuser_int => 1
            },
            credentials => #{
                clientid => <<"sha256">>,
                password => <<"sha256">>,
                cert_subject => <<"cert_subject_data">>,
                cert_common_name => <<"cert_common_name_data">>
            },
            config_params => #{
                <<"query">> =>
                    <<
                        "SELECT password_hash, salt, is_superuser_int as is_superuser\n"
                        "      FROM users where cert_subject = ${cert_subject} AND \n"
                        "                       cert_common_name = ${cert_common_name} LIMIT 1"
                    >>,
                <<"password_hash_algorithm">> => #{
                    <<"name">> => <<"sha256">>,
                    <<"salt_position">> => <<"prefix">>
                }
            },
            result => {ok, #{is_superuser => true}}
        },

        #{
            data => #{
                username => <<"bcrypt">>,
                password_hash => "$2b$12$wtY3h20mUjjmeaClpqZVveDWGlHzCGsvuThMlneGHA7wVeFYyns2u",
                salt => "$2b$12$wtY3h20mUjjmeaClpqZVve",
                is_superuser_int => 0
            },
            credentials => #{
                username => <<"bcrypt">>,
                password => <<"bcrypt">>
            },
            config_params => #{
                <<"query">> =>
                    <<
                        "SELECT password_hash, salt, is_superuser_int as is_superuser\n"
                        "                            FROM users where username = ${username} LIMIT 1"
                    >>,
                <<"password_hash_algorithm">> => #{<<"name">> => <<"bcrypt">>}
            },
            result => {ok, #{is_superuser => false}}
        },

        #{
            data => #{
                username => <<"bcrypt0">>,
                password_hash => "$2b$12$wtY3h20mUjjmeaClpqZVveDWGlHzCGsvuThMlneGHA7wVeFYyns2u",
                salt => "$2b$12$wtY3h20mUjjmeaClpqZVve",
                is_superuser_str => "0"
            },
            credentials => #{
                username => <<"bcrypt0">>,
                password => <<"bcrypt">>
            },
            config_params => #{
                % clientid variable & username credentials
                <<"query">> =>
                    <<
                        "SELECT password_hash, salt, is_superuser_int as is_superuser\n"
                        "                            FROM users where username = ${clientid} LIMIT 1"
                    >>,
                <<"password_hash_algorithm">> => #{<<"name">> => <<"bcrypt">>}
            },
            result => {error, not_authorized}
        },

        #{
            data => #{
                username => <<"bcrypt1">>,
                password_hash => "$2b$12$wtY3h20mUjjmeaClpqZVveDWGlHzCGsvuThMlneGHA7wVeFYyns2u",
                salt => "$2b$12$wtY3h20mUjjmeaClpqZVve",
                is_superuser_str => "0"
            },
            credentials => #{
                username => <<"bcrypt1">>,
                password => <<"bcrypt">>
            },
            config_params => #{
                % Bad keys in query
                <<"query">> =>
                    <<
                        "SELECT 1 AS unknown_field\n"
                        "                            FROM users where username = ${username} LIMIT 1"
                    >>,
                <<"password_hash_algorithm">> => #{<<"name">> => <<"bcrypt">>}
            },
            result => {error, not_authorized}
        },

        #{
            data => #{
                username => <<"bcrypt2">>,
                password_hash => "$2b$12$wtY3h20mUjjmeaClpqZVveDWGlHzCGsvuThMlneGHA7wVeFYyns2u",
                salt => "$2b$12$wtY3h20mUjjmeaClpqZVve",
                is_superuser => "0"
            },
            credentials => #{
                username => <<"bcrypt2">>,
                % Wrong password
                password => <<"wrongpass">>
            },
            config_params => #{
                <<"password_hash_algorithm">> => #{<<"name">> => <<"bcrypt">>}
            },
            result => {error, bad_username_or_password}
        }
    ].

init_seeds() ->
    ok = drop_seeds(),
    {ok, _, _} = q(
        "CREATE TABLE users(\n"
        "                       username varchar(255),\n"
        "                       password_hash varchar(255),\n"
        "                       salt varchar(255),\n"
        "                       cert_subject varchar(255),\n"
        "                       cert_common_name varchar(255),\n"
        "                       is_superuser_str varchar(255),\n"
        "                       is_superuser_int smallint,\n"
        "                       is_superuser_bool boolean)"
    ),

    lists:foreach(
        fun(#{data := Values}) ->
            ok = create_user(Values)
        end,
        user_seeds()
    ).

create_user(Values) ->
    Fields = [
        username,
        password_hash,
        salt,
        cert_subject,
        cert_common_name,
        is_superuser_str,
        is_superuser_int,
        is_superuser_bool
    ],

    InsertQuery =
        "INSERT INTO users(username, password_hash, salt, cert_subject, cert_common_name, "
        "is_superuser_str, is_superuser_int, is_superuser_bool) "
        "VALUES($1, $2, $3, $4, $5, $6, $7, $8)",

    Params = [maps:get(F, Values, null) || F <- Fields],
    {ok, 1} = q(InsertQuery, Params),
    ok.

q(Sql) ->
    emqx_resource:simple_sync_query(
        ?PGSQL_RESOURCE,
        {query, Sql}
    ).

q(Sql, Params) ->
    emqx_resource:simple_sync_query(
        ?PGSQL_RESOURCE,
        {query, Sql, Params}
    ).

drop_seeds() ->
    {ok, _, _} = q("DROP TABLE IF EXISTS users"),
    ok.

pgsql_server() ->
    iolist_to_binary(io_lib:format("~s", [?PGSQL_HOST])).

pgsql_config() ->
    #{
        auto_reconnect => true,
        disable_prepared_statements => false,
        database => <<"mqtt">>,
        username => <<"root">>,
        password => <<"public">>,
        pool_size => 8,
        server => pgsql_server(),
        ssl => #{enable => false}
    }.

start_apps(Apps) ->
    lists:foreach(fun application:ensure_all_started/1, Apps).

stop_apps(Apps) ->
    lists:foreach(fun application:stop/1, Apps).