PostgreSQL(02): PostgreSQL常用命令

来源:https://www.cnblogs.com/milton/archive/2023/01/06/17031034.html
-Advertisement-
Play Games

目錄 PostgreSQL(01): Ubuntu20.04/22.04 PostgreSQL 安裝配置記錄 PostgreSQL(02): PostgreSQL常用命令 PostgreSQL 常用命令 滿足驗證條件的用戶, 可以用psql命令進入pg的命令行交互模式 用戶管理相關 查看用戶列表 \ ...


目錄

PostgreSQL 常用命令

滿足驗證條件的用戶, 可以用psql命令進入pg的命令行交互模式

用戶管理相關

查看用戶列表

\du和\du+

postgres=# \du;
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 ubuntu    |                                                            | {}

postgres=# \du+;
                                          List of roles
 Role name |                         Attributes                         | Member of | Description 
-----------+------------------------------------------------------------+-----------+-------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        | 
 ubuntu    |                                                            | {}        |

查看role的全局許可權和口令, pg通過host登錄, 驗證的是role的密碼

postgres=# select rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, substring(rolpassword, 1, 18) from pg_authid;
          rolname          | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit |     substring      
---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+--------------------
 postgres                  | t        | t          | t             | t           | t           | t              | t            |           -1 | 
 pg_database_owner         | f        | t          | f             | f           | f           | f              | f            |           -1 | 
 pg_read_all_data          | f        | t          | f             | f           | f           | f              | f            |           -1 | 
 pg_write_all_data         | f        | t          | f             | f           | f           | f              | f            |           -1 | 
 pg_monitor                | f        | t          | f             | f           | f           | f              | f            |           -1 | 
 pg_read_all_settings      | f        | t          | f             | f           | f           | f              | f            |           -1 | 
 pg_read_all_stats         | f        | t          | f             | f           | f           | f              | f            |           -1 | 
 pg_stat_scan_tables       | f        | t          | f             | f           | f           | f              | f            |           -1 | 
 pg_read_server_files      | f        | t          | f             | f           | f           | f              | f            |           -1 | 
 pg_write_server_files     | f        | t          | f             | f           | f           | f              | f            |           -1 | 
 pg_execute_server_program | f        | t          | f             | f           | f           | f              | f            |           -1 | 
 pg_signal_backend         | f        | t          | f             | f           | f           | f              | f            |           -1 | 
 pg_checkpoint             | f        | t          | f             | f           | f           | f              | f            |           -1 | 
 ubuntu                    | f        | t          | f             | f           | t           | f              | f            |           -1 | 
(14 rows)

創建用戶

4個sql執行的結果沒什麼區別, 口令都會用SHA-256加密

postgres=# CREATE USER test_user1 WITH PASSWORD 'secret_passwd';
CREATE ROLE
postgres=# CREATE USER test_user2 WITH ENCRYPTED PASSWORD 'secret_passwd';
CREATE ROLE
postgres=# CREATE ROLE test_user3 WITH LOGIN PASSWORD 'secret_passwd';
CREATE ROLE
postgres=# CREATE ROLE test_user4 WITH LOGIN ENCRYPTED PASSWORD 'secret_passwd';
CREATE ROLE
-- 查看添加的結果
postgres=# select rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, substring(rolpassword, 1, 18) from pg_authid;
          rolname          | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit |     substring      
---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+--------------------
 postgres                  | t        | t          | t             | t           | t           | t              | t            |           -1 | 
 pg_database_owner         | f        | t          | f             | f           | f           | f              | f            |           -1 | 
 ...
 ubuntu                    | f        | t          | f             | f           | t           | f              | f            |           -1 | 
 test_user1                | f        | t          | f             | f           | t           | f              | f            |           -1 | SCRAM-SHA-256$4096
 test_user2                | f        | t          | f             | f           | t           | f              | f            |           -1 | SCRAM-SHA-256$4096
 test_user3                | f        | t          | f             | f           | t           | f              | f            |           -1 | SCRAM-SHA-256$4096
 test_user4                | f        | t          | f             | f           | t           | f              | f            |           -1 | SCRAM-SHA-256$4096
(18 rows)

查看user表

template1=# SELECT * FROM pg_user;
  usename   | usesysid | usecreatedb | usesuper | userepl | usebypassrls |  passwd  | valuntil | useconfig 
------------+----------+-------------+----------+---------+--------------+----------+----------+-----------
 postgres   |       10 | t           | t        | t       | t            | ******** |          | 
 ubuntu     |    16388 | f           | f        | f       | f            | ******** |          | 
 test_user2 |    16390 | f           | f        | f       | f            | ******** |          | 
 test_user3 |    16391 | f           | f        | f       | f            | ******** |          | 
 test_user4 |    16392 | f           | f        | f       | f            | ******** |          | 
 test_user1 |    16389 | f           | f        | f       | f            | ******** |          | 
(6 rows)

修改用戶口令

postgres=# ALTER ROLE test_user1 WITH password 'secret_passwd1';
ALTER ROLE

賦予許可權

可以直接將一個用戶的許可權賦給另一個用戶(以及收回)

GRANT myuser TO myuser1;
REVOKE myuser FROM myuser1;

查看用戶許可權之間的引用關係

postgres=# SELECT 
      r.rolname, 
      ARRAY(SELECT b.rolname
            FROM pg_catalog.pg_auth_members m
            JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
            WHERE m.member = r.oid) as memberof
FROM pg_catalog.pg_roles r
WHERE r.rolname NOT IN ('pg_signal_backend','rds_iam',
                        'rds_replication','rds_superuser',
                        'rdsadmin','rdsrepladmin')
ORDER BY 1;
          rolname          |                           memberof                           
---------------------------+--------------------------------------------------------------
 pg_checkpoint             | {}
 pg_database_owner         | {}
 pg_execute_server_program | {}
 pg_monitor                | {pg_read_all_settings,pg_read_all_stats,pg_stat_scan_tables}
 pg_read_all_data          | {}
 pg_read_all_settings      | {}
 pg_read_all_stats         | {}
 pg_read_server_files      | {}
 pg_stat_scan_tables       | {}
 pg_write_all_data         | {}
 pg_write_server_files     | {}
 postgres                  | {}
 test_user1                | {}
 test_user2                | {}
 test_user3                | {}
 test_user4                | {}
 ubuntu                    | {}
(17 rows)

DATABASE 相關

資料庫列表

\l

postgres=# \l
                                             List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  | ICU Locale | Locale Provider |   Access privileges   
-----------+----------+----------+---------+---------+------------+-----------------+-----------------------
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 |            | libc            | 
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 |            | libc            | =c/postgres          +
           |          |          |         |         |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 |            | libc            | =c/postgres          +
           |          |          |         |         |            |                 | postgres=CTc/postgres
(3 rows)

選中資料庫

\c [dbname]

postgres=# \c template1
You are now connected to database "template1" as user "postgres".

創建資料庫

創建資料庫並指定owner, 修改owner

-- 如果不指定, 則owner為當前用戶
template1=# CREATE DATABASE test_db1;
CREATE DATABASE
-- 指定用戶
template1=# CREATE DATABASE test_db2 OWNER test_user2;
CREATE DATABASE
template1=# CREATE DATABASE test_db3;
CREATE DATABASE
template1=# \l
                                              List of databases
   Name    |   Owner    | Encoding | Collate |  Ctype  | ICU Locale | Locale Provider |   Access privileges   
-----------+------------+----------+---------+---------+------------+-----------------+-----------------------
...
 test_db1  | postgres   | UTF8     | C.UTF-8 | C.UTF-8 |            | libc            | 
 test_db2  | test_user2 | UTF8     | C.UTF-8 | C.UTF-8 |            | libc            | 
 test_db3  | postgres   | UTF8     | C.UTF-8 | C.UTF-8 |            | libc            | 
(6 rows)

-- 修改owner
template1=# ALTER DATABASE test_db3 OWNER to test_user3;
ALTER DATABASE
-- 查看修改結果
template1=# \l
                                              List of databases
   Name    |   Owner    | Encoding | Collate |  Ctype  | ICU Locale | Locale Provider |   Access privileges   
-----------+------------+----------+---------+---------+------------+-----------------+-----------------------
 ...
 test_db1  | postgres   | UTF8     | C.UTF-8 | C.UTF-8 |            | libc            | 
 test_db2  | test_user2 | UTF8     | C.UTF-8 | C.UTF-8 |            | libc            | 
 test_db3  | test_user3 | UTF8     | C.UTF-8 | C.UTF-8 |            | libc            |

刪除資料庫

template1=# DROP DATABASE test_db3;
DROP DATABASE
-- 刪除前判斷是否存在
template1=# DROP DATABASE IF EXISTS test_db3;
NOTICE:  database "test_db3" does not exist, skipping
DROP DATABASE

授權資料庫給用戶

只是授權, 和owner有區別

-- 授權部分許可權
template1=# GRANT CONNECT ON DATABASE test_db1 TO test_user1;
GRANT
-- 授權全部許可權
template1=# GRANT ALL PRIVILEGES ON DATABASE test_db1 TO test_user2;
GRANT

查看資料庫許可權, 將sql中的 test_user2 換成要檢查的目標用戶

SELECT 'test_user2', datname, array(
	SELECT privs FROM unnest(ARRAY[
	(CASE WHEN has_database_privilege('test_user2',c.oid,'CONNECT') THEN 'CONNECT' ELSE NULL END),
	(CASE WHEN has_database_privilege('test_user2',c.oid,'CREATE') THEN 'CREATE' ELSE NULL END),
	(CASE WHEN has_database_privilege('test_user2',c.oid,'TEMPORARY') THEN 'TEMPORARY' ELSE NULL END),
	(CASE WHEN has_database_privilege('test_user2',c.oid,'TEMP') THEN 'TEMP' ELSE NULL END)])
	foo(privs)
	WHERE privs IS NOT NULL
) FROM pg_database c;

  ?column?  |  datname  |              array              
------------+-----------+---------------------------------
 test_user2 | postgres  | {CONNECT,TEMPORARY,TEMP}
 test_user2 | template1 | {CONNECT}
 test_user2 | template0 | {CONNECT}
 test_user2 | test_db2  | {CONNECT,CREATE,TEMPORARY,TEMP}
 test_user2 | test_db1  | {CONNECT,CREATE,TEMPORARY,TEMP}
(5 rows)

SCHEMA 相關

每個database都包含一個預設的schema, 名稱為 public, 如果不指定, 則使用這個預設的 schema.

除了public和用戶創建的schema之外, 每個資料庫都包含一個pg_catalog的schema, 它包含系統表和所有內置數據類型、函數、操作符. pg_catalog 總是搜索路徑中的一部分. 如果它沒有明確出現在路徑中, 那麼它隱含地在所有路徑之前搜索. 這樣就保證了內置名字總是可以被搜索. 不過, 你可以明確地把pg_catalog放在搜索路徑之後, 如果你想使用用戶自定義的名字覆蓋內置的名字的話.

-- 新增
CREATE SCHEMA aStock;
CREATE SCHEMA schema_name AUTHORIZATION user_name;
-- 刪除空schema
DROP SCHEMA aStock; 
-- 遞歸刪除非空 schema
DROP SCHEMA aStock CASCADE;

-- 顯示搜索路徑
SHOW search_path;
-- 變更搜索路徑：
SET search_path TO aStock, public;
SET search_path TO myschema;

授權schema給用戶

GRANT USAGE ON SCHEMA myschema TO myuser;
-- 如果用戶需要建表許可權
GRANT USAGE, CREATE ON SCHEMA myschema TO myuser;

TABLE 相關

授權table給用戶

GRANT SELECT ON TABLE mytable1, mytable2 TO myuser;
-- 如果需要包含myschema下所有table和view
GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO myuser;
-- 如果需要增刪改
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE mytable1, mytable2 TO myuser;
-- 如果需要包含myschema下所有table和view
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA myschema TO myuser;

註意上面的命令, 如果schema下創建了新table, myuser並不能訪問, 如果要新建的table也自動授權, 需要使用下麵的語句

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO myuser;
-- 帶增刪改
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO myuser;

SEQUENCE 相關

GRANT USAGE ON SEQUENCE myseq1, myseq2 TO readwrite;
-- You can also grant permission to all sequences using the following SQL statement:
GRANT USAGE ON ALL SEQUENCES IN SCHEMA myschema TO readwrite;
-- To automatically grant permissions to sequences added in the future:
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT USAGE ON SEQUENCES TO readwrite;

複雜查詢

分組後取第一條

根據bank_card_no分組, 取時間最晚的一條, 使用ROW_NUMBER() OVER (PARTITION BY [col1] ORDER BY [col2] [ASC|DESC]) AS [alias]格式

WITH tb1 AS (
  SELECT
    goods_order.*,
    ROW_NUMBER() OVER (PARTITION BY goods_order.bank_card_no ORDER BY created_at DESC ) AS rn
  FROM
    goods_order 
  WHERE goods_order.batch_id = 521
)
SELECT * from tb1
WHERE rn=1

對JSONB序列組合去重後更新

和MySQL一樣, 如果要update的欄位也在取值參數中, 需要多加一層select隔離一下才能執行

UPDATE goods_order
SET card_label = (select json_agg(t001.t) from (
  select distinct(jsonb_array_elements(goods_order.card_label || '["tag1","tag2","tag3"]'::jsonb)) as t
) t001)
where 
bank_card_no IN ( '123123123123' )

分組取最大最小值, 計數以及打上序號

SELECT
    goods_order.*,
    max(goods_order.created_at) OVER w AS created_at_max,
    min(goods_order.created_at) OVER w AS created_at_min,
    count(1) OVER w AS row_count,
    ROW_NUMBER() OVER w1 AS seq
  FROM
    goods_order 
  WHERE
    (
      goods_order.data_import_id = 2
      OR goods_order.data_import_id = 534 
    )
WINDOW 
w AS (PARTITION BY goods_order.bank_card_no),
w1 AS (PARTITION BY goods_order.bank_card_no ORDER BY created_at DESC)

使用temp view 簡化後續查詢

CREATE OR REPLACE TEMP VIEW view1 AS
SELECT
    goods_order.*,
    max(goods_order.created_at) OVER w AS created_at_max,
    min(goods_order.created_at) OVER w AS created_at_min,
    count(1) OVER w AS row_count,
    ROW_NUMBER() OVER w1 AS seq
  FROM
    goods_order 
  WHERE
    (
      goods_order.data_import_id = 2
      OR goods_order.data_import_id = 534 
    )
WINDOW 
w AS (PARTITION BY goods_order.bank_card_no),
w1 AS (PARTITION BY goods_order.bank_card_no ORDER BY created_at DESC);

select count(1) from view1;

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Ajax+WCF+MySQL實現資料庫部署並調用
    ​ 最近的資料庫課程要求將MySQL資料庫部署在伺服器上，參考了大佬們的博客後，總結一下。 先放上參考的大佬們的博客。 【原創經驗分享】JQuery（Ajax）調用WCF服務 - 南宮蕭塵 - 博客園 (cnblogs.com) WinForm+WCF+mysql+http實現簡單的用戶登錄註冊_小 ...
  • 學習.NET MAUI Blazor（四）、路由
    在ERP系統中，採集一線的生產數據是重要工作之一，而稱重計量是企業的核心資產數據，人工計重費時費力，還容易出錯，重量數據是否正確，直接影響企業的採購或銷售額。基於此，由系統對接電子秤實現自動抓取數據是企業管理的第一步。 電子秤，一般由重量感測器、砝碼、底座、儀錶等組成。儀錶與感測器相連，儀錶一般具有 ...
  • 那些年我們用過的組件-結構化日誌組件 Serilog
    什麼是結構化日誌 我們記錄日誌慣常使用 log4j2、NLog 等日誌組件，這些組件提供了輸出到多種終端的能力，但是大部分時候我們選擇將日誌輸出到操作系統的文件系統中，為什麼呢？至少有一部分原因是記錄的每條日誌為字元串格式，且按時間由遠往進順序記錄，打開文件可以直接人肉檢索；如果這些日誌記錄到其它終 ...
  • 修改NuGet包預設存放位置
    本實例使用了工具包SKIT.FlurlHttpClient.Wechat.TenpayV3（github:https://github.com/fudiwei/DotNetCore.SKIT.FlurlHttpClient.Wechat） 示例中的_repositoryWrapper的相關使用是我們 ...
  • cloudflare tunnel 折騰小記
    cloudflare 賬號註冊 https://www.cloudflare-cn.com/products/tunnel/ 功能變數名稱準備和配置 有兩種方式: 在 cloudflare 自己購買功能變數名稱, 比較貴 在其他平臺的功能變數名稱， 通過配置解析功能變數名稱規則， 這樣可以托管在 cloudflare 以阿裡雲為, ...
  • OpenOCD + DAP-LINK 調試ESP32的失敗經歷（2）
    背景 https://www.cnblogs.com/liteng0305/p/17018299.html 上次使用樂鑫編譯好的OpenOCD失敗，可能是因為沒有開啟CMSIS-DAP支持，手動開啟編譯試一下 平臺 Ubuntu Linux 5.4.0 官方OpenOCD 直接下載的OpenOCD沒 ...
  • 【OS】05 - 主引導程式的擴展（下）
    mycode : mycode 思考 突破引導程式方法： 再寫一個程式，並且把這個程式放到存儲介質中； 主引導程式要載入這個新的程式，將控制權轉交給新的程式； 遇到的問題：怎麼在存儲介質中找這個新的程式呢？ 那就需要藉助於一個文件系統，有了文件系統，就可以很方便的把寫好的程式放到軟盤裡了，也可以根據 ...
  • OpenOCD + DAP-LINK調試ESP32的失敗經歷
    目的 手裡有調試STM32的DAP-LINK，想試試通過JTAG調試ESP32 OpenOCD支持CMSIS-DAP DAP-LINK支持的晶元，我手上這款描述如下，應該JTAG協議的都支持 平臺 windows10 + ESP-IDF ESP-WROOM-32E模組 + 燒錄底座 DAP-LINK ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類