01_User Privileges.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# User Privileges\n",
    "本篇介紹基本的使用者權限，如果你是 DBA 的話，這是必然會面對的議題。<br/>\n",
    "本手冊操作一些常用的指令，並且帶入基本的設計概念。\n",
    "\n",
    "- **這份筆記的執行次序需要自行調整**\n",
    "\n",
    "但請特別注意，<br/>\n",
    "實際上權限設計，最好能夠**和組織文化相容，才能降低管理的複雜度**。\n",
    "\n",
    "資料庫的使用者權限和其他系統很類似，作為最基礎性的權限規畫方式，良好的使用者權限規畫，可以減少其他細部權限設定的困擾。\n",
    "\n",
    "1. 權限由小到大\n",
    "2. 群組相容人員組織"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 版權\n",
    "本筆記由 [PostgreSQL 台灣使用者社群](https://postgresql.tw)提供，採 [CC BY 4.0](https://creativecommons.org/licenses/by/4.0/deed.zh_TW) 授權。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 開始囉"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 載入 SQL 延伸套件，每次重開筆記都必須再次執行！\n",
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 連接你的資料庫系統吧！(最好是測試系統)\n",
    "# 使用 Binder 的話，請保持下面設定直接連線；否則請依你的資料庫連線資訊調整。\n",
    "# postgresql://username:password@host/database\n",
    "%sql postgresql://localhost/postgres"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 看看你的資料庫裡有哪些「role」？\n",
    "# role 是 PostgreSQL 中的「使用者」或「群組」\n",
    "%sql SELECT * FROM pg_roles;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 有關於 pg_roles 的欄位，請參閱[手冊說明](https://docs.postgresql.tw/internals/system-catalogs/pg_roles)。\n",
    "- role 的介紹在手冊的[第 21 章](https://docs.postgresql.tw/server-administration/user-manag)。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 如果有很多，我想要只看我自己呢？\n",
    "# current_user 指的是目前使用者名稱\n",
    "%sql SELECT * FROM pg_roles WHERE rolname=current_user"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "欄位很多，目前只看 rolsuper，瞭解自己是否為「超級使用者」（super user）。和其他系統意義相同，就是可以做到任何事的使用者。<br/>\n",
    "代表的危機意義也相同，除了小心使用之外，通常我們也會需要其他權限有所限制的使用者，以免發生人為的疏失。\n",
    "\n",
    "如果你使用 psql，會看到類似這樣的提示字串：\n",
    "```\n",
    "postgres=#\n",
    "```\n",
    "和一般 Linux 系統相同，「#」代表是系統管理者。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 使用者"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 建立使用者\n",
    "%sql CREATE ROLE alice\n",
    "# 你可以先回到前面步驟查詢 pg_roles，是否有 alice？"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- [CREATE ROLE](https://docs.postgresql.tw/reference/sql-commands/create-role)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "你應該會找到 alice，但無法進行下一步，因為無法登入（rolcanlogin=false），也沒有密碼。<br/>\n",
    "通常這樣建立的帳號是用於群組使用。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 刪除使用者\n",
    "%sql DROP ROLE alice"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- [DROP ROLE](https://docs.postgresql.tw/reference/sql-commands/drop-role)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 建立有密碼，可登入的使用者\n",
    "%sql CREATE ROLE alice WITH LOGIN PASSWORD 'alicepassword'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 現在 alice 有密碼，也可正常登入了，但我想加上使用期限，以免到期時忘記刪除。\n",
    "%sql ALTER ROLE alice VALID UNTIL '2018-12-31'\n",
    "# 執行完後你可以再查詢 pg_roles 的 rolvaliduntil 欄位資訊"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- [ALTER ROLE](https://docs.postgresql.tw/reference/sql-commands/alter-role)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 你也可以調整 alice 所能使用的資源\n",
    "%sql ALTER ROLE alice SET maintenance_work_mem = 100000;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 權限"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 建立一個測試權限用資料表\n",
    "%sql CREATE TABLE IF NOT EXISTS test (key text, value text)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 檢查一下你現在登入的使用者是誰?\n",
    "%sql SELECT current_user"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 試著把 alice 在資料表 test 的 SELECT 權限取消\n",
    "# 預設是 public，也就是其他人，可使用該資料表，所以也要取消 (時常會疏忽的部份)\n",
    "# 重跑此行時要注意是否用管理者帳號登入\n",
    "%sql REVOKE SELECT ON TABLE test FROME alice, public"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# has_table_privilege 可以檢查存取權限\n",
    "%sql SELECT has_table_privilege('alice', 'test', 'SELECT')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 再次授予權限\n",
    "%sql GRANT SELECT ON TABLE test TO alice, public"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 也可以真的使用 alice 登入來試試看下面兩個步驟\n",
    "%sql postgresql://alice:alicepassword@localhost/postgres"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT * FROM test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# alice 只有使用權，而沒有管理權\n",
    "%sql DROP TABLE test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}