如何在PostgreSQL服务端配置让指定列不被SELECT *语句查询到

结论 #

PostgreSQL 12及更高版本已经原生支持用户自定义列的隐藏属性，可以在服务端层面实现指定列对SELECT *通配符隐藏的需求，和系统内置隐藏列的效果一致。

具体操作方法 #

1. 建表时直接指定隐藏列

针对你举的示例场景，建表语句如下：

CREATE TABLE test_table (
    column_shown_by_default text,
    column_hidden_by_default text INVISIBLE
);

执行SELECT * FROM test_table时，只会返回column_shown_by_default列，隐藏列不会出现在结果中。如果需要查询隐藏列，只要在查询语句中显式指定列名即可：

SELECT column_shown_by_default, column_hidden_by_default FROM test_table;

2. 给已存在的表的列设置/取消隐藏属性

如果表已经创建完成，可以通过ALTER TABLE语句修改列的可见性：

-- 设为隐藏
ALTER TABLE test_table ALTER COLUMN column_hidden_by_default SET INVISIBLE;
-- 取消隐藏
ALTER TABLE test_table ALTER COLUMN column_hidden_by_default SET VISIBLE;

补充说明 #

• 用户自定义的隐藏列和ctid、xmin这类系统级隐藏列的区别是：自定义隐藏列仅对*通配符隐藏，只要显式指定列名就可以正常读写，权限规则和普通列完全一致；系统级隐藏列是内核层面的特殊属性，默认不会出现在任何通配符匹配结果中。
• 如果使用的是PostgreSQL 12以下的旧版本，没有原生隐藏列特性，可以通过视图模拟实现：创建仅包含公开列的视图，回收原表的公开查询权限，仅给业务侧开放视图的查询权限即可。

内容的提问来源于stack exchange，提问作者KRISTIJAN TOMASINI