PostgreSQL学习之限制用户单次登录时长

最新推荐文章于 2024-07-25 22:24:14 发布

weixin_38700215

最新推荐文章于 2024-07-25 22:24:14 发布

阅读量364

点赞数 10

文章标签：学习 postgresql 数据库密码学安全

本文链接：https://blog.csdn.net/weixin_38700215/article/details/140173968

版权

PostgreSQL14已经实现了空闲会话超时断开功能，如果要控制用户登录时长(即不管会话是否空闲，只要超时就断开)，时长可设置，并且可以精确到用户，测试验证如下：

1、配置hba文件：hba文件增加一列，用于设置指定用户可以登录的时间长度(请忽略其中的TIME-RANGE一列)，如

# TYPE  DATABASE        USER            ADDRESS        SESSION-TIME    TIME-RANGE         METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32  1  "MonTueWedThuFri|08:00:00-20:00:00,SatSun|10:00:00-16:00:00"     trust
# IPv6 local connections:
host    all             all             ::1/128   120    "MonTueWedThuFri|08:00:00-20:00:00,SatSun|10:00:00-16:00:00"          trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32  120    "MonTueWedThuFri|08:00:00-20:00:00,SatSun|10:00:00-16:00:00"       trust
host    replication     all             ::1/128     120      "MonTueWedThuFri|08:00:00-20:00:00,SatSun|10:00:00-16:00:00"      trust

2、修改hba.c文件:函数parse_hba_line逐行解析pg_hba.conf文件，在此函数里增加对会话时长一列进行解析并保存：

        field = lnext(tok_line->fields, field);
    	if (!field)
    	{
    		ereport(elevel,
    				(errcode(ERRCODE_CONFIG_FILE_ERROR),
    				 errmsg("end-of-line before session time"),
    				 errcontext("line %d of configuration file \"%s\"",
    							line_num, HbaFileName)));
    		*err_msg = "end-of-line before session method";
    		return NULL;
    	}
        tokens = lfirst(field);
    	if (tokens->length > 1)
    	{
    		ereport(elevel,
    				(errcode(ERRCODE_CONFIG_FILE_ERROR),
    				 errmsg("multiple values specified for session time"),
    				 errhint("Specify exactly one session time per line."),
    				 errcontext("line %d of configuration file \"%s\"",
    							line_num, HbaFileName)));
    		*err_msg = "multiple values specified for session time";
    		return NULL;
    	}
    	token = linitial(tokens);
        {
            char *endptr = NULL;
            parsedline->session_time = strtoint(token->string, &endptr, 10);
            if (endptr == token->string || errno == ERANGE || parsedline->session_time < 0){
        		ereport(elevel,
        				(errcode(ERRCODE_CONFIG_FILE_ERROR),
        				 errmsg("The time setting of the session time is incorrect"),
        				 errhint("Specify exactly one session time per line."),
        				 errcontext("line %d of configuration file \"%s\"",
        							line_num, HbaFileName)));
        		*err_msg = "The time setting of the session time is incorrect";
        		return NULL;
        	}
        }

3、增加GUC参数：在postgresql.conf文件里增加参数session_timeout用于指定通用会话时长，默认为0，0为不限制：

	{
		{"session_timeout", PGC_USERSET, CLIENT_CONN_STATEMENT,
			gettext_noop("Sets the maximum allowed session time."),
			gettext_noop("A value of 0 turns off the timeout."),
			GUC_UNIT_MS
		},
		&SessionTimeout,
		0, 0, INT_MAX,
		NULL, NULL, NULL
	},

4、增加会话定时器：为会话定义并注册一个定时器：

typedef enum TimeoutId
{
	/* Predefined timeout reasons */
	STARTUP_PACKET_TIMEOUT,
	DEADLOCK_TIMEOUT,
	LOCK_TIMEOUT,
	STATEMENT_TIMEOUT,
	STANDBY_DEADLOCK_TIMEOUT,
	STANDBY_TIMEOUT,
	STANDBY_LOCK_TIMEOUT,
	IDLE_IN_TRANSACTION_SESSION_TIMEOUT,
	IDLE_SESSION_TIMEOUT,
	SESSION_TIMEOUT, //新加会话定时器

	CLIENT_CONNECTION_CHECK_TIMEOUT,
	/* First user-definable timeout reason */
	USER_TIMEOUT,
	/* Maximum number of timeout reasons */
	MAX_TIMEOUTS = USER_TIMEOUT + 10
} TimeoutId;

	if (!bootstrap)
	{
		RegisterTimeout(DEADLOCK_TIMEOUT, CheckDeadLockAlert);
		RegisterTimeout(STATEMENT_TIMEOUT, StatementTimeoutHandler);
		RegisterTimeout(LOCK_TIMEOUT, LockTimeoutHandler);
		RegisterTimeout(IDLE_IN_TRANSACTION_SESSION_TIMEOUT,
						IdleInTransactionSessionTimeoutHandler);
		RegisterTimeout(IDLE_SESSION_TIMEOUT, IdleSessionTimeoutHandler);
        //在此注册会话定时器
		RegisterTimeout(SESSION_TIMEOUT, SessionTimeoutHandler);

		RegisterTimeout(CLIENT_CONNECTION_CHECK_TIMEOUT, ClientCheckTimeoutHandler);
	}

//定时器事件回调函数
static void
SessionTimeoutHandler(void)
{
	SessionTimeoutPending = true;
	InterruptPending = true;
	SetLatch(MyLatch);
}

5、启动定时器：在PostgresMain函数中会话进入for循环之前使能定时器：

    if (IsUnderPostmaster){
        if (MyProcPort->hba->session_time > 0)
            SessionTimeout = MyProcPort->hba->session_time;
        /* Start the session timer */
        if (SessionTimeout > 0)
        {
            enable_timeout_after(SESSION_TIMEOUT,
                                SessionTimeout*60000);
        }
    }

通过IsUnderPostmaster判断是postgres进程模式，以hba文件里针对指定用户的会话时长设置为准，如果hba文件里配置该用户会话时长为0，则取GUC参数的设置，最终如果设置的会话时长大于0则使能定时器，会话时长参数设置以分钟为单位；

6、结束会话：在事件处理函数ProcessInterrupts里，增加对会话时长定时器的事件处理，在此为结束会话：

	if (ClientConnectionLost)
	{
		QueryCancelPending = false; /* lost connection trumps QueryCancel */
		LockErrorCleanup();
		/* don't send to client, we already know the connection to be dead. */
		whereToSendOutput = DestNone;
		ereport(FATAL,
				(errcode(ERRCODE_CONNECTION_FAILURE),
				 errmsg("connection to client lost")));
	}

7、测试：pg_hba.conf文件中配置所有host登录的用户会话时长为1分钟：

# TYPE  DATABASE        USER            ADDRESS        SESSION-TIME    TIME-RANGE         METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32  1  "MonTueWedThuFri|08:00:00-20:00:00,SatSun|10:00:00-16:00:00"     trust
# IPv6 local connections:
host    all             all             ::1/128   120    "MonTueWedThuFri|08:00:00-20:00:00,SatSun|10:00:00-16:00:00"          trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32  120    "MonTueWedThuFri|08:00:00-20:00:00,SatSun|10:00:00-16:00:00"       trust
host    replication     all             ::1/128     120      "MonTueWedThuFri|08:00:00-20:00:00,SatSun|10:00:00-16:00:00"      trust

登录测试：

postgres=# select now();
              now
-------------------------------
 2024-07-05 09:52:12.870426+08
(1 row)

postgres=# select now();
              now
-------------------------------
 2024-07-05 09:52:15.931039+08
(1 row)

postgres=# select now();
              now
-------------------------------
 2024-07-05 09:53:04.816819+08
(1 row)

postgres=# select now();
FATAL:  terminating connection due to session timeout
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

登录一分钟后会话被中断，中断原因为：terminating connection due to session timeout