PostgreSQL 角色與用戶管理介紹
一、角色與用戶的區(qū)別
角色就相當(dāng)于崗位：角色可以是經(jīng)理，助理。
用戶就是具體的人：比如陳XX經(jīng)理，朱XX助理，王XX助理。
在PostgreSQL 里沒(méi)有區(qū)分用戶和角色的概念，"CREATE USER" 為 "CREATE ROLE" 的別名，這兩個(gè)命令幾乎是完全相同的，唯一的區(qū)別是"CREATE USER" 命令創(chuàng)建的用戶默認(rèn)帶有LOGIN屬性，而"CREATE ROLE" 命令創(chuàng)建的用戶默認(rèn)不帶LOGIN屬性(CREATE USER is equivalent to CREATE ROLE except that CREATE USER assumes LOGIN by default, while CREATE ROLE does not)。
1.1 創(chuàng)建角色與用戶
CREATE ROLE 語(yǔ)法
CREATE ROLE name [ [ WITH ] option [ ... ] ]
where option can be:
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
創(chuàng)建david 角色和sandy 用戶
postgres=# CREATE ROLE david; //默認(rèn)不帶LOGIN屬性
CREATE ROLE
postgres=# CREATE USER sandy; //默認(rèn)具有LOGIN屬性
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
david | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication | {}
sandy | | {}
postgres=#
postgres=# SELECT rolname from pg_roles ;
rolname
----------
postgres
david
sandy
(3 rows)
postgres=# SELECT usename from pg_user; //角色david 創(chuàng)建時(shí)沒(méi)有分配login權(quán)限，所以沒(méi)有創(chuàng)建用戶
usename
----------
postgres
sandy
(2 rows)
postgres=#
1.2 驗(yàn)證LOGIN屬性
postgres@CS-DEV:~> psql -U david
psql: FATAL: role "david" is not permitted to log in
postgres@CS-DEV:~> psql -U sandy
psql: FATAL: database "sandy" does not exist
postgres@CS-DEV:~> psql -U sandy -d postgres
psql (9.1.0)
Type "help" for help.
postgres=> \dt
No relations found.
postgres=>
用戶sandy 可以登錄，角色david 不可以登錄。
1.3 修改david 的權(quán)限，增加LOGIN權(quán)限
postgres=# ALTER ROLE david LOGIN ;
ALTER ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
david | | {}
postgres | Superuser, Create role, Create DB, Replication | {}
sandy | | {}
postgres=# SELECT rolname from pg_roles ;
rolname
----------
postgres
sandy
david
(3 rows)
postgres=# SELECT usename from pg_user; //給david 角色分配login權(quán)限，系統(tǒng)將自動(dòng)創(chuàng)建同名用戶david
usename
----------
postgres
sandy
david
(3 rows)
postgres=#
1.4 再次驗(yàn)證LOGIN屬性
postgres@CS-DEV:~> psql -U david -d postgres
psql (9.1.0)
Type "help" for help.
postgres=> \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
david | | {}
postgres | Superuser, Create role, Create DB, Replication | {}
sandy | | {}
postgres=>
david 現(xiàn)在也可以登錄了。
二、查看角色信息
psql 終端可以用\du 或\du+ 查看，也可以查看系統(tǒng)表 select * from pg_roles;
postgres=> \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
david | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication | {}
sandy | | {}
postgres=> \du+
List of roles
Role name | Attributes | Member of | Description
-----------+------------------------------------------------+-----------+-------------
david | Cannot login | {} |
postgres | Superuser, Create role, Create DB, Replication | {} |
sandy | | {} |
postgres=> SELECT * from pg_roles;
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolconfig | oid
----------+----------+------------+---------------+-------------+--------------+-------------+----------------+--------------+-------------+---------------+-----------+-------
postgres | t | t | t | t | t | t | t | -1 | ******** | | | 10
david | f | t | f | f | f | f | f | -1 | ******** | | | 49438
sandy | f | t | f | f | f | t | f | -1 | ******** | | | 49439
(3 rows)
postgres=>
三、角色屬性（Role Attributes）
一個(gè)數(shù)據(jù)庫(kù)角色可以有一系列屬性，這些屬性定義了他的權(quán)限。
| 屬性 | 說(shuō)明 |
| login | 只有具有 LOGIN 屬性的角色可以用做數(shù)據(jù)庫(kù)連接的初始角色名。 |
| superuser | 數(shù)據(jù)庫(kù)超級(jí)用戶 |
| createdb | 創(chuàng)建數(shù)據(jù)庫(kù)權(quán)限 |
| createrole | 允許其創(chuàng)建或刪除其他普通的用戶角色(超級(jí)用戶除外) |
| replication | 做流復(fù)制的時(shí)候用到的一個(gè)用戶屬性，一般單獨(dú)設(shè)定。 |
| password | 在登錄時(shí)要求指定密碼時(shí)才會(huì)起作用，比如md5或者password模式，跟客戶端的連接認(rèn)證方式有關(guān) |
| inherit | 用戶組對(duì)組員的一個(gè)繼承標(biāo)志，成員可以繼承用戶組的權(quán)限特性 |
| ... | ... |
四、創(chuàng)建用戶時(shí)賦予角色屬性
從pg_roles 表里查看到的信息，在上面創(chuàng)建的david 用戶時(shí)，默認(rèn)沒(méi)有創(chuàng)建數(shù)據(jù)庫(kù)等權(quán)限。
postgres@CS-DEV:~> psql -U david -d postgres
psql (9.1.0)
Type "help" for help.
postgres=> \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
david | | {}
postgres | Superuser, Create role, Create DB, Replication | {}
sandy | | {}
postgres=> CREATE DATABASE test;
ERROR: permission denied to create database
postgres=>
如果要在創(chuàng)建角色時(shí)就賦予角色一些屬性，可以使用下面的方法。
首先切換到postgres 用戶。
4.1 創(chuàng)建角色bella 并賦予其CREATEDB 的權(quán)限。
postgres=# CREATE ROLE bella CREATEDB ;
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
bella | Create DB, Cannot login | {}
david | | {}
postgres | Superuser, Create role, Create DB, Replication | {}
sandy | | {}
postgres=#
4.2 創(chuàng)建角色renee 并賦予其創(chuàng)建數(shù)據(jù)庫(kù)及帶有密碼登錄的屬性。
postgres=# CREATE ROLE renee CREATEDB PASSWORD 'abc123' LOGIN;
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
bella | Create DB, Cannot login | {}
david | | {}
postgres | Superuser, Create role, Create DB, Replication | {}
renee | Create DB | {}
sandy | | {}
postgres=#
4.3 測(cè)試renee 角色
a. 登錄
postgres@CS-DEV:~> psql -U renee -d postgres
psql (9.1.0)
Type "help" for help.
postgres=>
用renee 用戶登錄數(shù)據(jù)庫(kù)，發(fā)現(xiàn)不需要輸入密碼既可登錄，不符合實(shí)際情況。
b. 查找原因
在角色屬性中關(guān)于password的說(shuō)明，在登錄時(shí)要求指定密碼時(shí)才會(huì)起作用，比如md5或者password模式，跟客戶端的連接認(rèn)證方式有關(guān)。
查看pg_hba.conf 文件，發(fā)現(xiàn)local 的METHOD 為trust，所以不需要輸入密碼。
將local 的METHOD 更改為password，然后保存重啟postgresql。
c. 再次驗(yàn)證
提示輸入密碼，輸入正確密碼后進(jìn)入到數(shù)據(jù)庫(kù)。
d. 測(cè)試創(chuàng)建數(shù)據(jù)庫(kù)
創(chuàng)建成功。
五、給已存在用戶賦予各種權(quán)限
使用ALTER ROLE 命令。
ALTER ROLE 語(yǔ)法：
ALTER ROLE name [ [ WITH ] option [ ... ] ]
where option can be:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| CREATEUSER | NOCREATEUSER
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| CONNECTION LIMIT connlimit
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
| VALID UNTIL 'timestamp'
ALTER ROLE name RENAME TO new_name
ALTER ROLE name [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER ROLE name [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT
ALTER ROLE name [ IN DATABASE database_name ] RESET configuration_parameter
ALTER ROLE name [ IN DATABASE database_name ] RESET ALL
5.1 賦予bella 登錄權(quán)限
a. 查看現(xiàn)在的角色屬性
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
bella | Create DB, Cannot login | {}
david | | {}
postgres | Superuser, Create role, Create DB, Replication | {}
renee | Create DB | {}
sandy | | {}
postgres=#
b. 賦予登錄權(quán)限
postgres=# ALTER ROLE bella WITH LOGIN;
ALTER ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
bella | Create DB | {}
david | | {}
postgres | Superuser, Create role, Create DB, Replication | {}
renee | Create DB | {}
sandy | | {}
postgres=#
5.2 賦予renee 創(chuàng)建角色的權(quán)限
postgres=# ALTER ROLE renee WITH CREATEROLE;
ALTER ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
bella | Create DB | {}
david | | {}
postgres | Superuser, Create role, Create DB, Replication | {}
renee | Create role, Create DB | {}
sandy | | {}
postgres=#
5.3 賦予david 帶密碼登錄權(quán)限
postgres=# ALTER ROLE david WITH PASSWORD 'ufo456';
ALTER ROLE
postgres=#
5.4 設(shè)置sandy 角色的有效期
postgres=# ALTER ROLE sandy VALID UNTIL '2014-04-24';
ALTER ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
bella | Create DB | {}
david | | {}
postgres | Superuser, Create role, Create DB, Replication | {}
renee | Create role, Create DB | {}
sandy | | {}
postgres=# SELECT * from pg_roles ;
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolconfig | oid
----------+----------+------------+---------------+-------------+--------------+-------------+----------------+--------------+-------------+------------------------+-----------+-------
postgres | t | t | t | t | t | t | t | -1 | ******** | | | 10
bella | f | t | f | t | f | t | f | -1 | ******** | | | 49440
renee | f | t | t | t | f | t | f | -1 | ******** | | | 49442
david | f | t | f | f | f | t | f | -1 | ******** | | | 49438
sandy | f | t | f | f | f | t | f | -1 | ******** | 2014-04-24 00:00:00+08 | | 49439
(5 rows)
postgres=#
六、角色賦權(quán)/角色成員
在系統(tǒng)的角色管理中，通常會(huì)把多個(gè)角色賦予一個(gè)組，這樣在設(shè)置權(quán)限時(shí)只需給該組設(shè)置即可，撤銷權(quán)限時(shí)也是從該組撤銷。在PostgreSQL中，首先需要?jiǎng)?chuàng)建一個(gè)代表組的角色，之后再將該角色的membership 權(quán)限賦給獨(dú)立的角色即可。
6.1 創(chuàng)建組角色
postgres=# CREATE ROLE father login nosuperuser nocreatedb nocreaterole noinherit encrypted password 'abc123';
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
bella | Create DB | {}
david | | {}
father | No inheritance | {}
postgres | Superuser, Create role, Create DB, Replication | {}
renee | Create role, Create DB | {}
sandy | | {}
postgres=#
6.2 給father 角色賦予數(shù)據(jù)庫(kù)test 連接權(quán)限和相關(guān)表的查詢權(quán)限。
postgres=# GRANT CONNECT ON DATABASE test to father;
GRANT
postgres=# \c test renee
You are now connected to database "test" as user "renee".
test=> \dt
No relations found.
test=> CREATE TABLE emp (
test(> id serial,
test(> name text);
NOTICE: CREATE TABLE will create implicit sequence "emp_id_seq" for serial column "emp.id"
CREATE TABLE
test=> INSERT INTO emp (name) VALUES ('david');
INSERT 0 1
test=> INSERT INTO emp (name) VALUES ('sandy');
INSERT 0 1
test=> SELECT * from emp;
id | name
----+-------
1 | david
2 | sandy
(2 rows)
test=> \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+-------
public | emp | table | renee
(1 row)
test=> GRANT USAGE ON SCHEMA public to father;
WARNING: no privileges were granted for "public"
GRANT
test=> GRANT SELECT on public.emp to father;
GRANT
test=>
6.3 創(chuàng)建成員角色
test=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# CREATE ROLE son1 login nosuperuser nocreatedb nocreaterole inherit encrypted password 'abc123';
CREATE ROLE
postgres=#
這里創(chuàng)建了son1 角色，并開(kāi)啟inherit 屬性。PostgreSQL 里的角色賦權(quán)是通過(guò)角色繼承（INHERIT）的方式實(shí)現(xiàn)的。
6.4 將father 角色賦給son1
postgres=# GRANT father to son1;
GRANT ROLE
postgres=#
還有另一種方法，就是在創(chuàng)建用戶的時(shí)候賦予角色權(quán)限。
postgres=# CREATE ROLE son2 login nosuperuser nocreatedb nocreaterole inherit encrypted password 'abc123' in role father;
CREATE ROLE
postgres=#
6.5 測(cè)試son1 角色
postgres=# \c test son1
You are now connected to database "test" as user "son1".
test=> \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+-------
public | emp | table | renee
(1 row)
test=> SELECT * from emp;
id | name
----+-------
1 | david
2 | sandy
(2 rows)
test=>
用renee 角色新創(chuàng)建一張表，再次測(cè)試
test=> \c test renee
You are now connected to database "test" as user "renee".
test=> CREATE TABLE dept (
test(> deptid integer,
test(> deptname text);
CREATE TABLE
test=> INSERT INTO dept (deptid, deptname) values(1, 'ts');
INSERT 0 1
test=> \c test son1
You are now connected to database "test" as user "son1".
test=> SELECT * from dept ;
ERROR: permission denied for relation dept
test=>
son1 角色只能查詢emp 表的數(shù)據(jù)，而不能查詢dept 表的數(shù)據(jù)，測(cè)試成功。
6.6 查詢角色組信息
test=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=#
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
bella | Create DB | {}
david | | {}
father | No inheritance | {}
postgres | Superuser, Create role, Create DB, Replication | {}
renee | Create role, Create DB | {}
sandy | | {}
son1 | | {father}
son2 | | {father}
postgres=#
“ Member of ” 項(xiàng)表示son1 和son2 角色屬于father 角色組。
相關(guān)文章
如何解決PostgreSQL執(zhí)行語(yǔ)句長(zhǎng)時(shí)間卡著不動(dòng)不報(bào)錯(cuò)也不執(zhí)行的問(wèn)題
某日開(kāi)發(fā)同事上報(bào)一sql性能問(wèn)題,一條查詢好似一直跑不出結(jié)果,查詢了n小時(shí),還未返回結(jié)果,這篇文章主要給大家介紹了關(guān)于如何解決PostgreSQL執(zhí)行語(yǔ)句長(zhǎng)時(shí)間卡著不動(dòng)不報(bào)錯(cuò)也不執(zhí)行問(wèn)題的相關(guān)資料,需要的朋友可以參考下2024-02-02
PostgreSQL基礎(chǔ)知識(shí)之SQL操作符實(shí)踐指南
這篇文章主要給大家介紹了關(guān)于PostgreSQL基礎(chǔ)知識(shí)之SQL操作符實(shí)踐的相關(guān)資料，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家學(xué)習(xí)或者使用PostgreSQL具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2020-05-05
解決postgreSql 將Varchar類型字段修改為Int類型報(bào)錯(cuò)的問(wèn)題
這篇文章主要介紹了解決postgreSql 將Varchar類型字段修改為Int類型報(bào)錯(cuò)的問(wèn)題，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2020-12-12
PostgreSQL數(shù)據(jù)庫(kù)視圖及子查詢使用操作
這篇文章主要為大家介紹了PostgreSQL數(shù)據(jù)庫(kù)視圖及子查詢的使用操作，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步早日升職加薪2022-04-04
對(duì)PostgreSQL中的慢查詢進(jìn)行分析和優(yōu)化的操作指南
在數(shù)據(jù)庫(kù)的世界里,慢查詢就像是路上的絆腳石,讓數(shù)據(jù)處理的道路變得崎嶇不平,想象一下,你正在高速公路上飛馳,突然遇到一堆減速帶,那感覺(jué)肯定糟透了,本文介紹了怎樣對(duì)?PostgreSQL?中的慢查詢進(jìn)行分析和優(yōu)化,需要的朋友可以參考下2024-07-07
Docker環(huán)境下升級(jí)PostgreSQL的步驟方法詳解
這篇文章主要介紹了Docker環(huán)境下升級(jí)PostgreSQL的步驟方法詳解,本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2021-01-01
這篇文章主要介紹了基于pgrouting的路徑規(guī)劃處理,根據(jù)pgrouting已經(jīng)集成的Dijkstra算法來(lái)，結(jié)合postgresql數(shù)據(jù)庫(kù)來(lái)處理最短路徑，需要的朋友可以參考下2022-04-04
postgresql 刪除重復(fù)數(shù)據(jù)案例詳解
這篇文章主要介紹了postgresql 刪除重復(fù)數(shù)據(jù)案例詳解,本篇文章通過(guò)簡(jiǎn)要的案例,講解了該項(xiàng)技術(shù)的了解與使用,以下就是詳細(xì)內(nèi)容,需要的朋友可以參考下2021-08-08
postgreSQL中的內(nèi)連接和外連接實(shí)現(xiàn)操作
這篇文章主要介紹了postgreSQL中的內(nèi)連接和外連接實(shí)現(xiàn)操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01
Postgresql 賦予用戶權(quán)限和撤銷權(quán)限的實(shí)例
這篇文章主要介紹了Postgresql 賦予用戶權(quán)限和撤銷權(quán)限的實(shí)例，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01