I’ll begin by saying that if it’s a choice between maintaining two separate postgresql instances for two unrelated teams, or a single postgresql instance there’s no competition. Postgresql best lends itself to one instance per team and attempting otherwise is shoving a round peg in a square hole. Postgres is not microsoft sql, you’re not being charged by the number of cores each instance has access to. In the event only one ‘server’ is accessible for database use, there’s not much added complexity in having multiple instances/services listening on different ports.

That said, let’s discuss an example where separating two teams’ databases into separate instances is not an option.

Red Team vs Blue Team

Let’s consider a case of two teams that are less than friendly with one another, and permissions must have clear boundaries to reduce risk for either team.

We are given the following combination of requirements for development databases and user permissions from red team and blue team.

postgres instance
├─ databases
│  ├─ red_db
│  │  └─ owner: red_tank
│  └─ blue_db
│     └─ owner: blue_team
└─ roles
   ├─ group: blue_team
   │  ├─ permissions: 
   │  │  └─ blue_db: database owner
   │  └─ users:
   │     ├─ church
   │     ├─ caboose
   │     └─ tucker
   ├─ group: red_team
   │  ├─ permissions: 
   │  │  └─ red_db:
   │  │     ├─ list schema objects
   │  │     └─ read all data
   │  └─ users:
   │     ├─ sarge
   │     ├─ donut
   │     ├─ grif
   │     └─ simmons
   └─ service account: red_tank
      └─ permissions: 
         └─ red_db: database owner

Fulfilling Customer Requests

Blue Team Customer Story

Blue Team tells us that they want the members of their group to all have the database owner permission so that they can all create schemas and tables, and each member can perform any data definition language (ddl) or data manipulation language (dml) as they so choose. They do not have their schema defined using a schema migration tool. They are also not using a service account to deploy code releases. As a result, Church tells us he wants each team member to be able to manually create and edit tables all user tables in red_db and save their changes as sql files in their git repository.

While this model potentially introduces chaos for their team and could result in each team member stomping on one another, they insist, and this is a dev database, so we’ll grant their request to potentially hurt themselves in their own confusion.

It’s worth noting here that postgresql uses an authorization model with singular object owners, whether that be a database, table or schema. A workaround is to set the database owner as a nologin group role such as blue_team, but this will require some additional logic later to meet Church’s ddl request.

Red Team Customer Story

Red team has a slightly different model. They tell us they are using sqlalchemy’s alembic to define their schema and keep track of changes. They want a service account named red_tank to have database owner permission so that their schemas and tables can be defined in their code and updated as a part of their application releases.

Red Team doesn’t need their users to create or modify tables, but they do want to be able to verify table definitions and read test data for all tables.

For their dev database, the service account used by their application to write to the database is the same one used to create tables, so we don’t need to separate out ddl vs dml permissions to separate service accounts.

Blue Team Initial Setup

Then we have an initial SQL to fulfill blue team’s request:

create role blue_team with nologin;
create role church with login password null;
create role caboose with login password null;
create role tucker with login password null;

grant blue_team to church, caboose, tucker;

create database blue_db with owner blue_team;

For our initial setup we’ll create the blue team, and set their blue_team group as database owner. That fulfills there request right? Now let’s set the developers loose. What could go wrong?

Red Team Initial Setup

Let’s start with the initial SQL to fulfill red team’s request:

create role red_team with nologin;
create role sarge with login password null;
create role donut with login password null;
create role grif with login password null;
create role simmons with login password null;
create role red_tank with login password null;

grant pg_read_all_data to red_team;
grant red_team to sarge, donut, grif, simmons;

create database red_db with owner red_tank;

Let’s update the postgres client auth config, in this case located at /etc/postgresql/17/main/pg_hba.conf. Our users are going to use ipv4 and pam login with tls. Let’s add this line to the config so they can connect from any source ipv4 address:

#   TYPE      DATABASE        USER        ADDRESS     METHOD
hostssl     all             all         *.*.*.*/*   pam

Set ’em loose

Blue Starts Development

Caboose connects to blue database and creates pagila schema and tables to begin his development. Church connects to blue_db. He can list caboose’s tables, but he can’t read or modify them. ERROR: permission denied for schema pagila What’s happening?

We look at the schemas and now we can see that because Caboose created the pagila schema and didn’t change the permissions or set schema owner back to the group, only he has ddl permissions for pagila tables.

blue_db=> \dn
      List of schemas
  Name  |       Owner
--------+-------------------
 pagila | caboose
 public | pg_database_owner

We could instruct caboose to modify the owner of his tables and schema back to blue_team and to remember to modify the owner of future objects, but have you met Caboose? Caboose is not going to figure that out. Let’s update the schema owner, alter default privileges and we’ll check in on them later:

alter schema pagila owner to pg_database_owner;
grant all ON all tables in schema pagila to pg_database_owner;
alter default privileges in schema pagila grant all ON tables TO pg_database_owner;
alter default privileges in schema pagila grant all ON sequences TO pg_database_owner;
alter default privileges in schema pagila grant all ON functions TO pg_database_owner;

That seems to solve blue team’s problems for now. They go about their merry way with no complaints.

Caboose decides he is annoyed with having to understand how to join the pagila tables together to get the information he needs for his giant report. He makes a table called pagila.the_whole_thing that is a cartesian project of each of his previous tables. We tell him that’s not really how any of this works, but he barrels ahead, and that’s sarge’s problem.

Red Starts Development

Red team kicks off their application release with no complaints. Their service account is able to connect to red_db, and creates tables and inserts data without a hitch.

Grif connects to red_db using pgAdmin to check up on the release. He sees another database, and being of a curious nature, immediately starts poking around and realizes he can select * from a table called blue_db.pagila.the_whole_thing.

Grif isn’t quite sure what he’s looking at, and shows Sarge who immediately bursts out laughing.

Consequences

Sarge is the competitive sort, and sends out an email to the postgres admins and cc’s the CTO to take Church down a peg:

Dear morons,
No wonder your web app takes 2 minutes to load anything, you’re reading all data from a single table.
Red team has read access to all blue_db tables listed in pg_catalog.pg_tables.
Sincerely,
Sarge

Oh, right. the administrators didn’t prevent red team from accessing the blue_team’s blue_db, and pg_read_all_data is a global permission. When postgres creates a user, connect on all db’s is granted by default. We also set the hba config to allow any user (*) to connect to any database (*).

Remediation

Database Connection Security Remediation

First, the hba config needs to be broken down by group access to prevent connection of red_team to blue_db

#   TYPE      DATABASE        USER              ADDRESS     METHOD
hostssl     red_db          +red_team         *.*.*.*/*   pam
hostssl     blue_db         +blue_team        *.*.*.*/*   pam

Postgres also grants connect on all databases by default during user creation. We need to revoke connect for each db and each user. Ew cross join? Are you making a cartesian product? Yeah, I know. It is a tiny cartesian product though, and we don’t need a nested for loop for this which comes to the same thing.

do
$$
declare
    r record;
begin
   for r in select
      u.rolname,
      d.datname
    from pg_roles u
    cross join pg_database d
    where u.rolname !~ 'postgres'
    and u.rolname !~ '^pg_'
  loop
    EXECUTE format('revoke connect on database %I from %I', d.datname, u.rolname );  
  end loop;
end;
$$

We are then able to explicitly name which group can connect to each db.

grant connect on database red_db to red_team;
grant connect on database blue_db to blue team;

Blue Team personel remediation

Church also decides to fire caboose for his poor database design.

drop role caboose;

ERROR:  role "caboose" cannot be dropped because some objects depend on it
DETAIL:  56 objects in database blue_db

Right. We granted other team members access to the tables caboose created, but he is still the object owner. reassign owned by caboose to pg_database_owner; enables us to drop Caboose’s role.

We ask Church if we can set a default logon trigger ⊕There is some risk when using logon triggers. Should the logon trigger fail outright for any reason, no user can connect to that database. This can be resolved by temporarily setting event_triggers to false in postgresql.conf to connect and disable the offending logon trigger. This would require pg_ctl reload or pg_ctlcluster 17 main reload.

so that all new objects created in blue_db are owned by pg_database_owner which maps to blue_team. Church shrugs and says that works, since he doesn’t want anyone to be the sole owner of anything anyway. Especially Caboose.

We create a logon trigger on blue_db to reassign ownership for blue_team members back to the group:

BEGIN;
CREATE OR REPLACE FUNCTION blue_on_login_proc()
    RETURNS event_trigger AS
$$
declare
    role text;
  begin
    for role in select m.rolname
      from pg_catalog.pg_roles m
      join pg_catalog.pg_auth_members pam on (pam.member = m.oid)
      left join pg_catalog.pg_roles r on (pam.roleid = r.oid)
      where r.rolname = 'blue_team'
    loop
      EXECUTE format('reassign owned by %I to pg_database_owner', role );
    end loop;
  end;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER blue_on_login_event
    ON login
    EXECUTE FUNCTION blue_on_login_proc();

ALTER EVENT TRIGGER blue_on_login_event ENABLE ALWAYS;
COMMIT;

Postgres System View Permissions

The last security issue, and perhaps the most complex one to solve, is that red_team had read access to pg_catalog tables and views that enable them to list global objects like table-spaces, users, groups, and their permissions on other databases. As the name implies, global objects does not include objects such as tables or schemas which are specific to a given database.

This is a default permission and there’s no simple way around this without breaking some functionality. For example revoking permissions to pg_catalog will break psql shortcuts like \drg+ \l+ and also break functionality in IDE’s which rely upon these pg_catalog tables and views for object explorers.

red_team being able to list blue_db user permissions is beyond their need-to-know, and in some situations this would be untenable.

With that out of the way, let’s get ready to create a horrible user experience for the sake of security. The simplest solution is to revoke select from public on global object related tables (for each database):

revoke select on pg_catalog.pg_authid from PUBLIC;
revoke select on pg_catalog.pg_database from PUBLIC;

blue_team and red_team are both complaining they can’t use the object explorer in their IDE’s.

This is the deal breaker that makes multi-tenant postgres less attractive than creating one instance per team.

If this were a table outside pg_catalog we could use row level security. But attempting to enable RLS will throw errors, even if performed by the postgres user:

alter table pg_catalog.pg_authid enable row level security;
ERROR:  permission denied: "pg_authid" is a system catalog

alter table pg_catalog.pg_database enable row level security;
ERROR:  permission denied: "pg_database" is a system catalog

There’s a good reason for this. Attempting to edit the system catalog tables is a good way to brick your database. You could create separate views that each team has more appropriate read access to, but one would still have to modify what views and tables an IDE uses to populate the object explorer.

TL;DR

If users being to list the permissions of another team or organization is a deal breaker, multi-tenant postgres is not for you, use separate postgresql instances.

Otherwise, the below strategies enable a team to manage authorization with boundaries between teams:

• use groups for role based access control
• configure hba to map groups to databases the users can connect to
• grant specific schema permissions needed to groups based on day to day operations
• alter default schema privileges
• create login triggers

Real world take-away: The biggest complaint from developers will often be that they want additional permissions like superuser and createdb because they don’t want to have to wait to be granted select on each new schema or table, etc. Postgres authorization options are most granular at the database and schema level, so even createdb and pg_database_owner can empower a developer to hurt themselves (and others) in their own confusion. Postgres permissions, triggers, and hba must be understood thoroughly by administrators to strike the balance of not creating blockers for one dev team while still protecting the database of another.