PG中的must be owner of错误（权限）
Tags： grantGreenPlumPGPostgreSQL故障处理权限权限不足权限问题
👉 本文共约459个字，系统预计阅读时间或需2分钟。
现象
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | C:\Users\lhr>psql -U lhr -h 172.18.0.14 -p 54325 -d lhrdb Password for user lhr: psql (14.0, server 12.12 (Debian 12.12-1.pgdg110+1)) Type "help" for help. lhrdb=> \dt List of relations Schema | Name | Type | Owner --------+----------+-------+---------- lhr | t1 | table | lhr lhr | t3 | table | postgres public | bigtable | table | postgres public | t2 | table | postgres public | t_hash | table | postgres (5 rows) lhrdb=> alter table lhr.t3 add column id2 int; ERROR: must be owner of table t3 |
分析
表t3属于postgres用户，当前登陆用户为lhr用户，所以不能修改。
解决
可以有如下几种办法：
1、修改表t3的用户为lhr
1 | alter table lhr.t3 owner to lhr; |
2、使用表的owner用户登陆操作
3、使用超级管理员登陆
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
4、赋予原owner的给新用户
1 | grant postgres to lhr; |
总结
1、创建表的时候请使用正确的用户名来登陆，然后创建表，而不是统一使用管理员用户登陆
2、常用的赋权SQL如下：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | grant select,insert,update,delete on all tables in schema public to lhr; grant select,usage,update on all sequences in schema public to lhr; grant execute on all functions in schema public to lhr; grant references, trigger on all tables in schema public to lhr; grant create on schema public to lhr; grant usage on schema public to lhr; GRANT all ON ALL SEQUENCES IN SCHEMA public TO user1; GRANT all ON ALL tables IN SCHEMA public TO user1; GRANT all ON ALL FUNCTIONS IN SCHEMA public TO user1; GRANT all ON ALL SEQUENCES IN SCHEMA lhr TO user1; GRANT all ON ALL tables IN SCHEMA lhr TO user1; GRANT all ON ALL FUNCTIONS IN SCHEMA lhr TO user1; GRANT all ON database lhrdb TO user1; GRANT gpadmin to user1; ALTER SCHEMA lhr OWNER TO lhr; -- 授权单个序列给对应用户即可 GRANT USAGE,SELECT,UPDATE ON SEQUENCE lhrdb.report_seq TO user01; -- 授权所有序列给对应用户即可 GRANT USAGE,SELECT,UPDATE ON ALL SEQUENCES IN SCHEMA public TO user01; |
参考
https://www.coder.work/article/2597768
https://www.xmmup.com/greenplumzhongdexulie.html#bao_cuoERROR_permission_denied_for_sequence_report_seq
https://www.xmmup.com/greenplumdejiaoseyonghuyuquanxianguanli.html
