PostgreSQL用户管理专题

2022-11-01  本文已影响0人  这货不是王马勺

用户创建、修改及删除

可以使用create user或create role,区别仅在于user默认带有连接权限(login权限)而role没有;
例:

create user 'wenjie.wang' with SUPERUSER PASSWORD '123456';
create user repl REPLICATION LOGIN ENCRYPTED PASSWORD 'repl';
create user test with PASSWORD '123456' VALID UNTIL '2022-12-31';

ENCRYPTED PASSWORD和PASSWORD功能相同。
删除用户:

drop user test;

修改:

alter user admin with password '1234';

查看alter user命令说明:

\h alter user
\help alter user

查看有哪些用户:

\du

权限管理

权限级别

权限设置
database权限设置

grant create on database test1 to 'wenjie.wang';

schema权限

alter schema abc owner to abc;
grant select,insert,update ,delete on all tables in schema abc to abc;
grant all on all tables in schema abc to abc;

如果不建立自定义schema默认会存到public。

object 权限

grant select,insert,update ,delete on a.t1 to u;

例:

create database taobao;
\c taobao
create schema miaosha;
create user miaosha with password '123';
alter schema miaosha owner to miaosha;
grant select,insert,update,delete on all tables in schema miaosha to miaosha;

回收权限:

revoke select,insert,update,delete on all tables in schema miaosha from miaosha;
上一篇 下一篇

猜你喜欢

热点阅读