005-创建用户并授权表访问权限

创建用户 ttom，授权权限的数据库 factory。

• 创建用户
  create user ttom with password 'aaa';

• 授予用户数据库权限
  grant all privileges on database factory to ttom;

• 授予用户表所有权限
  grant all privileges on all tables in schema public to ttom;

• 授予用户表查看权限
  grant select on all tables in schema public to ttom;

• 单独授予某个表查看权限
  grant select on table mytable to ttom;

---

• 取消某个表的查看权限
  revoke select on mytable from ttom;

---

完整创建用户到授权

-- 创建用户
create user user1 with password '123';

-- 授予连接权限
grant connect on database factory to user1;

-- 取消新用户查看系统表的权限
revoke all on pg_class from public;
revoke all on pg_tablespace from public;
revoke all on pg_user from public;
revoke all on pg_roles from public;

-- 赋予 schema 使用权限
grant usage on schema public to user1;

-- 授予查看表权限
grant select on all tables in schema public to user1;

-- 重新赋予 schema 权限
grant all on pg_class to public;
grant all on pg_tablespace to public;
grant all on pg_user to public;
grant all on pg_roles to public;


-- 删除用户需先清空权限
revoke all on database schema_01 from user1;
revoke all on mytable from user1;
-- 将权限转移到其他用户
alter table mytable owner to userx;
drop user user1;