On 5/6/20 3:33 μ.μ., Ravi Krishna wrote:
Plus PG does not directly support cross database queries using 3 part name,
something
sqlserver excels at.
Maybe because SQL server does not have real databases but schemas instead ?
This sucks security wise.
SQLServer has real databases with its own transaction log files. You can
restore individual databases in a cluster.
They also have schemas which are not same as users (Oracle treats users and
schemas same).
Ok, I never meant SQL Server does not have real databases, I meant it handles
databases as top level schemas.
For security, there is grant connect to the DB and further filtering based on
schema.
PostgreSQL has stronger protection at connection level, via pg_hba.conf .
PostgreSQL also supports db-level GRANTs .
In MS SQL server if you need an additional DB for maintenance tasks or to act as an intermediate bridge (like in our case) with write access on it, then automatically you write access to the main
schema (ok DB in MS SQL terms). (and need further REVOKES to fix security). This (security-wise) is just poor.
So the cross db joins come with a price.
I find SQLServer implementation pretty strong in this.
The only time this can be a problem is when few databases failover in a
mirrored environment (streaming replication in PG speak).
Then suddenly 3 part names would fail if the remote DB is no longer primary. My
experience with SQLServer is badly dated. Last
time I worked was SS2008. I believe in later versions they solved this problem
by the failover group concept which failovers all
inter-related databases at one go.
BTW Mysql treats databases and schemas as same (that's what it was few years
ago)
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
