postgreSQL 使用者、角色、權限、資料庫的簡單使用

最近公司使用postgreSQL資料庫進行應用開發，對使用者、權限和資料庫的使用有些和mysql不一緻的地方，記下來供以後參考。

使用者和角色

postgreSQL的使用者和角色基本沒有什麼差别，隻不過使用者是預設有資料庫的login權限的，而角色沒有。

角色可以被當做一個使用者、或者一個組。

摘取postgreSQL手冊建立使用者大綱：

CREATE ROLE name [ [ WITH ] option [ … ] ]

where option可以是：

SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| CREATEUSER | NOCREATEUSER
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| BYPASSRLS | NOBYPASSRLS
| 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
           

• 使用者登陸

  psql -U username

  password:

  如下圖：

  

• 建立使用者

  create user username;

• 删除使用者

  drop user username;

• 建立一個有密碼的使用者

  create user username with password ‘password’;

• 建立角色

  create role rolename;

角色使用者此時是沒有登陸權限的，可以登陸驗證一下.

• 删除角色

  drop role rolename;

• 建立一個有登陸權限的角色

  create role rolename login;

• 建立一個有密碼的登陸角色

  create role rolename login password ‘password’;

當然也可以使用shell指令進行使用者建立，這裡就不介紹了。

資料庫

要建立一個資料庫那你必須是管理者或者有建立資料庫權限。

建立資料庫的時候預設使用template資料庫模闆，當然也可以使用template name指令指定模闆。下面有例子。

摘取postgreSQL手冊大綱：

CREATE DATABASE name
[ [ WITH ] [ OWNER [=] user_name ]
       [ TEMPLATE [=] template ]
       [ ENCODING [=] encoding ]
       [ LC_COLLATE [=] lc_collate ]
       [ LC_CTYPE [=] lc_ctype ]
       [ TABLESPACE [=] tablespace_name ]
       [ ALLOW_CONNECTIONS [=] allowconn ]
       [ CONNECTION LIMIT [=] connlimit ] ]
       [ IS_TEMPLATE [=] istemplate ]
           

• 建立一個資料庫

  create database databasename;

• 按模闆資料庫建立一個資料庫

  create database databasename template templatename; //目前登陸使用者必須對模闆資料庫有連接配接權限

• 删除一個資料庫

  drop database databasename;

• 建立一個資料庫同時指定它的所有者

  create database databasename owner username template templatename;

• 建立一個資料庫，然後隻允許管理者和資料庫所有者才能連接配接

  create database databasename owner username; //建立一個資料庫

  revoke all on database databasename from public; //回收其他使用者的連接配接權限

  grant connect on database databasename to username; //給使用者賦予資料庫連接配接權限

因為資料庫建立的時候是預設将連接配接權限授予public角色，是以新建立的資料庫是所有使用者都能看到的，我們可以在建立完資料庫之後對連接配接權限進行回收，以此達到資料庫私有。

授權GRANT

摘取postgreSQL資料庫手冊大綱：

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
    [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
    ON [ TABLE ] table_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { USAGE | SELECT | UPDATE }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { SEQUENCE sequence_name [, ...]
         | ALL SEQUENCES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
    ON DATABASE database_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON DOMAIN domain_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN DATA WRAPPER fdw_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN SERVER server_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
    ON { FUNCTION function_name ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) [, ...]
         | ALL FUNCTIONS IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON LANGUAGE lang_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
    ON LARGE OBJECT loid [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
    ON SCHEMA schema_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { CREATE | ALL [ PRIVILEGES ] }
    ON TABLESPACE tablespace_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON TYPE type_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

這裡的role_specification可以是:

    [ GROUP ] role_name
  | PUBLIC
  | CURRENT_USER
  | SESSION_USER

GRANT role_name [, ...] TO role_name [, ...] [ WITH ADMIN OPTION ]
           

• 将表的dml權限授予一個或多個使用者

  grant select,insert,update,delete,truncate on tablename to username1, username2, rolename1,rolename2;

• 将表的dml權限授予所有使用者

  grant select,insert,update,delete,truncate on tablename to public;

• 将表、視圖、資料庫、模式等的所有權限授予使用者

  grant all privileges on tablename to username;

• 将表、視圖、資料庫、模式等的所有權限授予所有使用者

  grant all privileges on tablename to public;

• 将使用者fire的權限授予icer

  grant fire to icer;

相關參考資料

—— [ PostgreSQL 9.5.3 中文手冊 ]

—— [ PostgreSQL配置優化 ]

網上資料還有很多，大家可以多google一下。