Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rx7Lt-006Tb7-CR for pgsql-general@arkaria.postgresql.org; Wed, 17 Apr 2024 15:37:37 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rx7Lr-00CTWc-RQ for pgsql-general@arkaria.postgresql.org; Wed, 17 Apr 2024 15:37:35 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rx7Lr-00CTWT-CQ for pgsql-general@lists.postgresql.org; Wed, 17 Apr 2024 15:37:35 +0000 Received: from mail-pj1-x1032.google.com ([2607:f8b0:4864:20::1032]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rx7Lk-003JSK-QE for pgsql-general@lists.postgresql.org; Wed, 17 Apr 2024 15:37:34 +0000 Received: by mail-pj1-x1032.google.com with SMTP id 98e67ed59e1d1-2ab1ddfded1so804758a91.1 for ; Wed, 17 Apr 2024 08:37:28 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1713368248; x=1713973048; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=F7mJ9Qf8ZRphGi2cYblIXGNwk4Ui/WDbr30DZUH+Atw=; b=d71oywgeA5Oy/XGbgUokIny9IJXN+e3T2QKCDJkQREe6f5Ng2X6pZmakvqWdzQsK79 8qx+RQ+YLtq1ziZh8LY2O0GyczQMUYMZfmda9AcXVFeZF5rEJQckHPMT6CPjdke/444G 2DdaFyyTncNi0cmce2n9WEbMx1VNlYfwNAO5kAQcIlM1Pm5NcuOCuiGTjHakgi6rNgup o8WoLxzw9OIc6gQUe8CqDicG3OnxqRZ2wMI/c0j6+dGfB+sUUjq0e96+JieI4b6fpV+f Y57Z3jWAlXCUSYxa88AxZPUZ5ZMwzEW0s01B969zL6+DoC5MG2kwnOJJcgLYXLiAVKDO 3hyg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1713368248; x=1713973048; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=F7mJ9Qf8ZRphGi2cYblIXGNwk4Ui/WDbr30DZUH+Atw=; b=THOA4DEHfPHsRuMb8q10C8qR1UGTcIDNjzqr3e8THcA9x20/RuzgTJoF3q3j7LrAXu gDyTBIbIxeaBj2HpZaCJJBDtiAtw4KHBi93XkmNOc8P7xnkvI/J62a5UZkDbgJDPWmrM 3Fyc+8+LS2gEiylg8C0Emc6XDMfomqneEmM1uVNjY3jBuy5JPrleKV2d25iLJBH484Jq 0iszomoFZ0n6MUQUiv+GRg7emuL2P1ipN8nzKdby2f56i/1A4nbHkWCFAtn3lcl7sTGb wx+w1NCbcArC1aw1c/J0k2Y/VQFFEv7W6l3PAnQXZc7INl1aPv7bS9qjPeQIyI3rmLIW f23A== X-Gm-Message-State: AOJu0YyjHP2vUtty4ZZIfv1DLp2l8u7Aj2J3gfU+cZrDrPY8VCMHOAPF aEXZcRXVJ4cbGWWRM3LbsQ5A57y1am8HQBOWNxiphFmvamyCRJYj3MdtNaPqX9+eEwgdav6to/d U/dNGaKtka9fa7z9pnoZquWWx6oA= X-Google-Smtp-Source: AGHT+IHvxPRDIpbnAUWP96EZs5BgEC2NEPlljXYgvKdJgAC/XvVpANlVKZ9BxXO73aeWMeuGj+sXUq1jehq2AJjHZZ4= X-Received: by 2002:a17:90b:3c3:b0:2a6:db3:1aa5 with SMTP id go3-20020a17090b03c300b002a60db31aa5mr16561323pjb.18.1713368247616; Wed, 17 Apr 2024 08:37:27 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Sasmit Utkarsh Date: Wed, 17 Apr 2024 21:07:16 +0530 Message-ID: Subject: Re: Assistance needed for the query execution in non-public schema To: Laurenz Albe Cc: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000c4cf9c06164ca13c" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000c4cf9c06164ca13c Content-Type: text/plain; charset="UTF-8" Thanks Laurenz and David Regards, Sasmit Utkarsh +91-7674022625 On Tue, 16 Apr, 2024, 16:58 Laurenz Albe, wrote: > On Tue, 2024-04-16 at 16:30 +0530, Sasmit Utkarsh wrote: > > msshctd=> SELECT setval(pg_get_serial_sequence('mqa_flfo_cstr', 'id'), > coalesce(MAX(id), 1)) from mqa_flfo_cstr; > > ERROR: permission denied for sequence mqa_flfo_cstr_id_seq > > msshctd=> SELECT setval(pg_get_serial_sequence('mqa_ffp_nval', 'id'), > coalesce(MAX(id), 1)) from mqa_ffp_nval; > > ERROR: permission denied for sequence mqa_ffp_nval_id_seq > > > > msshctd=> \z mqa_flfo_cstr_id_seq > > Access privileges > > Schema | Name | Type | Access privileges > | Column privileges | Policies > > > ---------+----------------------+----------+-----------------------------+-------------------+---------- > > shc_tty | mqa_flfo_cstr_id_seq | sequence | > pgddb_admin=rwU/pgddb_admin+| | > > | | | shc_uadmin=rU/pgddb_admin > | | > > (1 row) > > > > msshctd=> \z mqa_ffp_nval_id_seq > > Access privileges > > Schema | Name | Type | Access privileges > | Column privileges | Policies > > > ---------+---------------------+----------+-----------------------------+-------------------+---------- > > shc_tty | mqa_ffp_nval_id_seq | sequence | > pgddb_admin=rwU/pgddb_admin+| | > > | | | shc_uadmin=rU/pgddb_admin > | | > > (1 row) > > > > Above query executes fine when I try to execute as user > "pgddb_admin"(Super User). > > but when I switch to shc_uadmin, I see a permission error. > > That's because the user is lacking the UPDATE privilege ("U" is USAGE). > > > GRANT USAGE, ALTER ON SEQUENCE mqa_flfo_cstr_id_seq TO shc_uadmin; > > ERROR: unrecognized privilege type "alter" SQL state: 42601 > > There is no ALTER privilege. Try > > GRANT UPDATE ON SEQUENCE mqa_flfo_cstr_id_seq, mqa_ffp_nval_id_seq TO > shc_uadmin; > > Yours, > Laurenz Albe > --000000000000c4cf9c06164ca13c Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thanks Laurenz and David

Regards,
Sasmit Utkarsh
+91-76740226= 25

On Tue, 16 Apr, 2024, 16:58 Laurenz Albe, <laurenz.albe@cybertec.at> wrote:
On Tue, 2024-04-16 at 16:30 +0530, Sasmit Utkar= sh wrote:
> msshctd=3D> SELECT setval(pg_get_serial_sequence('mqa_flfo_cstr= ', 'id'), coalesce(MAX(id), 1)) from mqa_flfo_cstr;
> ERROR: =C2=A0permission denied for sequence mqa_flfo_cstr_id_seq
> msshctd=3D> SELECT setval(pg_get_serial_sequence('mqa_ffp_nval&= #39;, 'id'), coalesce(MAX(id), 1)) from mqa_ffp_nval;
> ERROR: =C2=A0permission denied for sequence mqa_ffp_nval_id_seq
>
> msshctd=3D> \z mqa_flfo_cstr_id_seq
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0Access privileges
> =C2=A0Schema =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 Name =C2=A0 =C2=A0 = =C2=A0 =C2=A0 | =C2=A0 Type =C2=A0 | =C2=A0 =C2=A0 =C2=A0Access privileges = =C2=A0 =C2=A0 =C2=A0| Column privileges | Policies
> ---------+----------------------+----------+--------------------------= ---+-------------------+----------
> =C2=A0shc_tty | mqa_flfo_cstr_id_seq | sequence | pgddb_admin=3DrwU/pg= ddb_admin+| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = |
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0| shc_uadmin=3DrU/pgddb_admin =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |
> (1 row)
>
> msshctd=3D> \z mqa_ffp_nval_id_seq
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0Access privileges
> =C2=A0Schema =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0Name =C2=A0 =C2=A0 =C2= =A0 =C2=A0 | =C2=A0 Type =C2=A0 | =C2=A0 =C2=A0 =C2=A0Access privileges =C2= =A0 =C2=A0 =C2=A0| Column privileges | Policies
> ---------+---------------------+----------+---------------------------= --+-------------------+----------
> =C2=A0shc_tty | mqa_ffp_nval_id_seq | sequence | pgddb_admin=3DrwU/pgd= db_admin+| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| s= hc_uadmin=3DrU/pgddb_admin =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 |
> (1 row)
> =C2=A0
> Above query executes fine when I try to execute as user "pgddb_ad= min"(Super User).
> but when I switch to shc_uadmin, I see a permission error.

That's because the user is lacking the UPDATE privilege ("U" = is USAGE).

> GRANT USAGE, ALTER ON SEQUENCE mqa_flfo_cstr_id_seq TO shc_uadmin;
> ERROR: unrecognized privilege type "alter" SQL state: 42601<= br>
There is no ALTER privilege.=C2=A0 Try

=C2=A0 GRANT UPDATE ON SEQUENCE mqa_flfo_cstr_id_seq, mqa_ffp_nval_id_seq T= O shc_uadmin;

Yours,
Laurenz Albe
--000000000000c4cf9c06164ca13c--