Обсуждение: [pgsql-cluster-hackers] Streaming replication for security
Hello ppl,
My name is Márcio, from Brazil and I need help in understanding the streaming replication process.
I have a PostgreSQL data base hosted in an internal server. I need to replicate this database on Heroku for read only access, but I need to keep this Heroku instance synced with my internal server... I don't want to expose this PostgreSQL internal server to the Web...
What I understood in https://wiki.postgresql.org/wiki/Streaming_Replication is that standby will read data from master and not the other way... In this case, I will need to expose the internal database server to the Web via TCP, right?
If so, there is a way to make the master send the data to standby?
Ty all!
Márcio
> case, I will need to expose the internal database server to the Web via > TCP, right? Technically, yes, but you still have pretty fine-grained control over who can come in to your database, via Postgres things like pg_hba.conf and good passwords, as well as OS-level things like iptables and port knocking. > If so, there is a way to make the master send the data to standby? You could certainly use traditional file-based log shipping to move the WAL files from the master to the standby, by any secure means you want, iincluding an intermediary system. However, this can be hard/impossible for SaaS things like Heroku and RDS, which don't allow complete file-level access. -- Greg Sabino Mullane greg@endpoint.com End Point Corporation PGP Key: 2529 DF6A B8F7 9407 E944 45B4 BC9B 9067 1496 4AC8
Вложения
Ty all...
I feel more comfortable with this approach... I'll learn more about ssh tunnel...
[]s
Márcio
On Wed, Dec 21, 2016 at 10:57 PM, Hannu Krosing <hkrosing@gmail.com> wrote:
On 12/17/2016 02:58 PM, Marcio Duarte wrote:
> Hello ppl,
>
> My name is Márcio, from Brazil and I need help in understanding the
> streaming replication process.
>
> I have a PostgreSQL data base hosted in an internal server. I need to
> replicate this database on Heroku for read only access, but I need to
> keep this Heroku instance synced with my internal server... I don't
> want to expose this PostgreSQL internal server to the Web...
>
> What I understood
> in https://wiki.postgresql.org/wiki/Streaming_Replication is that
> standby will read data from master and not the other way... In this
> case, I will need to expose the internal database server to the Web
> via TCP, right?
Maybe not web, but at least to the replica.
And otherways full PostgreSQL security applies., like requiring SSL
connection, fine-grained control of client addresses etc.
And, you can always set up an ssh tunnel if you are more comfortable
with this than with postgreSQL-s ssl.
And you can even initiate the tunneling SSH connection from the master :)
>
> If so, there is a way to make the master send the data to standby?
Not easily.
Cheers
--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
https://2ndquadrant.com/
On Thu, Dec 22, 2016 at 4:56 AM, Marcio Duarte <maethorin@gmail.com> wrote:
Ty all...I feel more comfortable with this approach... I'll learn more about ssh tunnel...
Note that ssh tunnel won't be an option for something like RDS. You simply cannot ssh into that DB server. Period.
Using client certificate based authentication would be my suggestion in this case.
Regards, Jan
[]sMárcioOn Wed, Dec 21, 2016 at 10:57 PM, Hannu Krosing <hkrosing@gmail.com> wrote:On 12/17/2016 02:58 PM, Marcio Duarte wrote:
> Hello ppl,
>
> My name is Márcio, from Brazil and I need help in understanding the
> streaming replication process.
>
> I have a PostgreSQL data base hosted in an internal server. I need to
> replicate this database on Heroku for read only access, but I need to
> keep this Heroku instance synced with my internal server... I don't
> want to expose this PostgreSQL internal server to the Web...
>
> What I understood
> in https://wiki.postgresql.org/wiki/Streaming_Replication is that
> standby will read data from master and not the other way... In this
> case, I will need to expose the internal database server to the Web
> via TCP, right?
Maybe not web, but at least to the replica.
And otherways full PostgreSQL security applies., like requiring SSL
connection, fine-grained control of client addresses etc.
And, you can always set up an ssh tunnel if you are more comfortable
with this than with postgreSQL-s ssl.
And you can even initiate the tunneling SSH connection from the master :)
>
> If so, there is a way to make the master send the data to standby?
Not easily.
Cheers
--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
https://2ndquadrant.com/
Jan Wieck
Senior Postgres Architect
Senior Postgres Architect