在PostgreSQL中，角色与用户是没有区别的，一个用户也是一个角色。
用户和角色在整个数据库实例中都是全局的，在同一个实例中的不同数据库中，看到的也是相同的。
## 使用pgAdmin工具 
## 展开 
在初始化数据库系统时，有一个预定义的超级用户：postgres；一般来说，初始化数据库使用的操作系统用户：postgres
USER 与 ROLE 的区别：
CREATE ROLE name [[with] option [...]] 创建的角色默认没有 LOGIN 权限
CREATE USER name [[with] option [...]] 创建的用户默认有 LOGIN 权限，除了这点，与 CREATE ROLE 完全相同。
# 查看 create user 语法帮助
postgres=# \h create user
Command: CREATE USER
Description: define a new database role
Syntax:
CREATE USER name [ [ WITH ] option [ ... ] ]
where option can be:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| BYPASSRLS | NOBYPASSRLS
| CONNECTION LIMIT connlimit
| [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
| VALID UNTIL 'timestamp'
| IN ROLE role_name [, ...]
| IN GROUP role_name [, ...]
| ROLE role_name [, ...]
| ADMIN role_name [, ...]
| USER role_name [, ...]
| SYSID uid
URL: https://www.postgresql.org/docs/12/sql-createuser.html
# 创建用户 sys ，密码为 sys ，且具有superuser、createdb、createrole 权限
postgres=# create user sys with password 'sys' superuser createdb createrole;
CREATE ROLE
#创建普通用户 scott ，密码为 123
postgres=# create user scott with password '123';
CREATE ROLE
# 查看用户
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
scott | | {}
sys | Superuser, Create role, Create DB | {}
# 查看用户：使用系统视图 pg_user
postgres=# select usename,usesysid,usecreatedb,usesuper from pg_user order by 1;
usename | usesysid | usecreatedb | usesuper
----------+----------+-------------+----------
postgres | 10 | t | t
scott | 16386 | f | f
sys | 16385 | t | t
(3 rows)
# 修改密码 with 可省略
postgres=# alter user scott with password 'tiger';
ALTER ROLE
或
postgres=# \password scott
Enter new password:
Enter it again:
# 除了默认的超级用户postgres，其他用户连接库，必须加 -d postgres
[postgres@pgdb1 ~]$ psql -h pgdb1 -p 5432 -U scott
Password for user scott:
psql: error: FATAL: database "scott" does not exist
##
[postgres@pgdb1 ~]$ psql -U sys
psql: error: FATAL: database "sys" does not exist
##
[postgres@pgdb1 ~]$ psql -U postgres
psql (12.8)
Type "help" for help.
# 创建密码有效期的用户
postgres=# create user user_10day password '123' valid until '2022-03-05';
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
------------+------------------------------------------------------------+-----------
hr | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
scott | Cannot login | {}
sys | Superuser, Create role, Create DB | {}
user_10day | Password valid until 2022-03-05 00:00:00+08 | {}
