Обсуждение: Postgresql assistance needed

Поиск
Список
Период
Сортировка

Postgresql assistance needed

От
Sasmit Utkarsh
Дата:
Hi Postgresql Team,

I'm facing a weird issue which testing the application code using libpq in C. when i run a test case with PGUSER as "pgddb_admin"  i get the expected result (more details attached in success_log doc) whereas when i run the same test case using another user "shc_uadmin" which has same privileges, I get below error message highlighted(more details in Error_log doc attached)

<3>3343433-[ERROR] SELECT nextval Failed [../tpfasm.c:3561:get_pool_fa]

  
More details related to "shc_uadmin" and sequence "unassigned_pool_fa"  which is present in non-public schema (shc) below from the terminal.

[shc_user@cucmtpccu1 ~]$ psql "host= cucmpsgsu0.postgres.database.azure.com port=5432 dbname=mshcd user=shc_uadmin password=abc123 sslmode=require options=--search_path=shc,public"
psql (16.1, server 15.4)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

mshcd=> \dp unassigned_pool_fa
                                          Access privileges
 Schema |        Name        |   Type   |      Access privileges      | Column privileges | Policies
--------+--------------------+----------+-----------------------------+-------------------+----------
 shc    | unassigned_pool_fa | sequence | pgddb_admin=rwU/pgddb_admin+|                   |
        |                    |          | shc_uadmin=rwU/pgddb_admin  |                   |
(1 row)

mshcd=> SELECT nextval('unassigned_pool_fa');
   nextval
-------------
 -1811939322
(1 row)

Is there any other issue related to the permissions or configuration associated with the shc_uadmin user in PostgreSQL which needs to be checked/verified? Please assist with your inputs


Regards,
Sasmit Utkarsh
+91-7674022625
Вложения

Re: Postgresql assistance needed

От
Laurenz Albe
Дата:
On Thu, 2024-02-22 at 14:01 +0530, Sasmit Utkarsh wrote:
> ==3343433==ERROR: AddressSanitizer: heap-buffer-overflow on address 0xf337ba80 at pc 0xf795fcdd bp 0xff8a74d8 sp
0xff8a70b0
> READ of size 4096 at 0xf337ba80 thread T0
>     #0 0xf795fcdc in __interceptor_memcpy
(/nix/store/3blqv6wzmwqgkkw45i6c8w0bkwldk418-gcc-12.2.0-lib/lib/libasan.so.8+0x42cdc)
>     #1 0xf78c34bb in pqPutnchar
(/nix/store/glkv3qv4y64xigwpjcwflp3ni2anjma0-postgresql-14.9-lib/lib/libpq.so.5+0x174bb)
>     #2 0xf78be05e in PQsendQueryGuts
(/nix/store/glkv3qv4y64xigwpjcwflp3ni2anjma0-postgresql-14.9-lib/lib/libpq.so.5+0x1205e)
>     #3 0xf78c05a2 in PQexecPrepared
(/nix/store/glkv3qv4y64xigwpjcwflp3ni2anjma0-postgresql-14.9-lib/lib/libpq.so.5+0x145a2)

Perhaps you forgot to terminate a string with '\0'.

Yours,
Laurenz Albe

Re: Postgresql assistance needed

От
Sasmit Utkarsh
Дата:
Hi Laurenz,

Sorry but are you talking about the export variables in the result?

Regards,
Sasmit Utkarsh
+91-7674022625


On Thu, Feb 22, 2024 at 2:20 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Thu, 2024-02-22 at 14:01 +0530, Sasmit Utkarsh wrote:
> ==3343433==ERROR: AddressSanitizer: heap-buffer-overflow on address 0xf337ba80 at pc 0xf795fcdd bp 0xff8a74d8 sp 0xff8a70b0
> READ of size 4096 at 0xf337ba80 thread T0
>     #0 0xf795fcdc in __interceptor_memcpy (/nix/store/3blqv6wzmwqgkkw45i6c8w0bkwldk418-gcc-12.2.0-lib/lib/libasan.so.8+0x42cdc)
>     #1 0xf78c34bb in pqPutnchar (/nix/store/glkv3qv4y64xigwpjcwflp3ni2anjma0-postgresql-14.9-lib/lib/libpq.so.5+0x174bb)
>     #2 0xf78be05e in PQsendQueryGuts (/nix/store/glkv3qv4y64xigwpjcwflp3ni2anjma0-postgresql-14.9-lib/lib/libpq.so.5+0x1205e)
>     #3 0xf78c05a2 in PQexecPrepared (/nix/store/glkv3qv4y64xigwpjcwflp3ni2anjma0-postgresql-14.9-lib/lib/libpq.so.5+0x145a2)

Perhaps you forgot to terminate a string with '\0'.

Yours,
Laurenz Albe

Re: Postgresql assistance needed

От
Laurenz Albe
Дата:
On Thu, 2024-02-22 at 16:13 +0530, Sasmit Utkarsh wrote:
> On Thu, Feb 22, 2024 at 2:20 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> > On Thu, 2024-02-22 at 14:01 +0530, Sasmit Utkarsh wrote:
> > > ==3343433==ERROR: AddressSanitizer: heap-buffer-overflow on address 0xf337ba80 at pc 0xf795fcdd bp 0xff8a74d8 sp
0xff8a70b0
> > > READ of size 4096 at 0xf337ba80 thread T0
> > >     #0 0xf795fcdc in __interceptor_memcpy
(/nix/store/3blqv6wzmwqgkkw45i6c8w0bkwldk418-gcc-12.2.0-lib/lib/libasan.so.8+0x42cdc)
> > >     #1 0xf78c34bb in pqPutnchar
(/nix/store/glkv3qv4y64xigwpjcwflp3ni2anjma0-postgresql-14.9-lib/lib/libpq.so.5+0x174bb)
> > >     #2 0xf78be05e in PQsendQueryGuts
(/nix/store/glkv3qv4y64xigwpjcwflp3ni2anjma0-postgresql-14.9-lib/lib/libpq.so.5+0x1205e)
> > >     #3 0xf78c05a2 in PQexecPrepared
(/nix/store/glkv3qv4y64xigwpjcwflp3ni2anjma0-postgresql-14.9-lib/lib/libpq.so.5+0x145a2)
> >
> > Perhaps you forgot to terminate a string with '\0'.
>
> Sorry but are you talking about the export variables in the result?

Whatever you fed to PQexecPrepared.

Yours,
Laurenz Albe

Re: Postgresql assistance needed

От
Sasmit Utkarsh
Дата:
Hi Laurenz/Postgresql Team,

Perhaps, the issue I wanted to highlight here is that I get the same entry working when I switch user to  "pgddb_admin" and not when change user with same privileges as PGUSER "shc_uadmin" I get the message in the error log like "<3>3343433-[ERROR] SELECT nextval Failed [../tpfasm.c:3561:get_pool_fa]" even though i see the next val from db. Please see below when logged in to the db and snippet of the code lines where it was throwing the error. 

[shc_user@cucmtpccu1 ~]$ psql "host= cucmpsgsu0.postgres.database.azure.com port=5432 dbname=mshcd user=shc_uadmin password=abc123 sslmode=require options=--search_path=shc,public"
psql (16.1, server 15.4)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.
mshcd=> SELECT nextval('unassigned_pool_fa');
   nextval
-------------
 -1811939322
(1 row)


code snippet:
3555                 } else {
 3556                         LOG_WARN("No File_address is found with status=free");
 3557                         //If no free address is found, get the next value available address from the sequence
 3558                         PQclear(res);
 3559                         res = PQexec(conn, "SELECT nextval('unassigned_pool_fa');");
 3560                         if (PQresultStatus(res) != PGRES_TUPLES_OK) {
 3561                                 LOG_ERROR("SELECT nextval Failed");
 3562                                 LOG_DEBUG("ROLLBACK TRANSACTION");
 3563                                 res = PQexec(conn,"ROLLBACK TRANSACTION");
 3564                                 PQclear(res);
 3565                                 return 0;
 3566                         }

Is the issue related to configuration of PGUSER or something else

Regards,
Sasmit Utkarsh
+91-7674022625


On Thu, Feb 22, 2024 at 4:20 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Thu, 2024-02-22 at 16:13 +0530, Sasmit Utkarsh wrote:
> On Thu, Feb 22, 2024 at 2:20 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> > On Thu, 2024-02-22 at 14:01 +0530, Sasmit Utkarsh wrote:
> > > ==3343433==ERROR: AddressSanitizer: heap-buffer-overflow on address 0xf337ba80 at pc 0xf795fcdd bp 0xff8a74d8 sp 0xff8a70b0
> > > READ of size 4096 at 0xf337ba80 thread T0
> > >     #0 0xf795fcdc in __interceptor_memcpy (/nix/store/3blqv6wzmwqgkkw45i6c8w0bkwldk418-gcc-12.2.0-lib/lib/libasan.so.8+0x42cdc)
> > >     #1 0xf78c34bb in pqPutnchar (/nix/store/glkv3qv4y64xigwpjcwflp3ni2anjma0-postgresql-14.9-lib/lib/libpq.so.5+0x174bb)
> > >     #2 0xf78be05e in PQsendQueryGuts (/nix/store/glkv3qv4y64xigwpjcwflp3ni2anjma0-postgresql-14.9-lib/lib/libpq.so.5+0x1205e)
> > >     #3 0xf78c05a2 in PQexecPrepared (/nix/store/glkv3qv4y64xigwpjcwflp3ni2anjma0-postgresql-14.9-lib/lib/libpq.so.5+0x145a2)
> >
> > Perhaps you forgot to terminate a string with '\0'.
>
> Sorry but are you talking about the export variables in the result?

Whatever you fed to PQexecPrepared.

Yours,
Laurenz Albe
Вложения

Re: Postgresql assistance needed

От
Laurenz Albe
Дата:
On Thu, 2024-02-22 at 17:18 +0530, Sasmit Utkarsh wrote:
> Perhaps, the issue I wanted to highlight here is that I get the same entry working
> when I switch user to "pgddb_admin" and not when change user with same privileges
> as PGUSER "shc_uadmin" I get the message in the error log like
> "<3>3343433-[ERROR] SELECT nextval Failed [../tpfasm.c:3561:get_pool_fa]"
> even though i see the next val from db. Please see below when logged in to the db
> and snippet of the code lines where it was throwing the error. 
>
> [shc_user@cucmtpccu1 ~]$ psql "host= cucmpsgsu0.postgres.database.azure.com port=5432 dbname=mshcd user=shc_uadmin
password=abc123sslmode=require options=--search_path=shc,public" 
> psql (16.1, server 15.4)
> SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
> Type "help" for help.
> mshcd=> SELECT nextval('unassigned_pool_fa');
>    nextval
> -------------
>  -1811939322
> (1 row)
>
>
> code snippet:
> 3555                 } else {
> 3556                         LOG_WARN("No File_address is found with status=free");
> 3557                         //If no free address is found, get the next value available address from the sequence
> 3558                         PQclear(res);
> 3559                         res = PQexec(conn, "SELECT nextval('unassigned_pool_fa');");
> 3560                         if (PQresultStatus(res) != PGRES_TUPLES_OK) {
> 3561                                 LOG_ERROR("SELECT nextval Failed");
> 3562                                 LOG_DEBUG("ROLLBACK TRANSACTION");
> 3563                                 res = PQexec(conn,"ROLLBACK TRANSACTION");
> 3564                                 PQclear(res);
> 3565                                 return 0;
> 3566                         }

To debug that, get the actual error message using PQerrorMessage().
That should tell you what is going on.

Perhaps the sequence is not on your "search_path", and you should
qualify the name with the schema.

Yours,
Laurenz Albe

Re: Postgresql assistance needed

От
Sasmit Utkarsh
Дата:
Thanks Laurenz

->To debug that, get the actual error message using PQerrorMessage().
That should tell you what is going on.  -- will try to add the  PQerrorMessage() in the logs

->Perhaps the sequence is not on your "search_path", and you should
qualify the name with the schema.  -- I have given the below set of env variables including setting the schema path, which works well for  "pgddb_admin" and not for user  "shc_uadmin". Is there any configuration/query that can be checked to verify if  "shc_uadmin" has the correct path set?

export PGHOST=cucmpsgsu0.postgres.database.azure.com
export PGDATABASE=mshcd
export PGUSER=shc_uadmin
export PGPASSWORD=abc123
export PGOPTIONS='--search_path=shc,shc_tty,public'



Regards,
Sasmit Utkarsh
+91-7674022625


On Thu, Feb 22, 2024 at 7:11 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Thu, 2024-02-22 at 17:18 +0530, Sasmit Utkarsh wrote:
> Perhaps, the issue I wanted to highlight here is that I get the same entry working
> when I switch user to "pgddb_admin" and not when change user with same privileges
> as PGUSER "shc_uadmin" I get the message in the error log like
> "<3>3343433-[ERROR] SELECT nextval Failed [../tpfasm.c:3561:get_pool_fa]"
> even though i see the next val from db. Please see below when logged in to the db
> and snippet of the code lines where it was throwing the error. 
>
> [shc_user@cucmtpccu1 ~]$ psql "host= cucmpsgsu0.postgres.database.azure.com port=5432 dbname=mshcd user=shc_uadmin password=abc123 sslmode=require options=--search_path=shc,public"
> psql (16.1, server 15.4)
> SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
> Type "help" for help.
> mshcd=> SELECT nextval('unassigned_pool_fa');
>    nextval
> -------------
>  -1811939322
> (1 row)
>
>
> code snippet:
> 3555                 } else {
> 3556                         LOG_WARN("No File_address is found with status=free");
> 3557                         //If no free address is found, get the next value available address from the sequence
> 3558                         PQclear(res);
> 3559                         res = PQexec(conn, "SELECT nextval('unassigned_pool_fa');");
> 3560                         if (PQresultStatus(res) != PGRES_TUPLES_OK) {
> 3561                                 LOG_ERROR("SELECT nextval Failed");
> 3562                                 LOG_DEBUG("ROLLBACK TRANSACTION");
> 3563                                 res = PQexec(conn,"ROLLBACK TRANSACTION");
> 3564                                 PQclear(res);
> 3565                                 return 0;
> 3566                         }

To debug that, get the actual error message using PQerrorMessage().
That should tell you what is going on.

Perhaps the sequence is not on your "search_path", and you should
qualify the name with the schema.

Yours,
Laurenz Albe

Re: Postgresql assistance needed

От
Greg Sabino Mullane
Дата:
On the surface, it looks as though it *should* work if the only thing changing is the username. Those other more serious errors should get fixed, but putting those aside for now... We don't know what your program does. Write a smaller one that just does a PQexec and calls nextval, then returns a proper error message if it fails.

Cheers,
Greg

Re: Postgresql assistance needed

От
Laurenz Albe
Дата:
On Thu, 2024-02-22 at 19:35 +0530, Sasmit Utkarsh wrote:
>  Is there any configuration/query that can be checked to verify if  "shc_uadmin" has the correct path set?

The SQL statement "SHOW search_path" would return the current setting.

But look at the error message first.

Yours,
Laurenz Albe

Re: Postgresql assistance needed

От
Sasmit Utkarsh
Дата:
Thanks, I'll check it out.

Regards,
Sasmit Utkarsh
+91-7674022625

On Thu, 22 Feb, 2024, 21:40 Laurenz Albe, <laurenz.albe@cybertec.at> wrote:
On Thu, 2024-02-22 at 19:35 +0530, Sasmit Utkarsh wrote:
>  Is there any configuration/query that can be checked to verify if  "shc_uadmin" has the correct path set?

The SQL statement "SHOW search_path" would return the current setting.

But look at the error message first.

Yours,
Laurenz Albe