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(at)endpoint(dot)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(at)gmail(dot)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(at)gmail(dot)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

>
> []s
>
> Márcio
>
> On Wed, Dec 21, 2016 at 10:57 PM, Hannu Krosing <hkrosing(at)gmail(dot)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
http://pgblog.wi3ck.info