mattermost=# \du
List of roles
Role name | Attributes
-----------±-----------------------------------------------------------
mmuser | Superuser, Create role, Create DB, Replication, Bypass RLS
mattermost=# CREATE ROLE superuser WITH BYPASSRLS REPLICATION CREATEDB CREATEROLE SUPERUSER LOGIN PASSWORD ‘superuser_passwd’;
CREATE ROLE
mattermost=# ALTER DATABASE mattermost OWNER TO superuser;
ALTER DATABASE
mattermost=# ALTER DATABASE postgres OWNER TO superuser;
ALTER DATABASE
mattermost=# ALTER DATABASE template0 OWNER TO superuser;
ALTER DATABASE
mattermost=# ALTER DATABASE template1 OWNER TO superuser;
ALTER DATABASE
mattermost=# GRANT ALL PRIVILEGES ON DATABASE mattermost to mmuser;
GRANT
mattermost=# ALTER ROLE mmuser NOBYPASSRLS NOREPLICATION NOCREATEDB NOCREATEROLE NOSUPERUSER;
ERROR: permission denied to alter role
DETAIL: The bootstrap superuser must have the SUPERUSER attribute.
mattermost=# \du
List of roles
Role name | Attributes
-----------±-----------------------------------------------------------
mmuser | Superuser, Create role, Create DB, Replication, Bypass RLS
superuser | Superuser, Create role, Create DB, Replication, Bypass RLS
mattermost=# ALTER ROLE mmuser NOBYPASSRLS NOREPLICATION NOCREATEDB NOCREATEROLE NOSUPERUSER;
ERROR: permission denied to alter role
DETAIL: The bootstrap superuser must have the SUPERUSER attribute.
mattermost=# exit
could not save history to file “/root/.psql_history”: Read-only file system
/ # psql mattermost superuser
psql (17.2)
Type “help” for help.
mattermost=# ALTER ROLE mmuser NOBYPASSRLS NOREPLICATION NOCREATEDB NOCREATEROLE NOSUPERUSER;
ERROR: permission denied to alter role
DETAIL: The bootstrap superuser must have the SUPERUSER attribute.
mattermost=#
Hi systemsheep! Welcome to the forums! It looks like you’ve encountered a permissions issue while following the guide.
I recommend reviewing this GitHub issue for a similar problem: PostgreSQL permissions issue. You might need to use the actual bootstrap superuser (default postgres role) to adjust the attributes of mmuser.
If you continue to face issues, please let us know or check out the relevant documentation: Creating Non-Superuser Roles. Hopefully this helps get you back on track!
I tried to connect as postgres role but it does not exist.
/ # psql mattermost postgres
psql: error: connection to server on socket “/var/run/postgresql/.s.PGSQL.5432” failed: FATAL: role “postgres” does not exist
/ #
I created the database using the procedures outlined in https://docs.mattermost.com/install/install-docker.html. I don’t know if there is something that alters the default role settings in the docker .env file or compose files.
Do you have a direct link to the GitHub issue you mentioned? That link takes me to the general list of open GitHub issues and I couldn’t find one by the name you mentioned.
Yes, the Creating Non-Superuser Roles documentation was the guide I was following for this.
My research suggests that due to the way the Docker release initializes the database, mmuser is the bootstrap superuser. It is the only superuser role that exists before modification.
Is it true that the bootstrap superuser cannot have its superuser privilege revoked?
It is probably worth noting that the guide for creating a non-superuser was last updated in 2021. I imagine that there have been many updates to Mattermost, Postgres, and Docker since then, probably including changes that no longer allow this guide to work. What should I do instead?
If the points presented there are accurate is it even advisable to do as the non-superuser guide says? Was the default mmuser ever intended to be the bootstrap superuser?
–
I’ve just noticed that the guide is titled “Migrating existing superuser to a less privileged user” and begins with “Mattermost-docker used to use the initially created user while database initialization.” Was this guide only meant to update old installations of Mattermost to a new standard? I am creating a brand-new installation. Should I even be trying to follow this guide? What is it for?
I am very new to all of this. I may be missing concepts. Thank you for your patience.
I’ve been trying to figure out another way to accomplish creating a non-superuser for Mattermost to connect with. I was able to create a new user and rename it to become the one that Mattermost uses, however I need to know what privileges it needs to properly use the database.
INCOMPLETE GUIDE
1.) After launching the docker compose package, shut down Mattermost to avoid interference with the database. Use “docker ps” to find the container name/id. $ sudo docker ps $ docker compose stop MATTERMOST_CONTAINER_NAME/ID
2.) Attach to the database container after finding its name or id. $ sudo docker exec -it POSTGRES_CONTAINER_NAME/ID /bin/sh
3.) Connect to the database. # psql mattermost mmuser
4.) Create a new role to be later used by Mattermost, and temporarily given the ability to rename the current superuser. Then exit the database. CREATE ROLE temp WITH SUPERUSER LOGIN PASSWORD 'mmuser_password'; exit
5.) Switch to temp role. # psql mattermost temp
6.) Rename the bootstrap superuser and exit. ALTER ROLE mmuser RENAME TO superuser; exit
7.) Switch to superuser role. # psql mattermost superuser
8.) Revoke superuser ability from the temp role and rename it to match the one Mattermost uses to connect to the database. ALTER ROLE temp NOSUPERUSER; ALTER ROLE temp RENAME TO mmuser;
This procedure up to this point works to create a non-superuser that Mattermost will use to connect to the database, assuming the name of the new user matches the name in the docker configuration. However, it has no permissions and thus Mattermost is unable to initialize.
What is every privilege for tables, schemas, functions, etc, that need to be granted to a role in order for Mattermost to have everything necessary to work properly?
