Re: Assistance needed for the query execution in non-public schema

Thanks Laurenz and David

Regards,
Sasmit Utkarsh
+91-7674022625

On Tue, 16 Apr, 2024, 16:58 Laurenz Albe, <laurenz(dot)albe(at)cybertec(dot)at> 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
>