sql - Heroku Postgres 数据库的只读权限

Question

我的应用程序有一个 Heroku Postgres 数据库。我可以使用 Heroku 提供的 DATABASE_URL 轻松访问 psql shell

psql $(heroku config:get DATABASE_URL -a my_app)
psql (9.6.1)
SSL connection (protocol: TLSv1.2, cipher: ECDSE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

d5i032ahpfiv07=> 

默认情况下，该用户似乎具有更新和删除表的完全访问权限

d5i032ahpfiv07=> SELECT 
  table_name, 
  string_agg(privilege_type, ', ') as privileges
FROM information_schema.role_table_grants 
WHERE table_schema = 'public'
  AND grantee = current_user
GROUP BY 1
;

            table_name             |                          privileges
-----------------------------------+---------------------------------------------------------------
 articles                          | INSERT, TRIGGER, REFERENCES, TRUNCATE, DELETE, UPDATE, SELECT
 comment_flags                     | TRIGGER, INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES
 comment_likes                     | TRUNCATE, REFERENCES, TRIGGER, INSERT, SELECT, UPDATE, DELETE
 comments                          | INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER
 communities                       | TRIGGER, REFERENCES, TRUNCATE, DELETE, UPDATE, SELECT, INSERT
 .....

我喜欢在数据库中设置“只读”用户，这样人们就可以查看这些数据而不必担心运行“DROP TABLE”或其他一些破坏性命令。

如何在 Heroku Postgres 上设置只读用户？

谢谢!

Answer 1

创建一个 Heroku Postgres Follower Database

A database follower is a read-only copy of the leader database that stays up-to-date with the leader database data. As writes and other data modifications are committed in the leader database, the changes are streamed, in real-time, to the follower databases.

然后只需针对关注者运行您的分析、数据剪辑和其他只读应用程序。这是一个非常标准的配置，可以保护您的主数据库，并增加了性能优势:您可以在不影响面向用户的应用程序的情况下使用查询(通常是密集型且具有不同的缓存配置文件)对其进行打击。