| 屬性 | 說明 |
| login | 只有具有 LOGIN 屬性的角色可以用做數(shù)據(jù)庫連接的初始角色名。 |
| superuser | 數(shù)據(jù)庫超級(jí)用戶 |
| createdb | 創(chuàng)建數(shù)據(jù)庫權(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)沒有創(chuàng)建數(shù)據(jù)庫等權(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ù)庫及帶有密碼登錄的屬性。
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 測試renee 角色
a. 登錄
postgres@CS-DEV:~> psql -U renee -d postgres
psql (9.1.0)
Type "help" for help.
postgres=>
用renee 用戶登錄數(shù)據(jù)庫，發(fā)現(xiàn)不需要輸入密碼既可登錄，不符合實(shí)際情況。
b. 查找原因
在角色屬性中關(guān)于password的說明，在登錄時(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ù)庫。
d. 測試創(chuàng)建數(shù)據(jù)庫
創(chuàng)建成功。
五、給已存在用戶賦予各種權(quán)限
使用ALTER ROLE 命令。
ALTER ROLE 語法：
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ù)庫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 角色，并開啟inherit 屬性。PostgreSQL 里的角色賦權(quán)是通過角色繼承（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 測試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)建一張表，再次測試
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ù)，測試成功。
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 角色組。
標(biāo)簽：舟山 呼倫貝爾 黃石 楚雄 池州 菏澤 安順 白山
巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《PostgreSQL 角色與用戶管理介紹》，本文關(guān)鍵詞 PostgreSQL,角色,與,用戶,管理,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。
