用户&权限

创建某个库用户：

create user admin with password 'chengce243';

create database mydb with encoding='utf8' owner=admin;

GRANT ALL PRIVILEGES ON DATABASE mydb TO admin;

 

创建一个超级用户

create user dbaadmin superuser;

 

修改用户名

alter user tuser to testuser;

 

修改用户密码

alter user testuser password 'test';

或者：

alter user testuser with password 'chengce243';

 

修改用户为超级用户

alter user testuser superuser;

 

将超级用户修改为普通用户

alter user testuser nosuperuser;

 

锁定/解锁用户,不允许/允许其登录

alter user testuser nologin;

alter user testuser login;

 

修改数据库所属用户

alter database database_name OWNER TO new_user;

 

设置用户的连接数,其中0表示不允许登录,-1表示无限制

alter user test connection limit 10;

 

直接删除用户

drop user testuser;

 

如果用户在数据库中有相关对象,不能直接删除,需要将相关对象所属修改到其它用户中

test=# drop user testuser;

ERROR: role "testuser" cannot be dropped because some objects depend on it

DETAIL: owner of table zzz.kkk

privileges for schema zzz

 

将testuser的所属用户修改为test:

 

test=# reassign owned by testuser to test;

REASSIGN OWNED

还需要把权限进行收回,再进行删除:

 

test=# revoke all on schema zzz from testuser;

REVOKE

test=# drop user testuser;

DROP ROLE

 

创建一个schema,并且设置所属用户为 testuser:

create schema zzz authorization testuser;

 

删除schema,如果schema中存在对象,则需要使用cascade选项: 

test=# drop schema zzz;

ERROR: cannot drop schema zzz because other objects depend on it

DETAIL: table zzz.test depends on schema zzz

HINT: Use DROP ... CASCADE to drop the dependent objects too.

test=# drop schema zzz cascade;

NOTICE: drop cascades to table zzz.test

DROP SCHEMA