PG修改表owner提示must be member of role “pg13“

最新推荐文章于 2024-09-27 17:53:52 发布

MambaCloud

最新推荐文章于 2024-09-27 17:53:52 发布

阅读量4.8k

点赞数 2

分类专栏： PostgreSQL--故障处理文章标签： postgresql

本文链接：https://blog.csdn.net/weixin_49756466/article/details/122074430

版权

PostgreSQL--故障处理专栏收录该内容

2 篇文章 0 订阅

订阅专栏

PG修改表owner提示must be member of role “pg13”

背景：有一个表test6在pg13这个schema下，但是目前的owner是pg13_1。想把这个表的owner修改成pg13。然而在修改表的owner时候，提示must be member of role “pg13”

pg13test1=> \c pg13test1 pg13_1
You are now connected to database "pg13test1" as user "pg13_1".
pg13test1=> alter table pg13.test5 owner to pg13;
ERROR:  must be member of role "pg13"

pg13test1=> select * from pg_tables where tablename='test5';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity 
------------+-----------+------------+------------+------------+----------+-------------+-------------
 pg13       | test5     | pg13_1     |            | f          | f        | f           | f
(1 row)

解决方案：

把原来的owner加上pg13的role。

grant pg13 to pg13_1;

以下是详细操作步骤：

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

pg13test1=> \c pg13test1 pg13
You are now connected to database "pg13test1" as user "pg13".
pg13test1=> grant pg13 to pg13_1;
GRANT ROLE
pg13test1=> \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 pg13      |                                                            | {}
 pg13_1    |                                                            | {pg13}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

pg13test1=> \c pg13test1 pg13_1
You are now connected to database "pg13test1" as user "pg13_1".
pg13test1=> alter table pg13.test5 owner to pg13;
ALTER TABLE
pg13test1=> select * from pg_tables where tablename='test5';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity 
------------+-----------+------------+------------+------------+----------+-------------+-------------
 pg13       | test5     | pg13       |            | f          | f        | f           | f
(1 row)