什么是PostgreSQL用戶和角色

本篇內容介紹了“什么是PostgreSQL用戶和角色”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

PostgreSQL使用角色的概念：管理數據庫訪問權限。根據角色自身的設置不同，一個角色可以看做是一個數據庫用戶，或者一組數據庫用戶。角色可以擁有數據庫對象(比如表)以及可以把這些對象上的權限賦予其它角色，以控制誰擁有訪問哪些對象的權限。

1. 創建角色

語法：

CREATE ROLE 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

 1) 角色屬性：數據庫角色有一系列的屬性，這些屬性定義了他們的權限。

• 登錄權限：只有具有LOGIN屬性的角色，才可以登陸（連接數據庫）。具有LOG IN屬性的角色實際上就是一個數據庫用戶。

•  CREATE ROLE name LOGIN：等價于 CREATE USER name

•  Superuser：數據庫超級用戶會繞過所有的權限檢查。具有一切權限。

•  CREATE ROLE name SUPERUSER（只有本身是超級用戶才能執行）

•  CREATEDB：創建數據庫。

•  REPLICATION：只能與LOGIN,同時指定。使用Replicate mode登錄數據庫或者創建刪除replicate slots，需要該權限。

•  CREATE ROLE name REPLICATION LOGIN.

 2) 角色關系：通常情況下會把很多角色分成同一組去管理。比如把權限授予一個組，或者回收一個組的權限。一般加入組的用戶不會授予LOGIN權限。

•  CREATE ROLE name

•  創建ROLE之后，就可以grant，revoke成員。

•  GRANT group_role TO role1, ... ;

•  REVOKE group_role FROM role1, ... ;

•  INHERIT：這些子句確定角色是否“繼承”其所屬角色的特權。一個具有INHERIT屬性的角色可以自動使用任何數據庫特權授予它直接或間接屬于的所有角色。沒有繼承，加入另一個角色只授予將角色設置為該另一個角色的能力;另一方的特權角色只有在完成此操作后才可用。如果未指定，則默認為INHERIT。

2. 其他選項

• BYPASSRLS:繞過所有的行級別安全策略。

• CONNECTION LIMIT connlimit：限制用戶登錄次數，默認值-1（不限制）。

• [ ENCRYPTED ] PASSWORD 'password'：設置登錄密碼。ENCRYPTED（默認省略，為向后兼容），加密方式是由配置文件決定。

•  VALID UNTIL 'timestamp'：指定密碼有效期。

•  IN ROLE：把當前創建的role加入已經指定的role組當中。注：不能作為administer加入，只能單獨使用grant的方式。

•  ROLE role_name：把role_name作為一個成員，加入當前創建的role中（即當前role作為group）。

• ADMIN role_name:在ROLE role_name的基礎上，賦予role_name grant membership的權限。類似于oracle的級聯權限。

測試：

[postgres@abc ~]$ psql
Password for user postgres:
psql (11.2)
Type "help" for help.
postgres=# CREATE ROLE LANSHAN LOGIN;  創建一個角色
CREATE ROLE
postgres=# CREATE USER lanshan WITH PASSWORD 'admin123';  可以看出pg中用戶名不區分大小寫
ERROR:  role "lanshan" already exists

postgres=# CREATE USER lanshan1 WITH PASSWORD 'admin123'; 創建一個用戶，并設置密碼
CREATE ROLE
postgres=# CREATE ROLE lanshan2 WITH PASSWORD 'admin123'; 創建一個角色，并設置密碼
CREATE ROLE
postgres=# \q
[postgres@abc ~]$ psql -Ulanshan1  -dpostgres  用戶登錄到數據庫
Password for user lanshan1:
psql (11.2)
Type "help" for help.

postgres=> \q
[postgres@abc ~]$ psql -Ulanshan2 -dpostgres  lanshan2是一個角色，無法登錄。
Password for user lanshan2:
psql: FATAL:  role "lanshan2" is not permitted to log in

postgres=# CREATE USER lanshan2 WITH PASSWORD 'admin123';  用戶和角色不能重名
ERROR:  role "lanshan2" already exists

二、創建用戶

其實用戶和角色都是角色，只是用戶是具有登錄權限的角色。

語法：

CREATE USER name [ [ WITH ] option [ ... ] ]
where option can be:
 SUPERUSER | NOSUPERUSER  ---dba用戶/普通用戶
 | CREATEDB | NOCREATEDB  ---創建db的權限
 | CREATEROLE | NOCREATEROLE ---創建role
 | 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

測試創建用戶：

方式1: 在系統命令行中使用createuser命令中創建
Create user username
方式2: 在PostgresSQL命令行中使用CREATE ROLE指令創建
CREATE ROLE rolename;

[postgres@abc ~]$ createuser lss
[postgres@abc ~]$ psql
psql (11.2)
Type "help" for help.
postgres-# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 lss       |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
授權
postgres=# alter role lss Createrole CREATEDB;
ALTER ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 lss       | Superuser, Create role, Create DB                          | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
授予多個權限用空格隔開
postgres=# alter role lss REPLICATION BYPASSRLS;
ALTER ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 lss       | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

修改用戶密碼
postgres=# ALTER ROLE lss WITH PASSWORD 'admin123';
ALTER ROLE
給用戶授權
pgtest=# GRANT ALL PRIVILEGES ON DATABASE "postgres" to lss;
GRANT
登錄：
[postgres@abc ~]$ psql -U lss -d pgtest -W
Password:
psql: FATAL:  Peer authentication failed for user "lss"
登陸失敗
[postgres@abc data]$ cat pg_hba.conf  ---該配置文件指定認證方式
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only 操作系統直接登錄
local    all             all                                     md5
#local   all             all                                     peer
# IPv4 local connections: 指定IP地址登錄
#host    all             all             127.0.0.1/32            ident
host     all             all             127.0.0.1/32            md5
# IPv6 local connections: 指定IP地址登錄
host     all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.  復制權限，認證方式
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            ident
host    replication     all             ::1/128                 ident

ident：通過聯系客戶端的 ident 服務器獲取客戶端的操作系統名，并且檢查它是否匹配被請求的數據庫用戶名。Ident 認證只能在 TCIP/IP 連接上使用。

Peer：從操作系統獲得客戶端的操作系統用戶，并且檢查它是否匹配被請求的數據庫用戶名。這只對本地連接可用。

md5:要求客戶端提供一個雙重 MD5 加密的口令進行認證。

Trust:不需要認證

把操作系統認證登錄的認證方式改為MD5
local    all             all                                     md5
[postgres@abc data]$ pg_ctl reload    ----重新加載配置文件
[postgres@abc data]$ psql -Ulss  -dpgtest
Password for user lss:
psql (11.2)
Type "help" for help.
pgtest=#

如果超級用戶忘記密碼怎辦？
vi pg_hba.conf
local    all             all                                     trust
[postgres@abc data]$ pg_ctl reload
[postgres@abc data]$ psql
psql (11.2)
Type "help" for help.
postgres=# alter user postgres WITH PASSWORD 'admin123';
ALTER ROLE
postgres=# exit
改完密碼之后，把認證方式改為md5,重新加載配置。
[postgres@abc data]$ psql
Password for user postgres:
psql: FATAL:  password authentication failed for user "postgres"
[postgres@abc data]$ psql
Password for user postgres:
psql (11.2)
Type "help" for help.
postgres=#

三、SCHEMA

CREATE SCHEMA在當前數據庫中創建一個新SCHEMA。SCHEMA名稱必須與當前數據庫中現有SCHEMA的名稱不同。

SCHEMA本質上是一個名稱空間:它包含命名對象(表、數據類型、函數和操作符)，這些對象的名稱可以復制其他SCHEMA中存在的其他對象的名稱。通過將SCHEA名作為前綴“限定”它們的名稱，或者通過設置包含所需SCHEMA的搜索路徑來訪問命名對象。指定非限定對象名稱的CREATE命令創建當前模式中的對象(搜索路徑前面的對象，可以使用函數current_schema確定)。

CREATE SCHEMA還可以包含子命令，用于在新SCHEMA中創建對象。子命令本質上與創建SCHEMA后發出的單獨命令相同，但如果使用了授權子句，則創建的所有對象都將由該用戶擁有。

1. 創建schema

語法：

CREATE SCHEMA schema_name [ AUTHORIZATION role_specification ]
 [ schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION role_specification [ schema_element
 [ ... ] ]
CREATE SCHEMA IF NOT EXISTS schema_name
 [ AUTHORIZATION role_specification ]
CREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_specification
where role_specification can be:
 user_name
 | CURRENT_USER
 | SESSION_USER

2. 參數

•  schema_name:schema名稱，不能以pg_開頭，pg_是為系統用戶保留。

•  user_name：新創建的用戶將屬于新SCHEMA。如果省略，則默認為執行命令的用戶。要創建由另一個role擁有的SCHEMA，您必須是該role的直接或間接成員，或者是superuser。

•  schema_element：定義要在模式中創建的對象的SQL語句。目前,只有創建表，創建視圖，創建索引，創建序列，創建觸發器和GRANT被接受為CREATE SCHEMA中的子句。其他類型的對象可以在模式創建之后在單獨的命令中創建。

•  IF NOT EXISTS：如果已經存在具有相同名稱的模式，則不創建 。使用此選項時，無法包含schema_element子命令。
    
  測試：

創建一個schema:
postgres=# CREATE SCHEMA myschema;
CREATE SCHEMA

為一個用戶創建schema:
postgres=# CREATE SCHEMA AUTHORIZATION joe;(這個用戶或者role必須存在)
ERROR:  role "joe" does not exist
postgres=# CREATE SCHEMA AUTHORIZATION lss;
CREATE SCHEMA

創建一個名為test的SCHEMA，該SCHEMA將由用戶lss擁有，除非已經有一個名為test的模式。(lss是否擁有已經存在的SCHEMA并不重要。)
postgres=# CREATE SCHEMA IF NOT EXISTS test AUTHORIZATION lss;
CREATE SCHEMA

創建一個schema,不指定以分號結尾，默認為子命令：
postgres=# CREATE SCHEMA hollywood
postgres-#  CREATE TABLE films (title text, release date, awards text[])
postgres-#  CREATE VIEW winners AS
postgres-#  SELECT title, release FROM films WHERE awards IS NOT NULL;
CREATE SCHEMA
等價于：
先刪除創建的對象：
ERROR:  cannot drop table hollywood.films because other objects depend on it
DETAIL:  view hollywood.winners depends on table hollywood.films
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
postgres=# DROP TABLE hollywood.films cascade;
NOTICE:  drop cascades to view hollywood.winners
DROP TABLE
刪除schema
postgres=# drop schema hollywood cascade;
DROP SCHEMA
可以看出跟oracle的命令還是很像的。
postgres=# CREATE SCHEMA hollywood;
CREATE SCHEMA
postgres=# CREATE TABLE hollywood.films (title text, release date, awards  text[]);
CREATE TABLE
postgres=# CREATE VIEW hollywood.winners AS  SELECT title, release FROM hollywood.films WHERE awards IS NOT NULL;
CREATE VIEW
加了分號之后，就不是子命令，必須指定schema名稱。

“什么是PostgreSQL用戶和角色”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！