I am architecting a solution for an interface between a highly secure
production environment and a corporate network which involves transfer of
records from Oracle and SQL Server through an intermediary "firewall DB", a
Postgres Instance, to SQL Server/ Oracle. I anticipate that there will
either be direct database links or jdbc connections and stored procedures to
pass data.

Does anybody have a view on likely issues I may have in practice, should I
really be looking at existing commercial technologies or is PostgreSQL the
right technology?

Patrick Carroll wrote:
> I am architecting a solution for an interface between a highly secure
> production environment and a corporate network which involves transfer of
> records from Oracle and SQL Server through an intermediary "firewall DB", a
> Postgres Instance, to SQL Server/ Oracle. I anticipate that there will
> either be direct database links or jdbc connections and stored
> procedures to
> pass data.
>
> Does anybody have a view on likely issues I may have in practice, should I
> really be looking at existing commercial technologies or is PostgreSQL the
> right technology?

I'm not sure what PostgreSQL is doing for you here, unless you need some
sort of "buffer" to cope with network bandwidth problems.

Why not just have a secured application sitting in the dmz/on firewall
and connect to both sides transferring for you?

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

There has got to be some persistence, there will be a lot of tables and
metadata and it may have to handle validation requirements for other apps
doing secure file transfer and a bespoke secure http proxy and it's going to
be a speculative buffer against protocol based worms crossing into the
production environment.

On 7/5/07, Richard Huxton <dev@archonet.com> wrote:
>
> Patrick Carroll wrote:
> > I am architecting a solution for an interface between a highly secure
> > production environment and a corporate network which involves transfer
> of
> > records from Oracle and SQL Server through an intermediary "firewall
> DB", a
> > Postgres Instance, to SQL Server/ Oracle. I anticipate that there will
> > either be direct database links or jdbc connections and stored
> > procedures to
> > pass data.
> >
> > Does anybody have a view on likely issues I may have in practice, should
> I
> > really be looking at existing commercial technologies or is PostgreSQL
> the
> > right technology?
>
> I'm not sure what PostgreSQL is doing for you here, unless you need some
> sort of "buffer" to cope with network bandwidth problems.
>
> Why not just have a secured application sitting in the dmz/on firewall
> and connect to both sides transferring for you?
>
> --
> Richard Huxton
> Archonet Ltd
>

Patrick Carroll wrote:
> There has got to be some persistence, there will be a lot of tables and
> metadata and it may have to handle validation requirements for other apps
> doing secure file transfer and a bespoke secure http proxy and it's
> going to
> be a speculative buffer against protocol based worms crossing into the
> production environment.

OK, in that case I would have:
- one PG database for each Oracle/MSSQL database being transferred
- two users for each database being transferred, one "inside", one "outside"
- each user is only allowed to connect to that database from a pre-set
IP address (via pg_hba.conf)
- lock down the permissions for each user appropriately

If you want to go the procedure/function route to access each DB you can
define your DBs as SECURITY DEFINER for operations that need "superuser"
permissions.

I'd use Perl to hook it all together, but you mentioned jdbc, so
presumably you'll be going for Java.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

I would use Java and possibly OLEDB/ADO/ADO.Net form the public side as the
consumers of this info will want it nice and easy and I dont want to spend
hours developing a bespoke interface but put the onus on the consumers of
the service to get what they need.

Thanks for your comments, much appreciated.

On 7/5/07, Richard Huxton <dev@archonet.com> wrote:
>
> Patrick Carroll wrote:
> > There has got to be some persistence, there will be a lot of tables and
> > metadata and it may have to handle validation requirements for other
> apps
> > doing secure file transfer and a bespoke secure http proxy and it's
> > going to
> > be a speculative buffer against protocol based worms crossing into the
> > production environment.
>
> OK, in that case I would have:
> - one PG database for each Oracle/MSSQL database being transferred
> - two users for each database being transferred, one "inside", one
> "outside"
> - each user is only allowed to connect to that database from a pre-set
> IP address (via pg_hba.conf)
> - lock down the permissions for each user appropriately
>
> If you want to go the procedure/function route to access each DB you can
> define your DBs as SECURITY DEFINER for operations that need "superuser"
> permissions.
>
> I'd use Perl to hook it all together, but you mentioned jdbc, so
> presumably you'll be going for Java.
>
> --
> Richard Huxton
> Archonet Ltd
>