SRE文档

深浅模式

  • pg_12.2

1.创建只读用户 ​

创建用户及指定密码：

CREATE USER readonly WITH ENCRYPTED PASSWORD 'ropass';
CREATE USER readonly WITH ENCRYPTED PASSWORD 'ropass';

设置用户默认事务只读：

alter user readonly set default_transaction_read_only=on;
alter user user_name with password '';
alter user user_name with CONNECTION LIMIT  20;#连接数限制
alter user readonly set default_transaction_read_only=on;
alter user user_name with password '';
alter user user_name with CONNECTION LIMIT  20;#连接数限制

赋予用户权限，查看public模式下所有表：

GRANT USAGE ON SCHEMA public to readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
GRANT USAGE ON SCHEMA public to readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

赋予用户连接数据库权限：

GRANT CONNECT ON DATABASE database_name to username;
GRANT CONNECT ON DATABASE database_name to username;

切换到指定数据库：

\c foo
\c foo

赋予用户表、序列查看权限：

GRANT USAGE ON SCHEMA public to username;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO username;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO username;


#弊端，每次有新的表添加，的重新赋值
GRANT USAGE ON SCHEMA public to username;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO username;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO username;


#弊端，每次有新的表添加，的重新赋值

2.查询 ​

1、查看某用户的表权限 ​

postgres=# \c usercenter
usercenter=# select * from information_schema.table_privileges where grantee='user_name';
postgres=# \c usercenter
usercenter=# select * from information_schema.table_privileges where grantee='user_name';

2、查看usage权限表 ​

select * from information_schema.usage_privileges where grantee='user_name';
select * from information_schema.usage_privileges where grantee='user_name';

3、查看存储过程函数相关权限表 ​

select * from information_schema.routine_privileges where grantee='user_name';
select * from information_schema.routine_privileges where grantee='user_name';

4、查看当前用户能够访问的数据类型 ​

select * from information_schema.data_type_privileges ;
select * from information_schema.data_type_privileges ;

5、查看系统有多少用户 ​

select * from pg_user;
select * from pg_user;

image-20240401153555377

2.角色 ​

2.1查看角色 ​

select * from pg_roles;

pg_database_owner	f	t	f	f	f	f	-1	********
pg_read_all_data	f	t	f	f	f	f	-1	********
pg_write_all_data	f	t	f	f	f	f	-1	********
pg_monitor	f	t	f	f	f	f	-1	********
pg_read_all_settings	f	t	f	f	f	f	-1	********
pg_read_all_stats	f	t	f	f	f	f	-1	********
pg_stat_scan_tables	f	t	f	f	f	f	-1	********
pg_read_server_files	f	t	f	f	f	f	-1	********
pg_write_server_files	f	t	f	f	f	f	-1	********
pg_execute_server_program	f	t	f	f	f	f	-1	********
pg_signal_backend	f	t	f	f	f	f	-1	********
pg_checkpoint	f	t	f	f	f	f	-1	********
pg_use_reserved_connections	f	t	f	f	f	f	-1	********
pg_create_subscription	f	t	f	f	f	f	-1	********
postgres	t	t	t	t	t	t	-1	********
select * from pg_roles;

pg_database_owner	f	t	f	f	f	f	-1	********
pg_read_all_data	f	t	f	f	f	f	-1	********
pg_write_all_data	f	t	f	f	f	f	-1	********
pg_monitor	f	t	f	f	f	f	-1	********
pg_read_all_settings	f	t	f	f	f	f	-1	********
pg_read_all_stats	f	t	f	f	f	f	-1	********
pg_stat_scan_tables	f	t	f	f	f	f	-1	********
pg_read_server_files	f	t	f	f	f	f	-1	********
pg_write_server_files	f	t	f	f	f	f	-1	********
pg_execute_server_program	f	t	f	f	f	f	-1	********
pg_signal_backend	f	t	f	f	f	f	-1	********
pg_checkpoint	f	t	f	f	f	f	-1	********
pg_use_reserved_connections	f	t	f	f	f	f	-1	********
pg_create_subscription	f	t	f	f	f	f	-1	********
postgres	t	t	t	t	t	t	-1	********
  • 查看角色的命令

\du role_name

sql
postgres=# \du pg_database_owner 
          List of roles
     Role name     |  Attributes  
-------------------+--------------
 pg_database_owner | Cannot login
postgres=# \du pg_database_owner 
          List of roles
     Role name     |  Attributes  
-------------------+--------------
 pg_database_owner | Cannot login