I have a multi-tenant application where the Postgres DB schemas are set up as follows:

  • _admin (tables that are invisible in the UI to standard users but need to be read-accessible, common across all tenants)
  • _master (table defs only, no keys or other constraints)
    • customer (columns...) CHECK false NO INHERIT
    • etc (columns...) CHECK false NO INHERIT
  • acme (primary tenant-specific schema for tenant "acme")
    • customer INHERITS _master.customer
  • acme_oms (order mgmt schema for tenant "acme")
    • etc INHERITS _master.etc
  • acme_xx (where xx is another schema for tenant "acme")
  • widgets_inc (primary tenant-specific)
  • widgets_inc_oms (etc)

The _master schema contains all table definitions that will be inherited in the tenant-specific schemas. This works well. I can place CHECK false NO INHERIT on all these table definitions to ensure that nothing is ever inserted into them.

I'd like to set up the roles this way:

  • db_admin superuser (has access to everything including schema modification and replication)
  • db_acme (has transparent read access to _admin, read-write access to everything in all schemas starting with "acme", but no schema modification privileges, no access at all to other tenant schemas)
  • db_widgets_inc (etc...)

Two questions:

  1. What would the CREATE ROLE code look like for the tenant-specific users?
  2. Would restricting read access to the _master tables still allow the tenant-specific users to access the data in their own schema tables?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.