On 2012-06-30, Edson Richter <edsonrichter@xxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
