SRE文档

深浅模式

1. 创建库和用户 ​

sql
create user transuser with password 'transiff61384e';

create database taoism_translation owner transuser;
grant all privileges on database taoism_translation to transuser;
create user transuser with password 'transiff61384e';

create database taoism_translation owner transuser;
grant all privileges on database taoism_translation to transuser;

2. 授权 ​

sql
-- 1. 创建用户(如果还没创建)
CREATE USER transuser WITH PASSWORD 'transiff61384e';

-- 2. 创建数据库(如果还没创建)
CREATE DATABASE taoism_translation WITH OWNER transuser;

-- 3. 撤销用户对public schema的权限(可选,增加安全性)
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM public;

-- 4. 授予用户连接权限
GRANT CONNECT ON DATABASE taoism_translation TO transuser;

-- 5. 授予用户对数据库中表的权限
\c taoism_translation
GRANT USAGE ON SCHEMA public TO transuser;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO transuser;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO transuser;

-- 6. 撤销对其他数据库的访问权限(如果有)
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM transuser;


-- 7. 修改表的属主，alter不是类型权限，无法进行授权，注意执行这个慎重，先前添加的授权会取消
ALTER TABLE table_name OWNER TO name_user;

-- 批量修改
DO $$
BEGIN
   EXECUTE (
       SELECT string_agg(
           format('ALTER TABLE public.%I OWNER TO transuser;', table_name),
           ' '
       )
       FROM information_schema.tables
       WHERE table_schema = 'public'
   );
END $$;
-- 1. 创建用户(如果还没创建)
CREATE USER transuser WITH PASSWORD 'transiff61384e';

-- 2. 创建数据库(如果还没创建)
CREATE DATABASE taoism_translation WITH OWNER transuser;

-- 3. 撤销用户对public schema的权限(可选,增加安全性)
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM public;

-- 4. 授予用户连接权限
GRANT CONNECT ON DATABASE taoism_translation TO transuser;

-- 5. 授予用户对数据库中表的权限
\c taoism_translation
GRANT USAGE ON SCHEMA public TO transuser;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO transuser;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO transuser;

-- 6. 撤销对其他数据库的访问权限(如果有)
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM transuser;


-- 7. 修改表的属主，alter不是类型权限，无法进行授权，注意执行这个慎重，先前添加的授权会取消
ALTER TABLE table_name OWNER TO name_user;

-- 批量修改
DO $$
BEGIN
   EXECUTE (
       SELECT string_agg(
           format('ALTER TABLE public.%I OWNER TO transuser;', table_name),
           ' '
       )
       FROM information_schema.tables
       WHERE table_schema = 'public'
   );
END $$;

3. 备份 ​

sql
pg_dump -U postgres -h 127.0.0.1 -p 5532 -d fllowordersystem  --no-owner --no-tablespaces --no-privileges >fll.sql

pg_dump -U postgres -h 127.0.0.1 -p 5532  -t fllowordersetting fllowordersystem  --no-owner --no-tablespaces --no-privileges >fllowordersetting.sql
pg_dump -U postgres -h 127.0.0.1 -p 5532 -d fllowordersystem  --no-owner --no-tablespaces --no-privileges >fll.sql

pg_dump -U postgres -h 127.0.0.1 -p 5532  -t fllowordersetting fllowordersystem  --no-owner --no-tablespaces --no-privileges >fllowordersetting.sql

4. 查看 ​

  • 查看序列user_wx_id_seq属于谁
sql
SELECT usename FROM pg_user u JOIN pg_class c ON (c.relowner = u.usesysid) WHERE c.relname = 'user_wx_id_seq';
SELECT usename FROM pg_user u JOIN pg_class c ON (c.relowner = u.usesysid) WHERE c.relname = 'user_wx_id_seq';
  • 查看用户是否有该权限
sql
SELECT has_sequence_privilege('username', 'user_wx_id_seq', 'SELECT,USAGE');
SELECT has_sequence_privilege('username', 'user_wx_id_seq', 'SELECT,USAGE');
  • 授权
sql
\c dataName
GRANT SELECT, USAGE ON SEQUENCE user_wx_id_seq TO username;
\c dataName
GRANT SELECT, USAGE ON SEQUENCE user_wx_id_seq TO username;

4.1 元命令 ​

通常可以使用\dp+ 列出用户,schema的默认权限。列出表、视图、序列的权限,但是它读起来比较不太方便。

\dp+
\dp+

4.2 查看视图 ​

select * from pg_roles;
select * from pg_roles;
  • 查看单表权限
sql
SELECT grantee,table_schema,table_name,string_agg( privilege_type,', ' ) as privilege_type
FROM information_schema.role_table_grants
WHERE table_name='xxx' --替换自己的
group by grantee,table_schema,table_name;

#显示结果
     grantee     | table_schema | table_name |                        privilege_type
-----------------+--------------+------------+---------------------------------------------------------------
 transuser       | public       | text_info  | INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER
SELECT grantee,table_schema,table_name,string_agg( privilege_type,', ' ) as privilege_type
FROM information_schema.role_table_grants
WHERE table_name='xxx' --替换自己的
group by grantee,table_schema,table_name;

#显示结果
     grantee     | table_schema | table_name |                        privilege_type
-----------------+--------------+------------+---------------------------------------------------------------
 transuser       | public       | text_info  | INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER
  • 查看某用户的权限

替换username

sql
SELECT grantee,table_schema,table_name, string_agg( privilege_type,', ' ) as privilege_type FROM information_schema.role_table_grants where grantee='username' group by table_name,table_schema,grantee;
SELECT grantee,table_schema,table_name, string_agg( privilege_type,', ' ) as privilege_type FROM information_schema.role_table_grants where grantee='username' group by table_name,table_schema,grantee;

1. schema权限 ​

关注usage的权限，如果没有这个权限，访问 schema有时候会遇到问题

sql
select a.nspname,b.rolname,string_agg(a.pri_t,',') from (select nspname,(aclexplode(COALESCE(nspacl, acldefault('n'::"char",nspowner)))).grantee as grantee,(aclexplode(COALESCE(nspacl, acldefault('n'::"char",nspowner)))).privilege_type as pri_t from pg_namespace where nspname not like 'pg%' and nspname <> 'information_schema') a,pg_authid b where (a.grantee=b.oid or a.grantee=0) and b.rolname='username' group by a.nspname,b.rolname;
select a.nspname,b.rolname,string_agg(a.pri_t,',') from (select nspname,(aclexplode(COALESCE(nspacl, acldefault('n'::"char",nspowner)))).grantee as grantee,(aclexplode(COALESCE(nspacl, acldefault('n'::"char",nspowner)))).privilege_type as pri_t from pg_namespace where nspname not like 'pg%' and nspname <> 'information_schema') a,pg_authid b where (a.grantee=b.oid or a.grantee=0) and b.rolname='username' group by a.nspname,b.rolname;

4.3 函数权限验证 ​

sql
\df has_*_privilege;
\df has_*_privilege;

1.数据库权限 ​

select has_database_privilege(user,database,privilege);
select has_database_privilege(database,privilege);

如：
taoism_translation=# select has_database_privilege('taoism_translation','create');
 has_database_privilege
------------------------
 t
(1 row)
select has_database_privilege(user,database,privilege);
select has_database_privilege(database,privilege);

如：
taoism_translation=# select has_database_privilege('taoism_translation','create');
 has_database_privilege
------------------------
 t
(1 row)

2.schema权限 ​

sql
select has_schema_privilege(user,schema,privilege);
select has_schema_privilege(schema,privilege);

taoism_translation=# \dn
      List of schemas
  Name  |       Owner
--------+-------------------
 public | pg_database_owner
(1 row)
select has_schema_privilege(user,schema,privilege);
select has_schema_privilege(schema,privilege);

taoism_translation=# \dn
      List of schemas
  Name  |       Owner
--------+-------------------
 public | pg_database_owner
(1 row)

3.表的权限 ​

has_table_privilege检查用户是否以特定方式访问表。如果使用文本字符串来声明所希望的权限类型，这个文本字符串必须是SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、TRIGGER。

select has_table_privilege(user,table,privilege);
select has_table_privilege(table,privilege);
select has_table_privilege(user,table,privilege);
select has_table_privilege(table,privilege);

4.2 系统权限、对象权限 ​

1.查看某用户的系统权限 ​

sql
SELECT * FROM  pg_roles WHERE rolname='postgres';
SELECT * FROM  pg_roles WHERE rolname='postgres';

2.查看某用户的表权限 ​

sql
select * from information_schema.table_privileges where grantee='postgres';
select * from information_schema.table_privileges where grantee='postgres';

3.查看某用户的usage权限 ​

sql
select * from information_schema.usage_privileges where grantee='postgres';
select * from information_schema.usage_privileges where grantee='postgres';

4.查看某用户在存储过程函数的执行权限 ​

sql
select * from information_schema.routine_privileges where grantee='postgres';
select * from information_schema.routine_privileges where grantee='postgres';

5.查看某用户在某表的列上的权限 ​

sql
select * from information_schema.column_privileges where grantee='postgres';
select * from information_schema.column_privileges where grantee='postgres';

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

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

7.查看用户自定义类型上授予的USAGE权限 ​

sql
select * from information_schema.udt_privileges where grantee='postgres';
select * from information_schema.udt_privileges where grantee='postgres';
  • 查看
sql
-- 查看当前模式（默认是 'public'）的权限分配
\dp

-- 或者使用 \z 命令，这通常会显示更详细的信息
\z
-- 查看当前模式（默认是 'public'）的权限分配
\dp

-- 或者使用 \z 命令，这通常会显示更详细的信息
\z

5.修改表拥有者 ​

ALTER TABLE your_table_name OWNER TO new_owner;
ALTER TABLE your_table_name OWNER TO new_owner;