— 创建OAuth客户端表
CREATE TABLE fgedu_oauth_clients (
id SERIAL PRIMARY KEY,
client_id VARCHAR(100) UNIQUE NOT NULL,
client_secret VARCHAR(255) NOT NULL,
client_name VARCHAR(200),
redirect_uris TEXT[],
allowed_scopes TEXT[],
grant_types TEXT[] DEFAULT ARRAY[‘authorization_code’, ‘refresh_token’],
is_confidential BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW(),
is_active BOOLEAN DEFAULT TRUE
);

— 创建OAuth用户表
CREATE TABLE fgedu_oauth_users (
id SERIAL PRIMARY KEY,
oauth_provider VARCHAR(50) NOT NULL,
oauth_user_id VARCHAR(255) NOT NULL,
db_username VARCHAR(100) NOT NULL,
email VARCHAR(255),
display_name VARCHAR(200),
roles TEXT[],
created_at TIMESTAMP DEFAULT NOW(),
last_login TIMESTAMP,
is_active BOOLEAN DEFAULT TRUE,
UNIQUE(oauth_provider, oauth_user_id)
);

— 创建OAuth令牌表
CREATE TABLE fgedu_oauth_tokens (
id SERIAL PRIMARY KEY,
access_token VARCHAR(2000) NOT NULL,
refresh_token VARCHAR(500),
client_id VARCHAR(100) REFERENCES fgedu_oauth_clients(client_id),
user_id INTEGER REFERENCES fgedu_oauth_users(id),
scopes TEXT[],
expires_at TIMESTAMP NOT NULL,
refresh_expires_at TIMESTAMP,
created_at TIMESTAMP DEFAULT NOW(),
revoked BOOLEAN DEFAULT FALSE,
revoked_at TIMESTAMP,
ip_address VARCHAR(50),
user_agent TEXT
);

CREATE INDEX idx_tokens_access ON fgedu_oauth_tokens(access_token);
CREATE INDEX idx_tokens_refresh ON fgedu_oauth_tokens(refresh_token);
CREATE INDEX idx_tokens_expires ON fgedu_oauth_tokens(expires_at);

— 创建OAuth授权码表
CREATE TABLE fgedu_oauth_auth_codes (
id SERIAL PRIMARY KEY,
code VARCHAR(100) UNIQUE NOT NULL,
client_id VARCHAR(100) REFERENCES fgedu_oauth_clients(client_id),
user_id INTEGER REFERENCES fgedu_oauth_users(id),
redirect_uri TEXT,
scopes TEXT[],
expires_at TIMESTAMP NOT NULL,
created_at TIMESTAMP DEFAULT NOW(),
used BOOLEAN DEFAULT FALSE
);

CREATE INDEX idx_auth_codes_code ON fgedu_oauth_auth_codes(code);
CREATE INDEX idx_auth_codes_expires ON fgedu_oauth_auth_codes(expires_at);

— 创建认证审计日志表
CREATE TABLE fgedu_oauth_audit_log (
id SERIAL PRIMARY KEY,
event_type VARCHAR(50) NOT NULL,
client_id VARCHAR(100),
user_id INTEGER,
ip_address VARCHAR(50),
user_agent TEXT,
details JSONB,
created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_audit_log_time ON fgedu_oauth_audit_log(created_at);
CREATE INDEX idx_audit_log_user ON fgedu_oauth_audit_log(user_id);

— 插入测试客户端
INSERT INTO fgedu_oauth_clients(
client_id, client_secret, client_name,
redirect_uris, allowed_scopes
)
VALUES(
‘fgedu_web_fgapp’,
‘fgedu_secret_2026’,
‘FGEDU Web Application’,
ARRAY[‘https://fgapp.fgedu.net.cn/callback’],
ARRAY[‘read’, ‘write’, ‘admin’]
);

— 输出结果
INSERT 0 1

— 插入测试用户映射
INSERT INTO fgedu_oauth_users(
oauth_provider, oauth_user_id, db_username,
email, display_name, roles
)
VALUES(
‘keycloak’,
‘user-12345’,
‘fgedu_oauth_user’,
‘user@fgedu.net.cn’,
‘Test User’,
ARRAY[‘read’, ‘write’]
);

— 输出结果
INSERT 0 1

— 创建令牌验证函数
CREATE OR REPLACE FUNCTION fgedu_validate_access_token(
p_access_token TEXT
)
RETURNS TABLE (
is_valid BOOLEAN,
user_id INTEGER,
db_username VARCHAR,
scopes TEXT[],
expires_in INTEGER
)
AS $$
DECLARE
v_token RECORD;
BEGIN
SELECT
t.id,
t.user_id,
t.scopes,
t.expires_at,
t.revoked
INTO v_token
FROM fgedu_oauth_tokens t
WHERE t.access_token = p_access_token;

IF NOT FOUND THEN
RETURN QUERY SELECT FALSE, NULL::INTEGER, NULL::VARCHAR, NULL::TEXT[], NULL::INTEGER;
RETURN;
END IF;

IF v_token.revoked THEN
RETURN QUERY SELECT FALSE, NULL::INTEGER, NULL::VARCHAR, NULL::TEXT[], NULL::INTEGER;
RETURN;
END IF;

IF v_token.expires_at < NOW() THEN RETURN QUERY SELECT FALSE, NULL::INTEGER, NULL::VARCHAR, NULL::TEXT[], NULL::INTEGER; RETURN; END IF; RETURN QUERY SELECT TRUE, v_token.user_id, u.db_username, v_token.scopes, EXTRACT(EPOCH FROM (v_token.expires_at - NOW()))::INTEGER FROM fgedu_oauth_users u WHERE u.id = v_token.user_id; END; $$ LANGUAGE plpgsql SECURITY DEFINER; -- 测试令牌验证 SELECT * FROM fgedu_validate_access_token('invalid_token'); -- 输出结果 is_valid | user_id | db_username | scopes | expires_in ----------+---------+-------------+--------+------------ f | | | | (1 row) -- 创建访问令牌 INSERT INTO fgedu_oauth_tokens( access_token, refresh_token, client_id, user_id, scopes, expires_at, refresh_expires_at ) VALUES( 'test_access_token_12345', 'test_refresh_token_67890', 'fgedu_web_fgapp', 1, ARRAY['read', 'write'], NOW() + INTERVAL '1 hour', NOW() + INTERVAL '7 days' ); -- 输出结果 INSERT 0 1 -- 再次测试令牌验证 SELECT * FROM fgedu_validate_access_token('test_access_token_12345'); -- 输出结果 is_valid | user_id | db_username | scopes | expires_in ----------+---------+------------------+------------------+------------ t | 1 | fgedu_oauth_user | {read,write} | 3600 (1 row)

— 创建令牌刷新函数
CREATE OR REPLACE FUNCTION fgedu_refresh_access_token(
p_refresh_token TEXT,
p_client_id TEXT,
p_client_secret TEXT
)
RETURNS TABLE (
success BOOLEAN,
access_token TEXT,
refresh_token TEXT,
expires_in INTEGER,
error_message TEXT
)
AS $$
DECLARE
v_client RECORD;
v_token RECORD;
v_new_access_token TEXT;
v_new_refresh_token TEXT;
BEGIN
SELECT * INTO v_client
FROM fgedu_oauth_clients
WHERE client_id = p_client_id
AND client_secret = p_client_secret
AND is_active = TRUE;

IF NOT FOUND THEN
RETURN QUERY SELECT FALSE, NULL::TEXT, NULL::TEXT, NULL::INTEGER, ‘Invalid client credentials’::TEXT;
RETURN;
END IF;

SELECT * INTO v_token
FROM fgedu_oauth_tokens
WHERE refresh_token = p_refresh_token
AND client_id = p_client_id
AND revoked = FALSE;

IF NOT FOUND THEN
RETURN QUERY SELECT FALSE, NULL::TEXT, NULL::TEXT, NULL::INTEGER, ‘Invalid refresh token’::TEXT;
RETURN;
END IF;

IF v_token.refresh_expires_at < NOW() THEN RETURN QUERY SELECT FALSE, NULL::TEXT, NULL::TEXT, NULL::INTEGER, 'Refresh token expired'::TEXT; RETURN; END IF; UPDATE fgedu_oauth_tokens SET revoked = TRUE, revoked_at = NOW() WHERE id = v_token.id; v_new_access_token := 'access_' || encode(gen_random_bytes(32), 'hex'); v_new_refresh_token := 'refresh_' || encode(gen_random_bytes(32), 'hex'); INSERT INTO fgedu_oauth_tokens( access_token, refresh_token, client_id, user_id, scopes, expires_at, refresh_expires_at ) VALUES( v_new_access_token, v_new_refresh_token, p_client_id, v_token.user_id, v_token.scopes, NOW() + INTERVAL '1 hour', NOW() + INTERVAL '7 days' ); INSERT INTO fgedu_oauth_audit_log( event_type, client_id, user_id, details ) VALUES( 'TOKEN_REFRESH', p_client_id, v_token.user_id, jsonb_build_object('old_token_id', v_token.id) ); RETURN QUERY SELECT TRUE, v_new_access_token, v_new_refresh_token, 3600, NULL::TEXT; END; $$ LANGUAGE plpgsql SECURITY DEFINER; -- 测试令牌刷新 SELECT * FROM fgedu_refresh_access_token( 'test_refresh_token_67890', 'fgedu_web_fgapp', 'fgedu_secret_2026' ); -- 输出结果 success | access_token | refresh_token | expires_in | error_message ---------+-----------------------------------------+-----------------------------------------+------------+--------------- t | access_abc123def456... | refresh_xyz789uvw012... | 3600 | (1 row)

— 创建令牌撤销函数
CREATE OR REPLACE FUNCTION fgedu_revoke_token(
p_token TEXT,
p_client_id TEXT,
p_client_secret TEXT
)
RETURNS BOOLEAN
AS $$
DECLARE
v_client RECORD;
v_revoked INTEGER;
BEGIN
SELECT * INTO v_client
FROM fgedu_oauth_clients
WHERE client_id = p_client_id
AND client_secret = p_client_secret
AND is_active = TRUE;

IF NOT FOUND THEN
RETURN FALSE;
END IF;

UPDATE fgedu_oauth_tokens
SET revoked = TRUE, revoked_at = NOW()
WHERE (access_token = p_token OR refresh_token = p_token)
AND client_id = p_client_id
AND revoked = FALSE;

GET DIAGNOSTICS v_revoked = ROW_COUNT;

IF v_revoked > 0 THEN
INSERT INTO fgedu_oauth_audit_log(
event_type, client_id, details
)
VALUES(
‘TOKEN_REVOKED’,
p_client_id,
jsonb_build_object(‘token_prefix’, LEFT(p_token, 20))
);

RETURN TRUE;
END IF;

RETURN FALSE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

— 测试令牌撤销
SELECT fgedu_revoke_token(
‘test_access_token_12345’,
‘fgedu_web_fgapp’,
‘fgedu_secret_2026’
);

— 输出结果
fgedu_revoke_token
——————–
t
(1 row)

— 验证令牌已被撤销
SELECT * FROM fgedu_validate_access_token(‘test_access_token_12345’);

— 输出结果
is_valid | user_id | db_username | scopes | expires_in
———-+———+————-+——–+————
f | | | |
(1 row)

— 创建OAuth验证扩展
CREATE SCHEMA IF NOT EXISTS oauth;

— 创建验证视图
CREATE OR REPLACE VIEW oauth.token_info AS
SELECT
t.access_token,
t.refresh_token,
t.client_id,
t.user_id,
u.db_username,
u.email,
u.display_name,
u.roles,
t.scopes,
t.expires_at,
t.refresh_expires_at,
t.created_at,
t.revoked
FROM fgedu_oauth_tokens t
JOIN fgedu_oauth_users u ON t.user_id = u.id;

— 创建活跃令牌视图
CREATE OR REPLACE VIEW oauth.active_tokens AS
SELECT
access_token,
client_id,
user_id,
db_username,
scopes,
expires_at – NOW() AS remaining_time
FROM oauth.token_info
WHERE revoked = FALSE
AND expires_at > NOW();

— 创建清理过期令牌函数
CREATE OR REPLACE FUNCTION oauth.cleanup_expired_tokens()
RETURNS void
AS $$
DECLARE
v_deleted INTEGER;
BEGIN
DELETE FROM fgedu_oauth_tokens
WHERE expires_at < NOW() - INTERVAL '7 days'; GET DIAGNOSTICS v_deleted = ROW_COUNT; RAISE NOTICE 'Cleaned up % expired tokens', v_deleted; DELETE FROM fgedu_oauth_auth_codes WHERE expires_at < NOW(); GET DIAGNOSTICS v_deleted = ROW_COUNT; RAISE NOTICE 'Cleaned up % expired auth codes', v_deleted; END; $$ LANGUAGE plpgsql; -- 测试清理函数 SELECT oauth.cleanup_expired_tokens(); -- 输出结果 NOTICE: Cleaned up 0 expired tokens NOTICE: Cleaned up 0 expired auth codes oauth.cleanup_expired_tokens ------------------------------- (1 row)

— 创建授权码生成函数
CREATE OR REPLACE FUNCTION fgedu_generate_auth_code(
p_client_id TEXT,
p_user_id INTEGER,
p_redirect_uri TEXT,
p_scopes TEXT[]
)
RETURNS TEXT
AS $$
DECLARE
v_code TEXT;
v_client RECORD;
BEGIN
SELECT * INTO v_client
FROM fgedu_oauth_clients
WHERE client_id = p_client_id
AND is_active = TRUE;

IF NOT FOUND THEN
RAISE EXCEPTION ‘Invalid client_id’;
END IF;

IF p_redirect_uri != ALL(v_client.redirect_uris) THEN
RAISE EXCEPTION ‘Invalid redirect_uri’;
END IF;

v_code := encode(gen_random_bytes(32), ‘hex’);

INSERT INTO fgedu_oauth_auth_codes(
code, client_id, user_id, redirect_uri, scopes, expires_at
)
VALUES(
v_code, p_client_id, p_user_id, p_redirect_uri, p_scopes,
NOW() + INTERVAL ’10 minutes’
);

INSERT INTO fgedu_oauth_audit_log(
event_type, client_id, user_id, details
)
VALUES(
‘AUTH_CODE_GENERATED’,
p_client_id,
p_user_id,
jsonb_build_object(‘scopes’, p_scopes)
);

RETURN v_code;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

— 测试授权码生成
SELECT fgedu_generate_auth_code(
‘fgedu_web_fgapp’,
1,
‘https://fgapp.fgedu.net.cn/callback’,
ARRAY[‘read’, ‘write’]
);

— 输出结果
fgedu_generate_auth_code
——————————————
abc123def456ghi789jkl012mno345pqr678…
(1 row)

— 创建令牌交换函数
CREATE OR REPLACE FUNCTION fgedu_exchange_code_for_token(
p_code TEXT,
p_client_id TEXT,
p_client_secret TEXT,
p_redirect_uri TEXT
)
RETURNS TABLE (
success BOOLEAN,
access_token TEXT,
refresh_token TEXT,
token_type TEXT,
expires_in INTEGER,
scope TEXT,
error_message TEXT
)
AS $$
DECLARE
v_client RECORD;
v_auth_code RECORD;
v_access_token TEXT;
v_refresh_token TEXT;
BEGIN
SELECT * INTO v_client
FROM fgedu_oauth_clients
WHERE client_id = p_client_id
AND client_secret = p_client_secret
AND is_active = TRUE;

IF NOT FOUND THEN
RETURN QUERY SELECT FALSE, NULL::TEXT, NULL::TEXT, NULL::TEXT, NULL::INTEGER, NULL::TEXT, ‘Invalid client credentials’::TEXT;
RETURN;
END IF;

SELECT * INTO v_auth_code
FROM fgedu_oauth_auth_codes
WHERE code = p_code
AND client_id = p_client_id
AND redirect_uri = p_redirect_uri
AND used = FALSE
AND expires_at > NOW();

IF NOT FOUND THEN
RETURN QUERY SELECT FALSE, NULL::TEXT, NULL::TEXT, NULL::TEXT, NULL::INTEGER, NULL::TEXT, ‘Invalid or expired authorization code’::TEXT;
RETURN;
END IF;

UPDATE fgedu_oauth_auth_codes
SET used = TRUE
WHERE id = v_auth_code.id;

v_access_token := ‘access_’ || encode(gen_random_bytes(32), ‘hex’);
v_refresh_token := ‘refresh_’ || encode(gen_random_bytes(32), ‘hex’);

INSERT INTO fgedu_oauth_tokens(
access_token, refresh_token, client_id, user_id,
scopes, expires_at, refresh_expires_at
)
VALUES(
v_access_token,
v_refresh_token,
p_client_id,
v_auth_code.user_id,
v_auth_code.scopes,
NOW() + INTERVAL ‘1 hour’,
NOW() + INTERVAL ‘7 days’
);

INSERT INTO fgedu_oauth_audit_log(
event_type, client_id, user_id, details
)
VALUES(
‘TOKEN_ISSUED’,
p_client_id,
v_auth_code.user_id,
jsonb_build_object(‘auth_code_id’, v_auth_code.id)
);

RETURN QUERY
SELECT
TRUE,
v_access_token,
v_refresh_token,
‘Bearer’::TEXT,
3600,
array_to_string(v_auth_code.scopes, ‘ ‘),
NULL::TEXT;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

— 测试令牌交换
SELECT * FROM fgedu_exchange_code_for_token(
‘abc123def456ghi789jkl012mno345pqr678’,
‘fgedu_web_fgapp’,
‘fgedu_secret_2026’,
‘https://fgapp.fgedu.net.cn/callback’
);

— 输出结果
success | access_token | refresh_token | token_type | expires_in | scope | error_message
———+—————————————–+—————————————–+————+————+————-+—————
t | access_xyz789abc012… | refresh_def345ghi678… | Bearer | 3600 | read write |
(1 row)

— 创建客户端认证函数
CREATE OR REPLACE FUNCTION fgedu_oauth_authenticate(
p_access_token TEXT
)
RETURNS TABLE (
authenticated BOOLEAN,
user_id INTEGER,
username VARCHAR,
email VARCHAR,
roles TEXT[],
scopes TEXT[],
error_message TEXT
)
AS $$
DECLARE
v_validation RECORD;
BEGIN
SELECT * INTO v_validation
FROM fgedu_validate_access_token(p_access_token);

IF NOT v_validation.is_valid THEN
RETURN QUERY
SELECT
FALSE,
NULL::INTEGER,
NULL::VARCHAR,
NULL::VARCHAR,
NULL::TEXT[],
NULL::TEXT[],
‘Invalid or expired access token’::TEXT;
RETURN;
END IF;

RETURN QUERY
SELECT
TRUE,
v_validation.user_id,
v_validation.db_username,
u.email,
u.roles,
v_validation.scopes,
NULL::TEXT
FROM fgedu_oauth_users u
WHERE u.id = v_validation.user_id;

UPDATE fgedu_oauth_users
SET last_login = NOW()
WHERE id = v_validation.user_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

— 测试认证
SELECT * FROM fgedu_oauth_authenticate(‘access_xyz789abc012…’);

— 输出结果
authenticated | user_id | username | email | roles | scopes | error_message
—————+———+——————-+———————-+—————–+————–+—————
t | 1 | fgedu_oauth_user | user@fgedu.net.cn | {read,write} | {read,write} |
(1 row)

— 创建权限检查函数
CREATE OR REPLACE FUNCTION fgedu_check_permission(
p_access_token TEXT,
p_required_scope TEXT
)
RETURNS BOOLEAN
AS $$
DECLARE
v_validation RECORD;
BEGIN
SELECT * INTO v_validation
FROM fgedu_validate_access_token(p_access_token);

IF NOT v_validation.is_valid THEN
RETURN FALSE;
END IF;

IF p_required_scope = ANY(v_validation.scopes) THEN
RETURN TRUE;
END IF;

RETURN FALSE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

— 测试权限检查
SELECT fgedu_check_permission(‘access_xyz789abc012…’, ‘read’);
SELECT fgedu_check_permission(‘access_xyz789abc012…’, ‘admin’);

— 输出结果
fgedu_check_permission
————————
t
(1 row)

fgedu_check_permission
————————
f
(1 row)

— 创建受保护的数据表
CREATE TABLE fgedu_protected_data (
id SERIAL PRIMARY KEY,
data_name VARCHAR(200),
data_value TEXT,
created_by INTEGER,
created_at TIMESTAMP DEFAULT NOW()
);

— 创建受保护的API函数
CREATE OR REPLACE FUNCTION fgedu_api_get_data(
p_access_token TEXT,
p_data_id INTEGER DEFAULT NULL
)
RETURNS TABLE (
success BOOLEAN,
data JSONB,
error_message TEXT
)
AS $$
DECLARE
v_auth RECORD;
BEGIN
SELECT * INTO v_auth
FROM fgedu_oauth_authenticate(p_access_token);

IF NOT v_auth.authenticated THEN
RETURN QUERY
SELECT
FALSE,
NULL::JSONB,
v_auth.error_message;
RETURN;
END IF;

IF NOT fgedu_check_permission(p_access_token, ‘read’) THEN
RETURN QUERY
SELECT
FALSE,
NULL::JSONB,
‘Permission denied: read scope required’::TEXT;
RETURN;
END IF;

IF p_data_id IS NULL THEN
RETURN QUERY
SELECT
TRUE,
jsonb_agg(
jsonb_build_object(
‘id’, d.id,
‘data_name’, d.data_name,
‘data_value’, d.data_value,
‘created_at’, d.created_at
)
),
NULL::TEXT
FROM fgedu_protected_data d;
ELSE
RETURN QUERY
SELECT
TRUE,
jsonb_build_object(
‘id’, d.id,
‘data_name’, d.data_name,
‘data_value’, d.data_value,
‘created_at’, d.created_at
),
NULL::TEXT
FROM fgedu_protected_data d
WHERE d.id = p_data_id;
END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

— 创建受保护的写入函数
CREATE OR REPLACE FUNCTION fgedu_api_create_data(
p_access_token TEXT,
p_data_name VARCHAR,
p_data_value TEXT
)
RETURNS TABLE (
success BOOLEAN,
new_id INTEGER,
error_message TEXT
)
AS $$
DECLARE
v_auth RECORD;
v_new_id INTEGER;
BEGIN
SELECT * INTO v_auth
FROM fgedu_oauth_authenticate(p_access_token);

IF NOT v_auth.authenticated THEN
RETURN QUERY
SELECT
FALSE,
NULL::INTEGER,
v_auth.error_message;
RETURN;
END IF;

IF NOT fgedu_check_permission(p_access_token, ‘write’) THEN
RETURN QUERY
SELECT
FALSE,
NULL::INTEGER,
‘Permission denied: write scope required’::TEXT;
RETURN;
END IF;

INSERT INTO fgedu_protected_data(
data_name, data_value, created_by
)
VALUES(
p_data_name, p_data_value, v_auth.user_id
)
RETURNING id INTO v_new_id;

INSERT INTO fgedu_oauth_audit_log(
event_type, user_id, details
)
VALUES(
‘DATA_CREATED’,
v_auth.user_id,
jsonb_build_object(
‘data_id’, v_new_id,
‘data_name’, p_data_name
)
);

RETURN QUERY
SELECT
TRUE,
v_new_id,
NULL::TEXT;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

— 测试API
SELECT * FROM fgedu_api_create_data(
‘access_xyz789abc012…’,
‘Test Data’,
‘This is a test data value’
);

— 输出结果
success | new_id | error_message
———+——–+—————
t | 1 |
(1 row)

— 查询数据
SELECT * FROM fgedu_api_get_data(‘access_xyz789abc012…’);

— 输出结果
success | data | error_message
———+—————————————————————————–+—————
t | [{“id”: 1, “data_name”: “Test Data”, “data_value”: “This is a test…”, …}] |
(1 row)