(一)背景
PG运维老师咨询过这样的一个问题:使用psql运维数据库时,常常使用快捷命令执行运维,如\l查看数据库,\dt+查看表详情,\dx查看插件等等,如果平台建设时,想参考快捷命令的实现方式,如何才能知道后台执行了什么SQL呢?
样例说明:
通过\dt+查看数据库所有a开头的表详情
tips: 不指定schema的情况下,\dt是查看当前用户当前会话,search_path参数限制下,有查询权限的schema的所有表,支持使用*完成模糊查询(对于tips内容不关注的可以直接跳到(二)实现参考)
1)search_path:
假设创建scott.dept表,当前会话search_path参数无scott这个schema,则\dt是看不到scott.emp表的。
复现&解决: 你可以通过show search_path查看配置,使用set设置(仅当前会话生效),也可以通过postgresql.conf全局调整。如下:
pgair=# create schema scott;
CREATE SCHEMA
pgair=# create table scott.dept(deptno smallserial primary key,dname varchar(15),loc varchar(50));
CREATE TABLE
-- 当前参数不包含scott
pgair=# show search_path ;
search_path
-------------------------------
"$user", public, postgres_air
(1 row)
-- \dt看不到创建的scott.dept表,只有public下的
pgair=# \dt
List of relations
Schema | Name | Type | Owner
--------------+-------------------+-------+----------
public | customers | table | postgres
public | orders | table | postgres
(2 rows)
-- 当前会话设置
pgair=# set search_path="$user", public, postgres_air,scott;
SET
-- 再次执行\dt就可以看到了
pgair=# \dt
List of relations
Schema | Name | Type | Owner
--------------+-------------------+-------+----------
public | customers | table | postgres
public | orders | table | postgres
scott | dept | table | postgres
(3 rows)
2)schema权限:
如果当前用户没有schema的权限,即使search_path包含,也是查看不到的。例如在1)的基础上,我们创建一个普通账号验证。
pgair=# create user joyann1;
CREATE ROLE
-- 切换到该用户下
pgair=# \c pgair joyann1
You are now connected to database "pgair" as user "joyann1".
-- 确认search_path是有scott用户的
pgair=> show search_path ;
search_path
--------------------------------------
"$user", public, postgres_air, scott
(1 row)
-- \dt依旧看不到创建的scott.dept表,只有public下的
pgair=# \dt
List of relations
Schema | Name | Type | Owner
--------------+-------------------+-------+----------
public | customers | table | postgres
public | orders | table | postgres
(2 rows)
-- 查看schema scott都是是否有权限,发现joyann1没有权限,如下
pgair=> \dn+ scott
List of schemas
Name | Owner | Access privileges | Description
-------+----------+-------------------+-------------
scott | postgres | |
(1 row)
-- 重开会话,使用管理员授权scheme使用权限给joyann1,注意这里并没有给表数据的查看权限
pgair=# grant usage on schema scott to joyann1;
GRANT
-- 使用joyann1查看发现已经有schema的权限了:
pgair=> \dn+ scott
List of schemas
Name | Owner | Access privileges | Description
-------+----------+----------------------+-------------
scott | postgres | postgres=UC/postgres+|
| | joyann1=U/postgres |
(1 row)
-- 再次执行\dt就可以看到scott.dept表了
pgair=# \dt
List of relations
Schema | Name | Type | Owner
--------------+-------------------+-------+----------
public | customers | table | postgres
public | orders | table | postgres
scott | dept | table | postgres
(3 rows)
(二)实现参考
\set ECHO_HIDDEN on
展示参考:
通过设置\set ECHO_HIDDEN on,就可以查看到快捷命令都执行了那些SQL。不需要了可以\set ECHO_HIDDEN off取消或者直接退出当前会话。
本次案例输出如下:
QUERY部分就是实际执行的SQL,注意:有些快捷方式涉及多个SQL,QUERY部分也会分开展示多个SQL,实际对接平台时,需要适当串联。
后记:
如果有任何需要指正/指教/交流的,可以关注微信公众号《PostgreSQL手记》后台评论,大家一起学习~