Database

问题现象 在 GitLab 中将一个**公有（Public）项目改为私有（Private）**时，点击保存后失败，页面无明确错误提示。 查找日志 方法一：查看 production.log # 查看 GitLab Rails 生产日志 tail -f /var/log/gitlab/gitlab-rails/production.log 在执行"改为私有"操作的同时观察日志，会发现关键报错： OpenSSL::Cipher::CipherError () 方法二：使用 gitlab-ctl tail（官方推荐） gitlab-ctl tail gitlab-rails 原因 GitLab 在修改项目可见性时，会尝试对某些敏感字段（如 runner token）进行加密/解密操作。如果加密密钥或 token 数据异常，就会抛出 OpenSSL::Cipher::CipherError。 解决方法 进入 GitLab 数据库控制台 gitlab-rails dbconsole 清空 runner token 相关字段 -- 清空项目的 runner token UPDATE projects SET runners_token = null, runners_token_encrypted = null; -- 清空命名空间的 runner token UPDATE namespaces SET runners_token = null, runners_token_encrypted = null; -- 清空应用设置的注册 token UPDATE application_settings SET runners_registration_token_encrypted = null; -- 清空 CI runner 的 token UPDATE ci_runners SET token = null, token_encrypted = null; 执行后输出示例： ...

基础脚本 -- 创建用户 CREATE USER readonly WITH PASSWORD 'TJJe5CvU'; -- 授予连接数据库权限 GRANT CONNECT ON DATABASE jgsteel_prm TO readonly; -- 授予 schema 使用权限 GRANT USAGE ON SCHEMA public TO readonly; -- 授予现有表查询权限 GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly; -- 设置默认权限：未来新建的表也自动授予 SELECT ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly; 这个脚本能跑通，但有几个可以改进的地方。 生产环境强化版 -- ============================================ -- Create Read-Only User for PostgreSQL -- ============================================ -- Step 1: Create login role (idempotent) DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'readonly') THEN CREATE ROLE readonly WITH LOGIN PASSWORD 'TJJe5CvU'; RAISE NOTICE 'Role [readonly] created.'; ELSE RAISE NOTICE 'Role [readonly] already exists.'; END IF; END $$; -- Step 2: Grant CONNECT on target database GRANT CONNECT ON DATABASE jgsteel_prm TO readonly; -- Step 3: Switch to target database (run these inside the database) -- \c jgsteel_prm -- Step 4: Grant schema usage GRANT USAGE ON SCHEMA public TO readonly; -- Step 5: Grant SELECT on ALL existing tables GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly; -- Step 6: Set default privileges for future tables ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly; -- Step 7: (Optional) Grant SELECT on sequences if needed GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO readonly; -- Step 8: (Optional) Revoke write permissions explicitly REVOKE INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public FROM readonly; 关键点解读 1. ALTER DEFAULT PRIVILEGES — 最容易遗漏的一步 GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly; 这条命令只对执行时已存在的表生效。未来新建的表不会自动获得权限。 ...

基础脚本 创建一个只能执行 SELECT 查询的只读用户，标准做法如下： CREATE LOGIN onlyque WITH PASSWORD = '123456'; USE test; GO CREATE USER onlyque FOR LOGIN onlyque; USE test; GO GRANT SELECT ON SCHEMA::dbo TO onlyque; 这个脚本能跑通，但在生产环境下存在几个风险。 生产环境强化版 -- ============================================ -- Create Read-Only Query User for test database -- ============================================ -- Step 1: Create login at server level (with strong password) IF NOT EXISTS (SELECT 1 FROM sys.sql_logins WHERE name = 'onlyque') BEGIN CREATE LOGIN onlyque WITH PASSWORD = 'YourStr0ngP@ss2026' , DEFAULT_DATABASE = test , CHECK_POLICY = ON , CHECK_EXPIRATION = ON; PRINT 'Login [onlyque] created.'; END ELSE PRINT 'Login [onlyque] already exists.'; GO -- Step 2: Create database user USE test; GO IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'onlyque') BEGIN CREATE USER onlyque FOR LOGIN onlyque WITH DEFAULT_SCHEMA = dbo; PRINT 'User [onlyque] created in [test].'; END ELSE PRINT 'User [onlyque] already exists in [test].'; GO -- Step 3: Grant read-only permissions GRANT SELECT ON SCHEMA::dbo TO onlyque; -- Step 4: Explicitly deny write operations (defense in depth) DENY INSERT, UPDATE, DELETE ON SCHEMA::dbo TO onlyque; GO -- Step 5: Verify permissions SELECT dp.name AS principal_name, dp.type_desc, perm.permission_name, perm.state_desc, OBJECT_SCHEMA_NAME(perm.major_id) AS schema_name FROM sys.database_principals dp JOIN sys.database_permissions perm ON dp.principal_id = perm.grantee_principal_id WHERE dp.name = 'onlyque'; GO 关键改进点 1. 密码策略 — 拒绝弱口令 CHECK_POLICY = ON 强制使用 Windows 密码复杂度策略（大写 + 小写 + 数字 + 特殊字符，最少 8 位）。生产环境永远不要用 123456。 ...