Re: Error message

[Igor Korot]

Thx, David.
I need to get some sleep. ;-)



On Tue, Mar 16, 2021 at 12:35 AM David G. Johnston
 wrote:
>
> On Monday, March 15, 2021, Igor Korot  wrote:
>>
>> [quote]
>> As with PQexec, the result is normally a PGresult object whose
>> contents indicate server-side success or failure. A null result
>> indicates out-of-memory or inability to send the command at all. Use
>> PQerrorMessage to get more information about such errors.
>> [/quote]
>>
>> But this function is not the same as PQexec().
>>
>> So what does it return if it succeeds?
>
>
> Exactly what the syntax line and that first sentence says it does, a PGresult.
>
> David J.
>

Re: Error message

[David G. Johnston]

On Monday, March 15, 2021, Igor Korot  wrote:
>
> [quote]
> As with PQexec, the result is normally a PGresult object whose
> contents indicate server-side success or failure. A null result
> indicates out-of-memory or inability to send the command at all. Use
> PQerrorMessage to get more information about such errors.
> [/quote]
>
> But this function is not the same as PQexec().
>
> So what does it return if it succeeds?
>

Exactly what the syntax line and that first sentence says it does, a
PGresult.

David J.

Error message

[Igor Korot]

Hi, ALL,

Documentation for PQprepare states:

[quote]
As with PQexec, the result is normally a PGresult object whose
contents indicate server-side success or failure. A null result
indicates out-of-memory or inability to send the command at all. Use
PQerrorMessage to get more information about such errors.
[/quote]

But this function is not the same as PQexec().

So what does it return if it succeeds?

Thank you.

Re: Fwd: row level security (RLS)

[Rafal Pietrak]

W dniu 15.03.2021 o 18:01, Laurenz Albe pisze:
> On Mon, 2021-03-15 at 16:28 +0100, Rafal Pietrak wrote:
>> 5. My experiments with RLS was like following:
>> - I've implemented a STABLE function, that returns INTEGER 1/0
>> - I've linked that function as POLICY to my tables
>> - I've GRANTED PUBLIC access to those tables
>> ---> and all works as predicted only slow (10x slower!).
>>
>> [lots of questions about how to solve this is some other way]
>>
>> Those questions come from my bad experience with POLICY performance.
> 
> You should figure out why RLS was so slow.

Yes I should... although I didn't. Somewhat because I thought it was
obvious (an additional function call on every row). Still, as I've
mentioned in my initial post, I'm going to revisit the case in the
couple of days and gather more evidence.

Having said that, I'm really interested in any comments on the way I've
"imagined" addressing RLS years ago (and described it in the post), when
I've looked for a solution and settled for the rule system. The question
about partition/check/role approach irrespective of where they come from.

Pls address the following reasoning:
1. POLICY calls a function on every row to check it's visibility to the
client (for 1mln rows, 1mln checks).
2. "alternative" does just one check on all the rows contained in a
particular partition (for 100 tenets 100 checks)

No matter how hard one optimises the POLICY function, it will always loose.

Then again, I'll be back with some "ANALYSE" in a couple of days.

-R

Re: sql3types.h does not include "BigInt"

[Tom Lane]

Niko Ware  writes:
> The data types defined in sql3types.h (see below) do not include "zero" or
> "bigint" (the enumerated values start at "1").

Hm.  It looks like that header hasn't been touched since around SQL99
(which did not have bigint).  Current versions of the spec list bigint
as code 25 (cf. table 7 in part 3 SQL/CLI).  If you wanted to send in
a patch that'd make that work, we'd likely accept it.

regards, tom lane

Re: Allowing John to Drop Triggers On Chad's Tables

[Tom Lane]

Fred Habash  writes:
> Based on my research, There are the options I have identified to allow user
> 'John' to drop a trigger on a table owner by 'Chad' ..

I guess the big-picture question is why do you think you need that?

The separate TRIGGER permission is something we consider obsolescent.
It's difficult to make use of it in any way that isn't tantamount to
giving John the ability to execute arbitrary code as Chad (the next
time Chad does anything with his table).  So while a case could be
made that granting TRIGGER should include the right to alter or drop
existing triggers, it's unlikely that anyone will want to expend effort
in that direction.

regards, tom lane

sql3types.h does not include "BigInt"

[Niko Ware]

I'm using "dynamic sql" to process generic queries (see code fragment
below). The routine works correctly for most data types, but it does not
work for functions (e.g., NextVal()). The data TYPE returned for NextVal()
sequence is zero (see line 24 of the code fragment). The documented data
type for NextVal() is "bigint" (see
https://www.postgresql.org/docs/current/functions-sequence.html). The query
being executed is "SELECT NEXTVAL('my_sequence')".

The data types defined in sql3types.h (see below) do not include "zero" or
"bigint" (the enumerated values start at "1"). My function defaults the
data type to "String" which returns a NULL terminated character array with
the expected value. I'm able to coerce this to an integer at the
application level, but there are other "unknown" cases where my application
is requesting the result of a function. The application code does not
always coerce the value to the correct type (it assumes the type based on
the query) which results in unexpected operations.

How do I identify these "out of bound" data types and properly coerce them
to the expected type? At a minimum I would like to handle the results from
sequence manipulation functions. Is there an alternate descriptor field
which can be used to identify the function return type (bigint) so that it
can be properly cast?

-
SQL3TYPES.H

/* SQL3 dynamic type codes */
/* chapter 13.1 table 2: Codes used for SQL data types in Dynamic SQL */

enum
{
   SQL3_CHARACTER = 1,
   SQL3_NUMERIC,
   SQL3_DECIMAL,
   SQL3_INTEGER,
   SQL3_SMALLINT,
   SQL3_FLOAT,
   SQL3_REAL,
   SQL3_DOUBLE_PRECISION,
   SQL3_DATE_TIME_TIMESTAMP,
   SQL3_INTERVAL,  /* 10 */
   SQL3_CHARACTER_VARYING = 12,
   SQL3_ENUMERATED,
   SQL3_BIT,
   SQL3_BIT_VARYING,
   SQL3_BOOLEAN,
   SQL3_abstract
   /* the rest is xLOB stuff */
};

-
CODE FRAGMENT:

  1EXEC SQL WHENEVER SQLERROR GOTO myErrorHandler;
  2EXEC SQL WHENEVER NOT FOUND DO break;
  3
  4EXEC SQL ALLOCATE DESCRIPTOR my_desc;
  5
  6EXEC SQL PREPARE my_prepare FROM :my_sql_query;
  7EXEC SQL DECLARE my_cursor CURSOR FOR my_prepare;
  8EXEC SQL OPEN my_cursor;
  9
 10while (TRUE)
 11{
 12   EXEC SQL FETCH NEXT FROM my_cursor INTO SQL DESCRIPTOR my_desc;
 13
 14   if (gdat->cols == NULL)
 15   {
 16  EXEC SQL GET DESCRIPTOR my_desc :my_num_cols = COUNT;
 17
 18  if ((gdat->num_cols = my_num_cols) > 0)
 19  {
 20 if ((gdat->cols = (ColDat*) calloc(gdat->num_cols,
sizeof(ColDat))) != NULL)
 21 {
 22for (my_col = 1; my_col <= gdat->num_cols; my_col++)
 23{
 24   EXEC SQL GET DESCRIPTOR my_desc VALUE :my_col
:my_name = NAME, :my_type = TYPE;
 25   OAStrCpySize(gdat->cols[my_col-1].name, my_name.arr);
 26   gdat->cols[my_col-1].type =
GDconvertSQL13Type(my_type);
 27   OAprintf("- %d = %d\n",
gdat->cols[my_col-1].type, my_type); //~~
 28}
 29 }
 30  }
 31   }
 32}

Re: Allowing John to Drop Triggers On Chad's Tables

[David G. Johnston]

On Mon, Mar 15, 2021 at 12:20 PM Fred Habash  wrote:

> If there is a 'grant trigger' why is there not a 'grant drop trigger'?
>
>
Because creating a trigger from scratch doesn't let you affect other
triggers that you may not own (at least not directly).  If drop permissions
were grantable the user could drop any trigger attached to said table.  The
missing feature here is that triggers should probably have their own
ownership separate from a trigger and a trigger can only be attached to a
table where the trigger owner has trigger permissions.  But the trigger
owner can always drop their owned triggers.



> Any other options since Chad has one table to allow John to drop triggers
> on?
>
>
Option 3 is the one that is expected to be used here.  Well, aside from the
"John the person asks Chad the person to drop the trigger".

David J.

Fwd: row level security (RLS)

[Rafal Pietrak]

Hello,

I was told, that pgsql-hackers is not the right list for the following
questions. So I'm reposting to general.

Does anybody have an opinion regarding the following questions?


---
Hello,

Forgive me for probably naive questions, being so talkative like the
following. But the less one knows the more one must explain. And I don't
know much regarding RLS.

1. Some time ago I've implemented in my schema a poore mans' RLS using
the rule system.

2. like half a year ago I've discovered postgreSQL native implementation
with policies, so I've decided to give it a try.

3. to my ultimate surprise, this turned out to be like 10 times slower.
So I abondened the project.

4. but it bites me, one question in particular  which requires the
lengthy explanations:

5. My experiments with RLS was like following:
- I've implemented a STABLE function, that returns INTEGER 1/0
- I've linked that function as POLICY to my tables
- I've GRANTED PUBLIC access to those tables
---> and all works as predicted only slow (10x slower!).

As I understand it, RLS took time to get implemented in postgreSQL for
may reasons, one of which was the requirement to prevent "not belonging"
rows from leaking into the query results of library buffers. Eventually,
this was somehow achieved.

FMHE (for my eyes) the most striking change the policy (as of step 5)
introduces is a change from "access denied" error, which GRANT would
raise when it declines access, to a "silent omission", which POLICY does
... AT THE SAME SITUATION.

This lead me to the following conclusions:
1. in the pass (like I was implementing poor mans RLS with rules), I
found it very useful for some GRANTs to silently omit access to object
instead of raising an error. But this is impossible, isn't it?

2. in particular, I thought I could partition a table (using
inheritance) and do RLS on GRANT/REVOKE into individual partitions. It
would certainly hard limit any rows leaking into library buffers,
particularly if partitions are on separate tablespaces. But
unfortunately GRANT/REVOKE did raises an error, (and doesn't simply
silently ignore those not granted).

3. So, what if one could change the way GRANT/REVOKE behave when denying
access?

4. one feature necesary for such scenario to work, is the ability to
select one particular (single) ROLE, from all the ROLEs a particular
session__user has, that would "solely" be used for RLS checking of such
"silent GRANT/REVOKE" validates. (a multitenet database). I mean here
something along the lines of: "SET ROLE  [FOR RLS]".

5. the above should come in pair with "CHECK (RLS = )" at partition
level. This way, when postgresql-session does NOT HAVE the "role for
rls" set, all GRANT/REVOKE would work as usual, i.e.: ignore that CHECK
and normally raise "access denied".

IMHO, such implementation would not suffer performance hit, that current
implementation of POLICIES do.

So, I have two questions here:
1. does the above scenario look like safe enough regarding unauthorised
rows leaking (and as substitute for POLICIES)?
2. would it be feasible to add such variant of RLS, should one attempt
to implement it? (i.e. would the community accept it?).

Those questions come from my bad experience with POLICY performance.
Unfortunatly I did that test like half a year ago, so I don't have
results at hand to quote them, but should anybody be interested, I may
try to do it again in a couple of days.

with best regards,

-R

Allowing John to Drop Triggers On Chad's Tables

[Fred Habash]

Based on my research, There are the options I have identified to allow user
'John' to drop a trigger on a table owner by 'Chad' ..

1. Make John a superuser
2. grant chad to john ;
3. Have Chad create a wrapper definer-rights procedure to drop triggers &
grant execute to John.
4. Give John Chad's password!

None of these options appeal to me. If there is a 'grant trigger' why is
there not a 'grant drop trigger'?

Any other options since Chad has one table to allow John to drop triggers
on?


Thank you

Re: Fwd: row level security (RLS)

[Laurenz Albe]

On Mon, 2021-03-15 at 16:28 +0100, Rafal Pietrak wrote:
> 5. My experiments with RLS was like following:
> - I've implemented a STABLE function, that returns INTEGER 1/0
> - I've linked that function as POLICY to my tables
> - I've GRANTED PUBLIC access to those tables
> ---> and all works as predicted only slow (10x slower!).
>
> [lots of questions about how to solve this is some other way]
>
> Those questions come from my bad experience with POLICY performance.

You should figure out why RLS was so slow.

The key to this is "EXPLAIN (ANALYZE, BUFFERS)" for the query -
that will tell you what is slow and why, so that you can tackle the
problem.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Postgres vacuum taking gitlab down ???

[RaviKrishna]

check the details here:
https://gitlab.com/gitlab-com/gl-infra/production/-/issues/3962

I noticed it when my git push was timing out.




--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Need help on understanding of wal_keep-segments and max_wal_size dependency.

[Laurenz Albe]

On Mon, 2021-03-15 at 10:00 +, Swati yadav wrote:
> We are using streaming replication without  feature archiving and replication 
> slots.
>
> Could you please help us in understanding how replication will works
> when I set max_wal_size to 1GB, wal_keep_segments  set to 300.
> 
> In this scenario, replication is getting failed.  Could you please brief
>  us the causes of failure and how can we fix this issue, apart from using
>  the feature archiving and replication slots.

"wal_keep_size" is irrelevant to the question.

"wal_keep_segments = 300" makes PostgreSQL keep 300 old WAL segments around
so that standby servers can catch up after a bit of downtime.

If, however, the downtime is long enough that the standby needs WAL that
is in an even earlier WAL segment that has already been archived and deleted,
the standby won't be able to catch up any more.

If you don't want to use "restore_command" or replication slots, your
only option is to increase "wal_keep_segments".

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: Refcursor

[Laurenz Albe]

On Mon, 2021-03-15 at 13:58 +0200, Yambu wrote:
> May I know why the below takes so long?
> 
> 
> BEGIN; 
> select * from func1() ; 
> fetch all from ""; 
> end;  
> 
> Select *  from func1() ;   on its own is very fast

The second just fetches a cursor for the query, the
first executes the query.  Executing the query is what
can take long.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: Postgres crashes at memcopy() after upgrade to PG 13.

[Avinash Kumar]

Hi Thomas,

On Sun, Mar 14, 2021 at 10:01 PM Avinash Kumar 
wrote:

> Hi Thomas,
>
> On Sun, Mar 14, 2021 at 9:40 PM Thomas Munro 
> wrote:
>
>> On Mon, Mar 15, 2021 at 1:29 PM Avinash Kumar
>>  wrote:
>> > Is this expected when replication is happening between PostgreSQL
>> databases hosted on different OS versions like Ubuntu 16 and Ubuntu 20 ?
>> Or, do we think this is some sort of corruption ?
>>
>> Is this index on a text datatype, and using a collation other than "C"?
>>
> Its en_US.UTF-8
>


> Also the datatype is bigint
>


>
>

>> https://wiki.postgresql.org/wiki/Locale_data_changes
>>
>> Not that I expect it to crash if that's the cause, I thought it'd just
>> get confused.
>>
> On Ubuntu 16 server,
>
> *$* ldd --version
>
> ldd (Ubuntu GLIBC 2.23-0ubuntu11.2) 2.23
>
> On New Server Ubuntu 20,
>
> *$* ldd --version
>
> ldd (Ubuntu GLIBC 2.31-0ubuntu9.2) 2.31
>
>
> --
> Regards,
> Avi.
>


-- 
Regards,
Avinash Vallarapu
+1-902-221-5976

Re: Postgres crashes at memcopy() after upgrade to PG 13.

[Avinash Kumar]

Hi Thomas,

On Sun, Mar 14, 2021 at 9:40 PM Thomas Munro  wrote:

> On Mon, Mar 15, 2021 at 1:29 PM Avinash Kumar
>  wrote:
> > Is this expected when replication is happening between PostgreSQL
> databases hosted on different OS versions like Ubuntu 16 and Ubuntu 20 ?
> Or, do we think this is some sort of corruption ?
>
> Is this index on a text datatype, and using a collation other than "C"?
>
Its en_US.UTF-8

>
> https://wiki.postgresql.org/wiki/Locale_data_changes
>
> Not that I expect it to crash if that's the cause, I thought it'd just
> get confused.
>
On Ubuntu 16 server,

*$* ldd --version

ldd (Ubuntu GLIBC 2.23-0ubuntu11.2) 2.23

On New Server Ubuntu 20,

*$* ldd --version

ldd (Ubuntu GLIBC 2.31-0ubuntu9.2) 2.31


-- 
Regards,
Avi.

Re: Postgres crashes at memcopy() after upgrade to PG 13.

[Thomas Munro]

On Mon, Mar 15, 2021 at 1:29 PM Avinash Kumar
 wrote:
> Is this expected when replication is happening between PostgreSQL databases 
> hosted on different OS versions like Ubuntu 16 and Ubuntu 20 ? Or, do we 
> think this is some sort of corruption ?

Is this index on a text datatype, and using a collation other than "C"?

https://wiki.postgresql.org/wiki/Locale_data_changes

Not that I expect it to crash if that's the cause, I thought it'd just
get confused.

Refcursor

[Yambu]

Hi

I new to cursors

May I know why the below takes so long?


BEGIN;
select * from func1() ;
fetch all from "";
end;

Select *  from func1() ;   on its own is very fast

Need help on understanding of wal_keep-segments and max_wal_size dependency.

[Swati yadav]

Hello Team


We are using streaming replication without  feature archiving and replication 
slots.


Could you please help us in understanding how replication will works when I set 
max_wal_size to 1GB, wal_keep_segments  set to 300?.

?In this scenario, replication is getting failed.  Could you please brief us 
the causes of failure and how can we fix this issue, apart from using the 
feature archiving? and replication slots.





Thanks & Regards
Swati Yadav | Xoriant Solutions Pvt. Ltd.
5th floor, Amar Paradigm, Baner Road, Baner,
Near D'mart, Pune - 411 045
http://www.xoriant.com