我需要加密PostgreSQL 9.6数据库中的某些列。加密的数据本质上是敏感的。但是，这些数据不是密码或其他身份验证凭据。用户需要解密此数据以进行统计分析和使用。
在阅读了几个问题和答案之后：
...这似乎是使用t的最大问题他的pgcrypto模块是在同一个数据库中存储密钥 。
将密钥存储在其他数据库中并通过外部数据包装程序（例如Postgresql_FDW）访问密钥是否与最佳做法一致？
解决方案使用加密机制时，秘密存储是一个常见问题。
pgcrypto不提供密钥存储，您可以自由使用
将密钥存储在另一个数据库中（如果由同一个DBA管理）不能提供比DBA更高的安全性
理想情况下，您将密钥存储在安全的保管库中，并从应用程序中请求它以构造查询。当请求通过pg_stat_activity 中的 select *运行时，仍可从DBA中看到。
您可以设置通过 set session my.vars.cryptokey ='secret'; 进行SQL会话广泛使用的关键字，然后将其用于查询中，语法如下： PostgreSQL规则：current_setting（'my.vars.cryptokey'）:: text
从应用程序角度来看（几乎）是透明的可能有助于将 secure_column 转换为带有会话存储密钥的解密函数调用。对于插入，将需要一个预插入触发器。
I need to encrypt some columns in a PostgreSQL 9.6 database. The data being encrypted is inherently sensitive; however, the data are not passwords or other authentication credentials. This data will need to be decrypted for statistical analysis and consumption by users.
After reading several questions and answers:
... and considering these comments:
... it seems the biggest problem with using the pgcrypto module is the storage of keys in the same database.
Is it consistent with best practices to store the key in a different database and access it via a foreign data wrapper, such as Postgresql_FDW?
Secret storage is a common issue when using crypto mecanisms.
pgcrypto does not povide key storage, you are free to store the key where you want and protect it as you can.
Storing the key in another database, if managed by the same DBA does not provide much security as DBA may access it the same way.
Ideally, you would store the key in a secure vault and request it from your application in order to construct the queries. It will still be visible from DBA while the request is running through select * from pg_stat_activity.
You may set the key for a SQL session wide use through set session my.vars.cryptokey = 'secret'; then use it into your queries with the following syntax : current_setting('my.vars.cryptokey')::text
To be (almost) transparent from the application point of view, PostgreSQL rules may help for translating secure_column to the call to decrypt function with the session stored key. For inserting, a pre-insert trigger would be required.
这篇关于PostgreSQL：使用pgcrypto加密列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！