3
PostgreSQL(02): PostgreSQL常用命令 - Milton
source link: https://www.cnblogs.com/milton/p/17031034.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
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 ROLEpostgres=# CREATE USER test_user2 WITH ENCRYPTED PASSWORD 'secret_passwd';CREATE ROLEpostgres=# CREATE ROLE test_user3 WITH LOGIN PASSWORD 'secret_passwd';CREATE ROLEpostgres=# 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 memberofFROM pg_catalog.pg_roles rWHERE 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 template1You 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 DATABASEtemplate1=# CREATE DATABASE test_db3;CREATE DATABASEtemplate1=# \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) -- 修改ownertemplate1=# 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, skippingDROP 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;-- 删除空schemaDROP SCHEMA aStock; -- 递归删除非空 schemaDROP 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和viewGRANT SELECT ON ALL TABLES IN SCHEMA myschema TO myuser;-- 如果需要增删改GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE mytable1, mytable2 TO myuser;-- 如果需要包含myschema下所有table和viewGRANT 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 tb1WHERE rn=1
对JSONB序列组合去重后更新
和MySQL一样, 如果要update的字段也在取值参数中, 需要多加一层select隔离一下才能执行
UPDATE goods_orderSET 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 ASSELECT 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;
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK