首页 » PostgreSQL/GaussDB » openGauss ERROR: current user does not have privilege to role XXX 解决方案
openGauss ERROR: current user does not have privilege to role XXX 解决方案
在openGauss 数据库中如果存在多个用户如A和B, 希望B用户可以在用户A的同名schema下创建table对象,需要授权给用户B,在oracle中如create any table的系统权限或者是user Proxy 高级用法,在我之前的笔记Oracle 23c 几个开发相关新特性中,在oracle 23c 才引入grant xx ON SCHEMA xx to xx的语法,就是这样的功能在openGauss中有不同的用法。今天在一个项目遇到了这个问题,下面演示一下这个情况。
openGauss V6
创建2个用户
openGauss=# create user anbob identified by "www.anbob.com"; openGauss=# grant Sysadmin to anbob; openGauss=# create user weejar identified by "weejar"; ERROR: Password must contain at least 8 characters. openGauss=# create user weejar identified by "6.anbob.com"; NOTICE: The encrypted password contains MD5 ciphertext, which is not secure. CREATE ROLE
授权 usage on schema
openGauss=# grant usage on schema anbob to weejar; GRANT
weejar尝试创建anbob下的表
openGauss=# \c - weejar
Password for user weejar:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "weejar".
openGauss=> create table anbob.test1(id int);
ERROR: permission denied for schema anbob
openGauss=# \dn+ anbob
List of schemas
Name | Owner | Access privileges | Description | WithBlockChain
-------+-------+-------------------+-------------+----------------
anbob | anbob | anbob=UC/anbob +| | f
| | weejar=U/anbob | |
openGauss=# \du anbob
List of roles
Role name | Attributes | Member of
-----------+------------+-----------
anbob | Sysadmin | {}
openGauss=# \du weejar
List of roles
Role name | Attributes | Member of
-----------+------------+-----------
weejar | | {}
授权 all on schema
openGauss=# GRANT all on schema anbob to weejar; GRANT
weejar尝试创建anbob下的表
openGauss=> create table anbob.test1(id int);
ERROR: current user does not have privilege to role anbob
解决方法
授权 usera to userb
openGauss=# grant anbob to weejar; GRANT ROLE
weejar尝试创建anbob下的表
openGauss=> create table anbob.test1(id int); CREATE TABLE
POSTGRESQL17
在postgresql中与openGauss有些不同,如同名schema和权限.
D:\postgresql\pgsql\bin>psql -d postgres
psql (17rc1)
输入 "help" 来获取帮助信息.
postgres=# create user anbob password 'www.anbob.com';
CREATE ROLE
postgres=# create user weejar1 password 'www.anbob.com';
CREATE ROLE
postgres=# grant usage on schema anbob to weejar1;
错误: 模式 "anbob" 不存在
postgres=# create schema anbob;
CREATE SCHEMA
postgres=# grant usage on schema anbob to weejar1;
GRANT
postgres=# \c - weejar1
您现在已经连接到数据库 "postgres",用户 "weejar1".
postgres=> create table anbob.test1(id int);
错误: 对模式 anbob 权限不够
第1行create table anbob.test1(id int);
^
解决方法
D:\postgresql\pgsql\bin>psql -d postgres
psql (17rc1)
输入 "help" 来获取帮助信息.
postgres=# grant all on schema anbob to weejar1;
GRANT
postgres=# \c - weejar1
您现在已经连接到数据库 "postgres",用户 "weejar1".
postgres=> create table anbob.test1(id int);
CREATE TABLE
postgres=> select version();
version
--------------------------------------------------------------------------
PostgreSQL 17rc1 on x86_64-windows, compiled by msvc-19.41.34120, 64-bit
(1 行记录)
总结:
yyy用户在schema XXX下创建表,在postgresql中使用grant all on schema xxx to yyy,可以解决该问题,但是在openGauss中并不适用,需要grant xxx to yyy。
— enjoy —
目前这篇文章还没有评论(Rss)