How to share a table between multiple Postgresql d

Question

My web app has multiple deployments -- each is a unique site with a unique URL.
Each deployment has different data, UI, etc. but a very similar Postgresql database structure (with PostGIS). The databases all live on the same DB server. I would like users from 1 deployment to be able to log in to all other deployed apps without having to re-register.

What I want is a single "users" table that is shared across multiple app databases. Any user who registers in one app should be recognized by all other apps. Is this possible with Postgres? Are schemas the way to do this?

Answer 1

The answer of filiprem is awesome.. However, I found that I needed to set up the paths also for the users app01, app02:

ALTER USER app01 SET SEARCH_PATH TO "$user",app;
ALTER USER app02 SET SEARCH_PATH TO "$user",app;

Also if you need that the role app to have access to the tables in the schemas app01, app02, you would need this code:

-- grant all future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA app01 GRANT SELECT ON TABLES TO app;

-- grant all existing tables
GRANT SELECT 
ON ALL TABLES IN SCHEMA app01
TO app

Instead of SELECT you can have other privileges. The same for user app02.

Update: In order to be able for the user app to select rows in app01 schema, it needs at least USAGE privilege for the schema app01 (along with table privilege SELECT defined above):

GRANT USAGE ON SCHEMA app01 TO app; 

Answer 2

Take a look at dblink.

Answer 3

Yes, schemas are the solution. Use a single PostgreSQL cluster, with a single database.

Create a group for all of app users:

CREATE ROLE app;

Create global "app" schema, where all global shared applications tables will live.

CREATE SCHEMA AUTHORIZATION app;
CREATE TABLE app.objects ( objectid int PRIMARY KEY );
ALTER TABLE app.objects OWNER TO app;

Create separate user (with no superuser rights) for each of deployments:

CREATE USER app01 IN ROLE app;
CREATE USER app02 IN ROLE app;

Optionally, instead of IN ROLE app, you can grant explicit rights for these users on selected app objects:

GRANT USAGE ON SCHEMA app TO app01;
GRANT SELECT on app.objects TO app01;

Create private schemas, where deployment-dependent tables will live:

CREATE SCHEMA AUTHORIZATION app01; 
CREATE SCHEMA AUTHORIZATION app02;

Now you have a private schema for every application deployed; but at the same time you have shared access to global data.

What's nice, is that application does not have to be schema-aware. SELECT * FROM froobles will by default resolve to SELECT * FROM app01.froobles, if you are connected as app01 user. You do not have to specify schema name.

As an extra measure, you can use table inheritance to extend global objects on per-deployment basis:

CREATE TABLE app01.objects (
  localattr1 int,
  localattr2 text
)
INHERITS ( app.objects );