什么是PostgreSQL用戶和角色

133次閱讀

共計(jì) 7629 個(gè)字符，預(yù)計(jì)需要花費(fèi) 20 分鐘才能閱讀完成。

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

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

1. 創(chuàng)建角色

語(yǔ)法：

1) 角色屬性：數(shù)據(jù)庫(kù)角色有一系列的屬性，這些屬性定義了他們的權(quán)限。

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

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

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

CREATE ROLE name SUPERUSER（只有本身是超級(jí)用戶才能執(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)限。一般加入組的用戶不會(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：限制用戶登錄次數(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 中用戶名不區(qū)分大小寫(xiě)
ERROR: role lanshan already exists

postgres=# CREATE USER lanshan1 WITH PASSWORD admin123 創(chuàng)建一個(gè)用戶，并設(shè)置密碼
CREATE ROLE
postgres=# CREATE ROLE lanshan2 WITH PASSWORD admin123 創(chuàng)建一個(gè)角色，并設(shè)置密碼
CREATE ROLE
postgres=# \q
[postgres@abc ~]$ psql -Ulanshan1 -dpostgres 用戶登錄到數(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 用戶和角色不能重名
ERROR: role lanshan2 already exists

二、創(chuàng)建用戶

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

語(yǔ)法：

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

方式 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 | {}

修改用戶密碼
postgres=# ALTER ROLE lss WITH PASSWORD admin123
ALTER ROLE
給用戶授權(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)系客戶端的 ident 服務(wù)器獲取客戶端的操作系統(tǒng)名，并且檢查它是否匹配被請(qǐng)求的數(shù)據(jù)庫(kù)用戶名。Ident 認(rèn)證只能在 TCIP/IP 連接上使用。

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

md5: 要求客戶端提供一個(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í)用戶忘記密碼怎辦？
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 名稱必須與當(dāng)前數(shù)據(jù)庫(kù)中現(xiàn)有 SCHEMA 的名稱不同。

SCHEMA 本質(zhì)上是一個(gè)名稱空間: 它包含命名對(duì)象(表、數(shù)據(jù)類(lèi)型、函數(shù)和操作符)，這些對(duì)象的名稱可以復(fù)制其他 SCHEMA 中存在的其他對(duì)象的名稱。通過(guò)將 SCHEA 名作為前綴“限定”它們的名稱，或者通過(guò)設(shè)置包含所需 SCHEMA 的搜索路徑來(lái)訪問(wèn)命名對(duì)象。指定非限定對(duì)象名稱的 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ì)象都將由該用戶擁有。

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 名稱，不能以 pg_開(kāi)頭，pg_是為系統(tǒng)用戶保留。

user_name：新創(chuàng)建的用戶將屬于新 SCHEMA。如果省略，則默認(rèn)為執(zhí)行命令的用戶。要?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)存在具有相同名稱的模式，則不創(chuàng)建。使用此選項(xiàng)時(shí)，無(wú)法包含 schema_element 子命令。

測(cè)試：

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

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

創(chuàng)建一個(gè)名為 test 的 SCHEMA，該 SCHEMA 將由用戶 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 名稱。