Re: Complex database infrastructure - how to?

On 2012-06-30, Edson Richter <edsonrichter(at)hotmail(dot)com> wrote:
> I've a plan that will need a complex database infra-structure using
> PostgreSQL 9.1.
> I've seen similar setups using MS SQL Server and other databases, but
> all of them support cross database queries (also easy to implement with
> materialized views).
>
> - Administrative database: have few tables, used to administer the
> infrastructure. This database have some tables like "users", "groups",
> "permissions", etc.
> - Application databases: have app specific data.
>
> 1) One main Administrative application that will have read/write
> permissions over the Administrative database.
> 2) Each application will have to access the application database (for
> read/write), and the administrative database (for read only - mainly to
> maintain the record references to the users that created objects, and so
> on).
> 3) All applications are written in Java, using JPA for persistence.
> 4) All databases are running on same server, and all of them have same
> encoding.

> Is there any ohter way to do that? Please, adivce!

Schemas,
give each application a different username and a matching schema name
with matching ownership the default postgres schema search path will
cause tables created by each application user accout to be segregated
into the schema with no extra work.

"Admin" schema tables can be accessed as eg: admin.tablename
or if you put them into the schema "public" or modfy the search path
just by tablename.

http://www.postgresql.org/docs/9.1/static/ddl-schemas.html
AIUI postgresql schems are almost the same thing as mysql databases.

--
⚂⚃ 100% natural