pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSchema vs Independant Databases, ACLS,Overhead,pg_hba.conf
2+ messages / 2 participants
[nested] [flat]
* Schema vs Independant Databases, ACLS,Overhead,pg_hba.conf
@ 2006-02-24 20:03 Eric Lauzon <eric.lauzon@abovesecurity.com>
0 siblings, 1 reply; 2+ messages in thread
From: Eric Lauzon @ 2006-02-24 20:03 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Schema vs Independant Databases, ACLS,Overhead,pg_hba.conf
@ 2006-02-25 16:03 Alvaro Herrera <alvherre@commandprompt.com>
parent: Eric Lauzon <eric.lauzon@abovesecurity.com>
0 siblings, 0 replies; 2+ messages in thread
From: Alvaro Herrera @ 2006-02-25 16:03 UTC (permalink / raw)
To: Eric Lauzon <eric.lauzon@abovesecurity.com>; +Cc: pgsql-performance
Eric Lauzon wrote:
Hi,
> 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
This is certainly possible to do using GRANT and REVOKE on the schemas;
no need to fool with pg_hba.conf. You can of course create groups/roles
to simplify the assignment of privileges, as needed.
Apart from the much more efficient queries (i.e. using cross-schema
queries instead of dblink), I don't think you're going to see much
change in performance, because most things like WAL and shared buffers
are shared among all databases anyway. You'd save a bit by not having
multiple copies of system caches (pg_class cache, etc), but I wouldn't
know if that's going to be very noticeable next to the primary
improvement.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2006-02-25 16:03 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-02-24 20:03 Schema vs Independant Databases, ACLS,Overhead,pg_hba.conf Eric Lauzon <eric.lauzon@abovesecurity.com>
2006-02-25 16:03 ` Alvaro Herrera <alvherre@commandprompt.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox