User Management in HA instalation. #1787
-
HA installation user/rights management.I realized this is more of pure Postgresql question, nonetheless, I think it might be useful to others. We have stateless application, one master and multiple read only replicas. We use logical replication and this works great since we control exactly which table we want to distribute to replicas. We need to update/change/add/remove users and access rights fairly frequently and we would like to replicate user/roles/access rights along with the previously mentioned table. However I have not found any information on how to do this. I know Streaming replication would solve this problem, however it would also push all database objects and create an identical copy, we would like to avoid doing this. Another option might be creating an intermediate Postgresql instance with only the objects we need to serve as the new master replica... Thanks for your ideas and comments. Eric. |
Beta Was this translation helpful? Give feedback.
Replies: 1 comment 2 replies
-
|
I suggest you keep the users and privileges in your own tables, for which you enable logical replication. You probably already do. Then manage the SQL roles/grants through triggers on those tables. If you use the same triggers on the subscriber-side, all those changes should be replayed the same way. |
Beta Was this translation helpful? Give feedback.
-
|
Did that work for you? |
Beta Was this translation helpful? Give feedback.
-
|
Yes, thank you for your suggestion. |
Beta Was this translation helpful? Give feedback.
I suggest you keep the users and privileges in your own tables, for which you enable logical replication. You probably already do. Then manage the SQL roles/grants through triggers on those tables. If you use the same triggers on the subscriber-side, all those changes should be replayed the same way.