【pg学习】-账号管理

一、概念

1.用户

用户是具有登录权限的角色，默认拥有登录数据库的权限。用户可以执行数据库操作，如查询、插入、更新等‌。

2.角色

角色是一系列相关权限的集合，可以将角色赋给多个用户，共享相同的权限‌。

二、实操

--创建只读账户
CREATE USER readonly_user WITH PASSWORD 'your_password';
--授予只读用户访问数据库的权限
GRANT CONNECT ON DATABASE your_database TO readonly_user;
--授予public权限
GRANT USAGE ON SCHEMA public TO readonly_user;
--授予只读权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
--禁止修改权限 撤销 "readonly_user" 在 "public" 模式下所有表的所有权限
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM readonly_user;

CREATE USER readonly_users WITH PASSWORD 'readonly';
GRANT CONNECT ON DATABASE tpm TO readonly_users;
GRANT USAGE ON SCHEMA public TO readonly_users;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_users;

--给单表新增查询权限
GRANT SELECT ON table_name TO readonly_users;


--查看用户
select *
from pg_user;
--查看特定用户的权限
SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication
FROM pg_roles;
--查看用户的数据库访问权限
SELECT * FROM pg_catalog.pg_roles r
JOIN pg_catalog.pg_auth_members m ON (r.oid = m.roleid)
JOIN pg_catalog.pg_roles mr ON (m.member = mr.oid)
WHERE mr.rolname = 'readonly_users';

--查看用户的表和列权限
SELECT * FROM information_schema.role_table_grants WHERE grantee = 'your_username';
SELECT * FROM information_schema.role_usage_grants WHERE grantee = 'your_username';
SELECT * FROM information_schema.role_column_grants WHERE grantee = 'your_username';