默认pogres的账号是没密码,可以对pogres设置密码
ALTER USER postgres WITH PASSWORD ‘123456’;
创建用户并添加密码u_test
create user u_test with password ‘123456’;
创建数据库,所有者是新创建的用户(数据库为dbtest)
create database dbtest owner u_test;
并授将新建的库权给u_test所有权限
grant all on database dbtest to u_test;
在dbtest下创建一个模式
create schema bj;
将模式bj授权给u_test的usage权限
grant usage on schema bj to u_test;
u_test在bj下拥有增删改查权限
grant select,insert,update,TRUNCATE,REFERENCES,TRIGGER on all tables in schema bj to u_test;
#修改默认权限为对bj模式下所有表增删改查权限
alter default privileges in schema bj grant insert,update,TRUNCATE,REFERENCES,TRIGGER on tables to u_test;
在bj模式下创建表权限及对表进行修改的权限:
grant create on schema bj to u_test;
#如果只想要查询,或者其中某个权限自行修改执行即可
grant select on all tables in schema bj to u_test;
grant delete on all tables in schema bj to u_test;
某个模式下的单张表授权
GRANT Select ON TABLE “bj”.“test_table” TO “u_test”;
查询用户的权限
select * from information_schema.table_privileges where grantee=‘u_test’;