Clarify PostgreSQL user permissions in the documentation

Questions
1

Your question

Stalwart v0.16.3 (Docker)

I have the following PostgreSQL setup:

CREATE DATABASE stalwart;
CREATE ROLE "user1" WITH LOGIN PASSWORD 'pass1';
GRANT ALL ON DATABASE stalwart TO "user1";
GRANT ALL ON SCHEMA public TO "user1";

On an emty database a bunch of tables, etc., are created by the Stalwart app. Then a new user is created in the following way:

CREATE ROLE "user2" WITH LOGIN PASSWORD 'pass2';
GRANT ALL ON DATABASE stalwart TO "user2";
GRANT ALL ON SCHEMA public TO "user2";

When Stalwart is restarted with user2 there are constant DB permissions issues. Please, clarify how the PostgreSQL database and user should be created in the documentation.

I understand that topics in this category are triaged by a bot first but a human reply will follow up. If I’d prefer a human-only reply, I’ll add the no-ai tag to my topic.

on

2

The current docs page for the PostgreSQL backend (/docs/storage/backends/postgresql) only covers connection parameters — it does not document the required PostgreSQL role/permission setup. Your observation is a valid documentation gap.

That said, the root cause of your issue is almost certainly a PostgreSQL table ownership problem. When user1 first starts Stalwart and it auto-creates the schema, all the tables, sequences, and indexes are owned by user1. When user2 connects, even with GRANT ALL ON SCHEMA public, it still lacks access to those already-existing objects because GRANT ALL ON DATABASE and GRANT ALL ON SCHEMA do not retroactively grant privileges on existing tables and sequences.

To fix this, after the schema has been created by user1, run the following as a superuser or as user1:

-- Grant privileges on all existing tables
GRANT ALL ON ALL TABLES IN SCHEMA public TO "user2";

-- Grant privileges on all existing sequences (needed for auto-increment/serial columns)
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO "user2";

-- Optionally, make future objects automatically accessible too
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO "user2";
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO "user2";

The cleanest long-term solution for a shared setup is to have both users share a common role that owns the schema objects, or to let the same DB user own all objects. The ALTER DEFAULT PRIVILEGES approach is the standard PostgreSQL way to ensure any future tables Stalwart creates are also accessible.

Since this isn’t documented in the current Stalwart PostgreSQL docs, it would be worth opening a documentation issue on the GitHub repo so the setup prerequisites can be clarified — a human community member or staff member may also follow up here with the canonical recommendation.

This is an automated reply from the Stalwart Help Bot. Other community members may follow up if this answer is incomplete or wrong. If you found it useful, mention @helpbot in any reply to ask follow-up questions and the bot will respond again.

3

Transferred GitHub issue here.

4

This is a Postgres ownership thing rather than a Stalwart issue: GRANT ALL ON SCHEMA / DATABASE doesn’t propagate to objects already created by another role. user1 created the tables and owns them; user2 has connect rights to the database but no rights on those tables.

For a single-app setup the simplest path is to use one role for both schema creation and ongoing access. If you really want two roles (say a migration role and a runtime role), the grants you’d need on top of yours are:

  GRANT ALL ON ALL TABLES IN SCHEMA public TO "user2";
  GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO "user2";
  ALTER DEFAULT PRIVILEGES FOR ROLE "user1" IN SCHEMA public GRANT ALL ON TABLES TO "user2";
  ALTER DEFAULT PRIVILEGES FOR ROLE "user1" IN SCHEMA public GRANT ALL ON SEQUENCES TO "user2";

The ALTER DEFAULT PRIVILEGES lines matter because Stalwart will create new tables (e.g. on migrations or new feature rollouts), and without that line those new tables would also be owned by user1 and inaccessible to user2.

Agreed the docs should call this out; the current page just says “create a database and a user”, which is true but elides the two-role case. I’ll get a note added.

1 Like