目錄
- PostgreSQL(01): Ubuntu20.04/22.04 PostgreSQL 安裝配置記錄
- PostgreSQL(02): PostgreSQL常用命令
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;
- OIDs demoted to normal columns: a glance at the past
https://postgresql.verite.pro/blog/2019/04/24/oid-column.html - 表中隱藏的系統欄位, 應該是沒有網上一些文章說的自帶版本控制欄位用于樂觀鎖了
https://www.postgresql.org/docs/14/ddl-system-columns.html
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;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/541408.html
標籤：PostgreSQL