在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: # 创建用户 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 | {}
