什么是PostgreSQL用戶(hù)和角色

本篇內(nèi)容介紹了“什么是PostgreSQL用戶(hù)和角色”的有關(guān)知識(shí)，在實(shí)際案例的操作過(guò)程中，不少人都會(huì)遇到這樣的困境，接下來(lái)就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

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

1. 創(chuàng)建角色

語(yǔ)法：

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) 角色屬性：數(shù)據(jù)庫(kù)角色有一系列的屬性，這些屬性定義了他們的權(quán)限。

• 登錄權(quán)限：只有具有LOGIN屬性的角色，才可以登陸（連接數(shù)據(jù)庫(kù)）。具有LOG IN屬性的角色實(shí)際上就是一個(gè)數(shù)據(jù)庫(kù)用戶(hù)。

•  CREATE ROLE name LOGIN：等價(jià)于 CREATE USER name

•  Superuser：數(shù)據(jù)庫(kù)超級(jí)用戶(hù)會(huì)繞過(guò)所有的權(quán)限檢查。具有一切權(quán)限。

•  CREATE ROLE name SUPERUSER（只有本身是超級(jí)用戶(hù)才能執(zhí)行）

•  CREATEDB：創(chuàng)建數(shù)據(jù)庫(kù)。

•  REPLICATION：只能與LOGIN,同時(shí)指定。使用Replicate mode登錄數(shù)據(jù)庫(kù)或者創(chuàng)建刪除replicate slots，需要該權(quán)限。

•  CREATE ROLE name REPLICATION LOGIN.

 2) 角色關(guān)系：通常情況下會(huì)把很多角色分成同一組去管理。比如把權(quán)限授予一個(gè)組，或者回收一個(gè)組的權(quán)限。一般加入組的用戶(hù)不會(huì)授予LOGIN權(quán)限。

•  CREATE ROLE name

•  創(chuàng)建ROLE之后，就可以grant，revoke成員。

•  GRANT group_role TO role1, ... ;

•  REVOKE group_role FROM role1, ... ;

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

2. 其他選項(xiàng)

• BYPASSRLS:繞過(guò)所有的行級(jí)別安全策略。

• CONNECTION LIMIT connlimit：限制用戶(hù)登錄次數(shù)，默認(rèn)值-1（不限制）。

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

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

•  IN ROLE：把當(dāng)前創(chuàng)建的role加入已經(jīng)指定的role組當(dāng)中。注：不能作為administer加入，只能單獨(dú)使用grant的方式。

•  ROLE role_name：把role_name作為一個(gè)成員，加入當(dāng)前創(chuàng)建的role中（即當(dāng)前role作為group）。

• ADMIN role_name:在ROLE role_name的基礎(chǔ)上，賦予role_name grant membership的權(quán)限。類(lèi)似于oracle的級(jí)聯(lián)權(quán)限。

測(cè)試：

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

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

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

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

二、創(chuàng)建用戶(hù)

其實(shí)用戶(hù)和角色都是角色，只是用戶(hù)是具有登錄權(quán)限的角色。

語(yǔ)法：

CREATE USER name [ [ WITH ] option [ ... ] ]
where option can be:
 SUPERUSER | NOSUPERUSER  ---dba用戶(hù)/普通用戶(hù)
 | CREATEDB | NOCREATEDB  ---創(chuàng)建db的權(quán)限
 | CREATEROLE | NOCREATEROLE ---創(chuàng)建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

測(cè)試創(chuàng)建用戶(hù)：

方式1: 在系統(tǒng)命令行中使用createuser命令中創(chuàng)建
Create user username
方式2: 在PostgresSQL命令行中使用CREATE ROLE指令創(chuàng)建
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 | {}
授權(quán)
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 | {}
授予多個(gè)權(quán)限用空格隔開(kāi)
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 | {}

修改用戶(hù)密碼
postgres=# ALTER ROLE lss WITH PASSWORD 'admin123';
ALTER ROLE
給用戶(hù)授權(quán)
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  ---該配置文件指定認(rèn)證方式
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only 操作系統(tǒng)直接登錄
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.  復(fù)制權(quán)限，認(rèn)證方式
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            ident
host    replication     all             ::1/128                 ident

ident：通過(guò)聯(lián)系客戶(hù)端的 ident 服務(wù)器獲取客戶(hù)端的操作系統(tǒng)名，并且檢查它是否匹配被請(qǐng)求的數(shù)據(jù)庫(kù)用戶(hù)名。Ident 認(rèn)證只能在 TCIP/IP 連接上使用。

Peer：從操作系統(tǒng)獲得客戶(hù)端的操作系統(tǒng)用戶(hù)，并且檢查它是否匹配被請(qǐng)求的數(shù)據(jù)庫(kù)用戶(hù)名。這只對(duì)本地連接可用。

md5:要求客戶(hù)端提供一個(gè)雙重 MD5 加密的口令進(jìn)行認(rèn)證。

Trust:不需要認(rèn)證

把操作系統(tǒng)認(rèn)證登錄的認(rèn)證方式改為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=#

如果超級(jí)用戶(hù)忘記密碼怎辦？
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
改完密碼之后，把認(rèn)證方式改為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在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)新SCHEMA。SCHEMA名稱(chēng)必須與當(dāng)前數(shù)據(jù)庫(kù)中現(xiàn)有SCHEMA的名稱(chēng)不同。

SCHEMA本質(zhì)上是一個(gè)名稱(chēng)空間:它包含命名對(duì)象(表、數(shù)據(jù)類(lèi)型、函數(shù)和操作符)，這些對(duì)象的名稱(chēng)可以復(fù)制其他SCHEMA中存在的其他對(duì)象的名稱(chēng)。通過(guò)將SCHEA名作為前綴“限定”它們的名稱(chēng)，或者通過(guò)設(shè)置包含所需SCHEMA的搜索路徑來(lái)訪問(wèn)命名對(duì)象。指定非限定對(duì)象名稱(chēng)的CREATE命令創(chuàng)建當(dāng)前模式中的對(duì)象(搜索路徑前面的對(duì)象，可以使用函數(shù)current_schema確定)。

CREATE SCHEMA還可以包含子命令，用于在新SCHEMA中創(chuàng)建對(duì)象。子命令本質(zhì)上與創(chuàng)建SCHEMA后發(fā)出的單獨(dú)命令相同，但如果使用了授權(quán)子句，則創(chuàng)建的所有對(duì)象都將由該用戶(hù)擁有。

1. 創(chuàng)建schema

語(yǔ)法：

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. 參數(shù)

•  schema_name:schema名稱(chēng)，不能以pg_開(kāi)頭，pg_是為系統(tǒng)用戶(hù)保留。

•  user_name：新創(chuàng)建的用戶(hù)將屬于新SCHEMA。如果省略，則默認(rèn)為執(zhí)行命令的用戶(hù)。要?jiǎng)?chuàng)建由另一個(gè)role擁有的SCHEMA，您必須是該role的直接或間接成員，或者是superuser。

•  schema_element：定義要在模式中創(chuàng)建的對(duì)象的SQL語(yǔ)句。目前,只有創(chuàng)建表，創(chuàng)建視圖，創(chuàng)建索引，創(chuàng)建序列，創(chuàng)建觸發(fā)器和GRANT被接受為CREATE SCHEMA中的子句。其他類(lèi)型的對(duì)象可以在模式創(chuàng)建之后在單獨(dú)的命令中創(chuàng)建。

•  IF NOT EXISTS：如果已經(jīng)存在具有相同名稱(chēng)的模式，則不創(chuàng)建 。使用此選項(xiàng)時(shí)，無(wú)法包含schema_element子命令。
    
  測(cè)試：

創(chuàng)建一個(gè)schema:
postgres=# CREATE SCHEMA myschema;
CREATE SCHEMA

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

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

創(chuàng)建一個(gè)schema,不指定以分號(hào)結(jié)尾，默認(rèn)為子命令：
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
等價(jià)于：
先刪除創(chuàng)建的對(duì)象：
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
加了分號(hào)之后，就不是子命令，必須指定schema名稱(chēng)。

“什么是PostgreSQL用戶(hù)和角色”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注億速云網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！