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
  • C#自增運算符詳解：++i與i++的區別及應用場景
    概述：在C#中，++i和i++都是自增運算符，其中++i先增加值再返回，而i++先返回值再增加。應用場景根據需求選擇，首碼適合先增後用，尾碼適合先用後增。詳細示例提供清晰的代碼演示這兩者的操作時機和實際應用。 在C#中，++i 和 i++ 都是自增運算符，但它們在操作上有細微的差異，主要體現在操作的 ...
  • Taurus.MVC 性能壓力測試（ap 壓測 和 linux 下wrk 壓測）：.NET 版本
    上次發佈了：Taurus.MVC 性能壓力測試（ap 壓測 和 linux 下wrk 壓測）：.NET Core 版本，今天計劃準備壓測一下 .NET 版本，來測試並記錄一下 Taurus.MVC 框架在 .NET 版本的性能，以便後續持續優化改進。 為了方便對比，本文章的電腦環境和測試思路，儘量和... ...
  • 一文讀懂.NET WebAPI中FromRoute、FromQuery、FromBody的關鍵角色與用法
    .NET WebAPI作為一種構建RESTful服務的強大工具，為開發者提供了便捷的方式來定義、處理HTTP請求並返迴響應。在設計API介面時，正確地接收和解析客戶端發送的數據至關重要。.NET WebAPI提供了一系列特性，如[FromRoute]、[FromQuery]和[FromBody]，用 ...
  • WPF/C#實現圖像濾鏡優化方案：打造炫目視覺體驗！
    原因：我之所以想做這個項目，是因為在之前查找關於C#/WPF相關資料時，我發現講解圖像濾鏡的資源非常稀缺。此外，我註意到許多現有的開源庫主要基於CPU進行圖像渲染。這種方式在處理大量圖像時，會導致CPU的渲染負擔過重。因此，我將在下文中介紹如何通過GPU渲染來有效實現圖像的各種濾鏡效果。 生成的效果 ...
  • Bogus 實戰：使用 Bogus 和 EFCore 生成模擬數據和種子數據【完整教程】
    引言 上一章我們介紹了在xUnit單元測試中用xUnit.DependencyInject來使用依賴註入，上一章我們的Sample.Repository倉儲層有一個批量註入的介面沒有做單元測試，今天用這個示例來演示一下如何用Bogus創建模擬數據 ，和 EFCore 的種子數據生成 Bogus 的優 ...
  • WPF隨筆收錄-實時繪製心率曲線
    一、前言 在自己的項目中，涉及到實時心率曲線的繪製，項目上的曲線繪製，一般很難找到能直接用的第三方庫，而且有些還是定製化的功能，所以還是自己繪製比較方便。很多人一聽到自己畫就害怕，感覺很難，今天就分享一個完整的實時心率數據繪製心率曲線圖的例子；之前的博客也分享給DrawingVisual繪製曲線的方 ...
  • wpf程式修改啟動入口註意事項
    如果你在自定義的 Main 方法中直接使用 App 類並啟動應用程式，但發現 App.xaml 中定義的資源沒有被正確載入，那麼問題可能在於如何正確配置 App.xaml 與你的 App 類的交互。 確保 App.xaml 文件中的 x:Class 屬性正確指向你的 App 類。這樣，當你創建 Ap ...
  • 記一次 .NET某爐膛鍋爐檢測系統 崩潰分析
    一：背景 1. 講故事 上個月有個朋友在微信上找到我，說他們的軟體在客戶那邊隔幾天就要崩潰一次，一直都沒有找到原因，讓我幫忙看下怎麼回事，確實工控類的軟體環境複雜難搞，朋友手上有一個崩潰的dump，剛好丟給我來分析一下。 二：WinDbg分析 1. 程式為什麼會崩潰 windbg 有一個厲害之處在於 ...
  • 一個.NET內置依賴註入的小型強化版
    前言 .NET生態中有許多依賴註入容器。在大多數情況下，微軟提供的內置容器在易用性和性能方面都非常優秀。外加ASP.NET Core預設使用內置容器，使用很方便。 但是筆者在使用中一直有一個頭疼的問題：服務工廠無法提供請求的服務類型相關的信息。這在一般情況下並沒有影響，但是內置容器支持註冊開放泛型服 ...
  • WPF隨筆收錄-DataGrid固定右側列
    一、前言 在項目開發過程中，DataGrid是經常使用到的一個數據展示控制項，而通常表格的最後一列是作為操作列存在，比如會有編輯、刪除等功能按鈕。但WPF的原始DataGrid中，預設只支持固定左側列，這跟大家習慣性操作列放最後不符，今天就來介紹一種簡單的方式實現固定右側列。（這裡的實現方式參考的大佬 ...
所有分類