pgsql创建用户并赋予权限

• 使用 CREATE DATABASE SQL 语句来创建

  CREATE DATABASE testdb;
  

• 使用超级用户登录数据库 -> 然后执行 ->创建用户及密码 ->设置备注

  CREATE USER testuser WITH PASSWORD '123456';
  comment on role testuser is '测试'; 
  

• 创建scheme被赋予权限 ->在数据库testdb session下创建schema

  create schema testschema;
  

• 赋予用户对应的schema权限和表权限

  ALTER DEFAULT PRIVILEGES IN SCHEMA testschema GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON TABLES TO testuser ;
  ALTER DEFAULT PRIVILEGES IN SCHEMA testschema GRANT EXECUTE ON FUNCTIONS TO testuser ;
  ALTER DEFAULT PRIVILEGES IN SCHEMA testschema GRANT ALL ON SEQUENCES TO testuser ;
  ALTER DEFAULT PRIVILEGES GRANT USAGE ON SCHEMAS TO testuser ;
  
  GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA testschema  TO testuser ;
  GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA testschema TO testuser ;
  GRANT ALL ON ALL SEQUENCES IN SCHEMA testschema TO testuser ;
  GRANT USAGE,CREATE ON SCHEMA testschema TO testuser ;
  
  GRANT TEMPORARY ON DATABASE testdb TO testuser ;
  

更多干货地址：https://blog.csdn.net/wangxudongx