Re: Oracle vs. PostgreSQL - a comment

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