-- 1. 创建mydb_admin管理员账户 -- 格式:`CREATE USER 'username'@'host' IDENTIFIED WITH authentication_plugin BY 'password';` CREATEUSER'mydb_admin'@'%' IDENTIFIED WITH caching_sha2_password BY'your_password';
-- 2. 授权数据库/表访问权限 -- 格式:`GRANT PRIVILEGE ON database.table TO 'username'@'host';` GRANTALL PRIVILEGES ON mydb.*TO'mydb_admin'@'%'; -- 如果不清楚有哪些权限,执行 -- SHOW PRIVILEGES;
-- 3. 立即生效 FLUSH PRIVILEGES;
-- 4. 查看账户列表核对 SELECT*FROM mysql.user; -- SELECT * FROM mysql.user WHERE User='mydb_admin' AND Host='%';
-- 1. 创建mydb_admin管理员账户 CREATE ROLE mydb_admin LOGIN PASSWORD 'your_password';
-- 2. 授权数据库/表访问权限(需要多条语句) GRANTALL PRIVILEGES ON DATABASE mydb TO mydb_admin; -- 如果不想让账号访问其他db -- REVOKE CONNECT ON DATABASE otherdb1 FROM PUBLIC;
-- 所有跟public相关的授权都需要先切换到指定数据库 \c mydb; -- 授权public GRANTALL PRIVILEGES ON SCHEMA public TO mydb_admin; -- 后续所有变更自动授权(由super admin执行) ALTERDEFAULT PRIVILEGES IN SCHEMA public GRANTALLON TABLES TO mydb_admin; ALTERDEFAULT PRIVILEGES IN SCHEMA public GRANTALLON SEQUENCES TO mydb_admin; ALTERDEFAULT PRIVILEGES IN SCHEMA public GRANTALLON FUNCTIONS TO mydb_admin;
-- 3. 删除角色 -- DROP OWNED BY mydb_admin; -- DROP ROLE mydb_admin;
-- 角色授权,只能连接mydb GRANTCONNECTON DATABASE mydb TO mydb_write_role; -- 所有跟public相关的授权都需要先切换到指定数据库 \c mydb; GRANT USAGE ON SCHEMA public TO mydb_write_role; GRANTSELECT, INSERT, UPDATE, DELETEONALL TABLES IN SCHEMA public TO mydb_write_role; -- 后续所有变更自动授权(由super admin以及mydb_admin执行) ALTERDEFAULT PRIVILEGES IN SCHEMA public GRANTSELECT, INSERT, UPDATE, DELETEON TABLES TO mydb_write_role;
-- 创建用户并关联角色 CREATEUSER mydb_write_user WITH PASSWORD 'your_password'; GRANT mydb_write_role TO mydb_write_user;
-- 2. 创建只读账户 -- 创建角色 CREATE ROLE mydb_read_role;
-- 角色授权 GRANTCONNECTON DATABASE mydb TO mydb_read_role; -- 所有跟public相关的授权都需要先切换到指定数据库 \c mydb; GRANT USAGE ON SCHEMA public TO mydb_read_role; GRANTSELECTONALL TABLES IN SCHEMA public TO mydb_read_role; -- 后续所有变更自动授权(由super admin以及mydb_admin执行) ALTERDEFAULT PRIVILEGES IN SCHEMA public GRANTSELECTON TABLES TO mydb_read_role;
-- 创建用户并关联角色 CREATEUSER mydb_read_user WITH PASSWORD 'your_password'; GRANT mydb_read_role TO mydb_read_user;
DDL
建库
MySQL建库操作如下
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
-- 1. 列出所有数据库 SHOW DATABASES;
-- 2. 创建mydb数据库 CREATE DATABASE mydb; -- 创建并设置charset -- CREATE DATABASE mydb CHARACTER SET utf8mb4;
# 重新打tag docker tag registry.k8s.io/kube-apiserver:v1.31.0 registry.noname.io:5000/kube-apiserver:v1.31.0 docker tag registry.k8s.io/kube-controller-manager:v1.31.0 registry.noname.io:5000/kube-controller-manager:v1.31.0 docker tag registry.k8s.io/kube-scheduler:v1.31.0 registry.noname.io:5000/kube-scheduler:v1.31.0 docker tag registry.k8s.io/kube-proxy:v1.31.0 registry.noname.io:5000/kube-proxy:v1.31.0 docker tag registry.k8s.io/coredns/coredns:v1.11.1 registry.noname.io:5000/coredns:v1.11.1 docker tag registry.k8s.io/pause:3.10 registry.noname.io:5000/pause:3.10 docker tag registry.k8s.io/etcd:3.5.15-0 registry.noname.io:5000/etcd:3.5.15-0 docker tag quay.io/cilium/cilium:v1.15.6 registry.noname.io:5000/cilium/cilium:v1.15.6 docker tag quay.io/cilium/operator-generic:v1.15.6 registry.noname.io:5000/cilium/operator-generic:v1.15.6
上面debian的修改会引入另一个问题,就是vm无法连通外网了,发现是dhcp有异常,进而导致ImagePullBackOff,也不知道为什么不能直接读取本地的image,需要使用systemd-networkd才行,详细看问题描述Configuring systemd-networkd on Debian
# TYPE DATABASE USER ADDRESS METHOD host blogdb all 0.0.0.0/0 scram-sha-256 host blogdb all ::0/0 scram-sha-256
重启postgresql
1
sudo systemctl start postgresql
修改超级用户密码或者创建新角色并授权
1 2 3 4 5 6 7 8 9 10 11 12 13
-- 1. 修改postgres密码 -- alter user postgres password 'your_password';
-- 2. 创建新角色并授权 \c blogdb;
CREATE ROLE blog_read_only WITH LOGIN PASSWORD 'your_password'; CREATE ROLE blog_read_write WITH LOGIN PASSWORD 'your_password'; CREATE ROLE blog_admin WITH LOGIN PASSWORD 'your_password';
GRANTSELECTONALL TABLES IN SCHEMA public TO blog_read_only; GRANTSELECT, INSERT, UPDATE, DELETEONALL TABLES IN SCHEMA public TO blog_read_write; GRANTALL PRIVILEGES ONALL TABLES IN SCHEMA public TO blog_admin;