Hello all,
I have this small set of questions that i have been looking to ask and
here it comes.
Lets imagin we have a setup where we have multiple databases instances
on one server,
and we are starting to use more and more cross databases queries [query
unions] using dblink [multiple context but
using the same templated database schema]. Most of the operations
performed on those instance
do not need to be done in the same context , but as requirement comes
for more and more inter context
data analysis we are starting to see some issue with the current
databases layout.
Thats where we asked our self what we could do. Without mutch changes it
wouldn't be so hard to merge
lets say 4 context into one [4 database using the same template into 1]
, and we are sure that those cross databases
query would get major speed improvement.
But now we have some issues arround having 1 database context for whats
is actualy contained in 4 separated ones.
Having one context implies either 1 database with 4 SCHEMA OR 1 database
with 1 schema [still trying to see where we want to go]
The first issue we have is with pg_hba Acls where you can't define
specific ACLS for SCHEMA , where
if we want to have a first line of defense against data segregation we
must
1.Create "ala context" users for schema and per schema defined
acls ...[hard to manage and a bit out of Security Logic]
or
2.Just create as many user as needed and implement underlying
acls ..[also hard to manage and out of Security Logic]
Now that the issue is that we can't implement a per connection user/ACL
pair , would it be a good idea to implement
Schema ACLS and mabey pg_hba.conf Schema acls
I mean
NOW:
local database user auth-method [auth-option]
Could be :
local database/or database.schema user auth-method [auth-option]
^^^
database could be considered as database.public
Now if we would have that kind of acces Control to postresql i would
like to know what is the
benefices of using multiple schema in a single database in term of
Postgresql Backend management
[file on disk,Index Management,Maintenance Management],beside the
possibilites to do quick and
efficient cross databases querys,
[Note those cross databases queries are not done by a users that need
per Schema/Databases
ACLS but by a "superior user" so we do not need to consider that for
this issue]
Is it really more efficient of having 4 schema in one databaes , 1
database with one schema or having 4 databases?
I think i can have some of those answers i think but i want behind the
scene point of view on those.
Thanks you all in advance, have a nice week-end
-elz
AVERTISSEMENT CONCERNANT LA CONFIDENTIALITE
Le present message est a l'usage exclusif du ou des destinataires mentionnes ci-dessus. Son contenu est confidentiel et peut etre assujetti au secret professionnel. Si vous avez recu le present message par erreur, veuillez nous en aviser immediatement et le detruire en vous abstenant d'en faire une copie, d'en divulguer le contenu ou d'y donner suite.
CONFIDENTIALITY NOTICE
This communication is intended for the exclusive use of the addressee identified above. Its content is confidential and may contain privileged information. If you have received this communication by error, please notify the sender and delete the message without copying or disclosing it.
