Postgresql无法将角色更改为超级用户

Question

我在postgreql 101失败了...我只是想改变我的当前用户cataid拥有超级用户权限。

(catmaid) [catmaid@sonic ~]$ psql postgres
psql (10.10)
Type "help" for help.

postgres=> \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 catmaid   |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=> alter role catmaid superuser;
ERROR:  must be superuser to alter superusers

我想这样做是因为我无法进行django迁移

(catmaid) [catmaid@sonic django]$ ./projects/manage.py migrate
INFO 2021-02-11 18:12:40,155 CATMAID version 2018.11.09-1660-gce1dee4
WARNING 2021-02-11 18:12:40,806 NeuroML module could not be loaded.
WARNING 2021-02-11 18:12:41,068 CATMAID was unable to load the Rpy2 library, which is an optional dependency. Nblast support is therefore disabled.
WARNING 2021-02-11 18:12:41,068 CATMAID was unable to load the Pandas lirary, which is an optional dependency. Nblast support is therefore disabled.
INFO 2021-02-11 18:12:41,125 Spatial update events disabled
INFO 2021-02-11 18:12:41,271 History tracking enabled
System check identified some issues:

WARNINGS:
?: Couldn't check spatial update notification setup, missing database functions
        HINT: Migrate CATMAID
?: The output folder  is not writable
        HINT: Make sure the user running CATMAID can write to the output directory or change the MEDIA_ROOT setting.
Operations to perform:
  Apply all migrations: admin, auth, authtoken, catmaid, contenttypes, guardian, performancetests, pgcompat, sessions, sites, taggit
Running migrations:
  Applying catmaid.0058_add_pg_trgm_ext_and_class_instance_name_index...Traceback (most recent call last):
  File "/home/catmaid/miniconda/envs/catmaid/lib/python3.6/site-packages/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.InsufficientPrivilege: permission denied to create extension "pg_trgm"
HINT:  Must be superuser to create this extension.

Answer 1

我骗自己以为我实际上是postgres的用户。@iklinac和@Frank N Stein在评论中指出，我必须把自己写到postgres上。

(catmaid) [catmaid@sonic django]$ su - postgres
-bash-4.2$ psql -U postgres
psql (10.10)
Type "help" for help.

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 catmaid   |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# alter role catmaid superuser;
ALTER ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 catmaid   | Superuser                                                  | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

所以功劳归他们，谢谢！