PostgreSQL Schema Design

In your phrasing, a database (really a database INSTANCE) is similar to a whole server itself, and carries with it overhead such as memory allocation, etc.

When you put multiple schemas in a single instance, the resources allocated to the database instance can be shared, whereas if you have every app in its own instance, you can’t share things like working memory between them because they are in separate processes.

Use CREATE TABLE AS SELECT to have cheap copying from one schema to another.

By host, I mean a server. A host can have many database clusters. Usually it has just one. To have more than one, you would have to have more than one PostgreSQL instance running, each listening on a separate port. The default port is 5432. I don't recommend more than one cluster per host. I just mention it as possible.

A cluster can have many databases.

A database can have many schemas.

A schema can have many objects. By object, the most familiar is a table, but there are other kinds of objects: views, functions, custom types, sequences. An object cannot exist directly in the database. It must be part of a schema. The default schema is called "public". Traditionally in other databases, there was a schema for each user. So if jdoe logs in, his default schema is also called jdoe. In fact in other databases this is the only schema a user can have. You cannot make more schemas and name them whatever you wish.

The advantage of a schema over a database is that you can make a query that uses objects in different schemas.

  select *
  from schema1.table1
      join schema2.table2 on table1.col = table2.col

I have gotten a long way, with many applications over many years, with one host, one cluster, one database, and many schemas.

In some RDBMSes "schema" also refers to a namespace that qualifies type, table, view, materialized table, and function names -- this qualifier is optional, so you don't always see it, but all obtjects' fully qualified names include the schema name.

This overloading of the word can cause confusion, naturally, but once you understand it it's easy enough to keep it straight.

"Let me show you my schema" -> generic sense of the word.

"Utilities live in the 'util' schema, while the business logic lives in the 'public' schema" -> the second sense of the word given above.

So I have a few practices related to PostgreSQL schemas that I like to employ when I'm designing a PostgreSQL database. I tend to work in enterprise business systems with larger database structures than many here I think, so to be sure, what I typically do isn't for everyone, but maybe it'll help you better understand the spot where this concept lives.

First, most database objects, like tables and functions, must live in some schema in PostgreSQL. By default the schema is 'public'. I actually avoid using the public schema in favor of using schemas I create. The reason I do this is because, some extensions and such will also define objects in the public schema and I don't want to confuse stuff from third parties with stuff I manage. By always creating at least one clearly dedicated schema for the objects I create, I know what software I'm managing vs. just got thrown into the dumping ground. I do this on all size databases I create.

If the database is sufficiently complex, I may create different schemas for different "modules" that I define in the software. It helps me to understand, in the database, where the boundaries are. For example, I work with an off-the-shelf ERP system. When I create extensions to this system, I will create a new database schema to hold the various tables and database functions required.

I may use PostgreSQL schemas to logically delineate different security concerns; I'll usually do this in conjunction with different authorization roles that have schema level permissions. I do this more often when there's need to define a database function "API" to the database. I'll put the data into a data schema, but then I'll create, say, two additional schemas... one to hold private/internal database functions and another to hold the "public" facing API. I can then have those applications/integrations that should always use the database function driven API to use a database role which only gives them access to functions defined in that schema. I don't mean to suggest that this is a "sufficient" security mechanism, but is part of a broader strategy of security in depth.

Anyway, some ideas to go along with the definitions.

Traditionally the applicaiton handles authentication and authorization. Then it connects to the database as a generic user account, with access to everything. It lets the signed-in user do only the things that he should, through carefully crafted queries.

If instead you registered each end user as a database user, then that makes certain things easier (and perhaps other things harder). For one thing, the current user is available as a variable, current_user. You could create database views that use that variable, instead of having to feed it into each query as a parameter (a small convenience, I admit). You could make the current_user the default value for columns like created_by. You could update columns like last_edited_by purely through triggers. In general, you could write more of your logic in pure SQL, instead of a tight coupling of SQL and your application.

If you're not used to doing it this way, it feels dangerous, and rightly so. But don't think it's actually harder than securing it in application code, just less familiar. A new user in PostgreSQL has no rights, only what is granted through commands.

For ease of maintenance, you can gather users into groups. They are both called roles. A role that has a login is generally a user. You can add role to another role, though, and so the second role acts like a group. Then you can grant and revoke rights to the group, instead of having to issue commands to change the rights one user at a time. There is even a function to help check role membership, pg_has_role.

Basically, create three tables: user, user_role, role_permission. Each user can have one or more role, and each role has one or more permissions. Permissions could be things like "view_admin_panel" or even granular like "view_project_with_id_5".

Then, you can create a row level security policy that does the right look up in these tables. I've not run this in a production system yet, but did successfully build out a proof of concept that worked. Performance seemed reasonable.