快来学PostgreSQL用户角色与权限

## 1. 系统命令行创建
createuser username with option xxx;


## 2. 在psql命令行创建用户
create user username with option xxx;
option可以是：
 SUPERUSER | NOSUPERUSER
 | CREATEDB | NOCREATEDB
 | CREATEROLE | NOCREATEROLE
 | CREATEUSER | NOCREATEUSER
 | INHERIT | NOINHERIT #角色是其他角色的成员，这些子句决定新角色是否从那些角色中“继承”特权
 | LOGIN | NOLOGIN
 | REPLICATION | NOREPLICATION
 | BYPASSRLS | NOBYPASSRLS #决定是否一个角色可以绕过每一条行级安全性（RLS）策略。
 | CONNECTION LIMIT connlimit
 | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | VALID UNTIL 'timestamp' | IN ROLE role_name [, ...]
 | IN GROUP role_name [, ...]
 | ROLE role_name [, ...]
 | ADMIN role_name [, ...] #ADMIN子句与ROLE相似，让它们能够把这个角色中的成员关系授予给其他人。
 | USER role_name [, ...]
 | SYSID uid


## 在psql命令行创建角色
create role rolename with option xxx;
option可以是：
 SUPERUSER | NOSUPERUSER
 | CREATEDB | NOCREATEDB
 | CREATEROLE | NOCREATEROLE
 | CREATEUSER | NOCREATEUSER
 | INHERIT | NOINHERIT
 | LOGIN | NOLOGIN
 | REPLICATION | NOREPLICATION
 | CONNECTION LIMIT connlimit
 | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | VALID UNTIL 'timestamp' | IN ROLE role_name [, ...]
 | IN GROUP role_name [, ...]
 | ROLE role_name [, ...]
 | ADMIN role_name [, ...]
 | USER role_name [, ...]
 | SYSID uid

## 显示用户和角色属性
\du 


postgres=# \d pg_roles
                         View "pg_catalog.pg_roles"
     Column     |           Type           | Collation | Nullable | Default
----------------+--------------------------+-----------+----------+---------
 rolname        | name                     |           |          |
 rolsuper       | boolean                  |           |          |
 rolinherit     | boolean                  |           |          |
 rolcreaterole  | boolean                  |           |          |
 rolcreatedb    | boolean                  |           |          |
 rolcanlogin    | boolean                  |           |          |
 rolreplication | boolean                  |           |          |
 rolconnlimit   | integer                  |           |          |
 rolpassword    | text                     |           |          |
 rolvaliduntil  | timestamp with time zone |           |          |
 rolbypassrls   | boolean                  |           |          |
 rolconfig      | text[]                   | C         |          |
 oid            | oid                      |           |          |


postgres=# \d pg_user
                        View "pg_catalog.pg_user"
    Column    |           Type           | Collation | Nullable | Default
--------------+--------------------------+-----------+----------+---------
 usename      | name                     |           |          |
 usesysid     | oid                      |           |          |
 usecreatedb  | boolean                  |           |          |
 usesuper     | boolean                  |           |          |
 userepl      | boolean                  |           |          |
 usebypassrls | boolean                  |           |          |
 passwd       | text                     |           |          |
 valuntil     | timestamp with time zone |           |          |
 useconfig    | text[]                   | C         |          |

## 修改用户、角色属性
alter role username with option;
## 重命名
alter user oldname rename to newname;
## 设置用户参数
alter user username set parametername to value;
## 重置配置参数
alter user username reset parametername  all;

## 1. 系统命令行删除
dropuser username


## 2.在psql命令行删除
drop user username;
drop role rolename;
drop role if exists role_name;


## 注意：
## 1、只用超级用户能够删除超级用户
## 2、只有具有createrole权限的用户能删除非超级用户
## 3、删除用户前，需要先删除依赖该用户的对象、权限等信息
## 4、任何属于该组角色的对象都必须先被删除或者将对象的所有者赋予其它角色，任何赋予该组角色的权限也都必须被撤消。
## 5、删除组role只会删除组的role本身，组的成员并不会被删除

## 配置pg_hba.conf限制用户登录
cat $PGDATA/pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD


# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

revoke connect on database dbname from public;

grant usage on schema schemaname to username;
grant create on schema schemaname to username;
grant all on schema schemaname to username;

## 授予增删改权限
grant insert,update,delete on table tablename to username;


## 授予单个表上的所有权限
grant all on table tablename to username;


## 授权schema上所有表的所有权限
grant all on all tables in schema schemaname to username;

postgres=# \h grant
Command:     GRANT
Description: define access privileges
Syntax:
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]


GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
    [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
    ON [ TABLE ] table_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]


GRANT { { USAGE | SELECT | UPDATE }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { SEQUENCE sequence_name [, ...]
         | ALL SEQUENCES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]


GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
    ON DATABASE database_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]


GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON DOMAIN domain_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]


GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN DATA WRAPPER fdw_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]


GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN SERVER server_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]


GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
    ON LARGE OBJECT loid [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]


GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
    ON SCHEMA schema_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]


GRANT { CREATE | ALL [ PRIVILEGES ] }
    ON TABLESPACE tablespace_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]


GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON TYPE type_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]


GRANT role_name [, ...] TO role_specification [, ...]
    [ WITH ADMIN OPTION ]
    [ GRANTED BY role_specification ]


where role_specification can be:


    [ GROUP ] role_name
  | PUBLIC
  | CURRENT_ROLE
  | CURRENT_USER
  | SESSION_USER