Re: Grants not working on partitions

[Adrian Klaver]

On 9/28/24 04:02, Lok P wrote:

Hi,
While we are creating any new tables, we used to give SELECT privilege 
on the newly created tables using the below command. But we are seeing 
now , in case of partitioned tables even if we had given the privileges 
in the same fashion, the user is not able to query specific partitions 
but only the table. Commands like "select * from 
schema1. " are erroring out with the "insufficient 
privilege" error , even if the partition belongs to the same table.


Grant SELECT ON  to ;

Grant was seen as a one time command which needed while creating the 
table and then subsequent partition creation for that table was handled 
by the pg_partman extension. But that extension is not creating or 
copying any grants on the table to the users. We were expecting , once 
the base table is given a grant , all the inherited partitions will be 
automatically applied to those grants. but it seems it's not working 
that way. So is there any other way to handle this situation?



The docs are there for a reason:

https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman.md#child-table-property-inheritance

"Privileges & ownership are NOT inherited by default. If enabled by 
pg_partman, note that this inheritance is only at child table creation 
and isn't automatically retroactive when changed (see 
reapply_privileges()). Unless you need direct access to the child 
tables, this should not be needed. You can set the inherit_privileges 
option if this is needed (see config table information below)."



And:

"reapply_privileges(
p_parent_table text
)
RETURNS void

This function is used to reapply ownership & grants on all child 
tables based on what the parent table has set.
Privileges that the parent table has will be granted to all child 
tables and privileges that the parent does not have will be revoked 
(with CASCADE).
Privileges that are checked for are SELECT, INSERT, UPDATE, DELETE, 
TRUNCATE, REFERENCES, & TRIGGER.
Be aware that for large partition sets, this can be a very long 
running operation and is why it was made into a separate function to run 
independently. Only privileges that are different between the parent & 
child are applied, but it still has to do system catalog lookups and 
comparisons for every single child partition and all individual 
privileges on each.
p_parent_table - parent table of the partition set. Must be schema 
qualified and match a parent table name already configured in pg_partman.

"




In other databases(say like Oracle) we use to create standard 
"roles"(Read_role, Write_role etc..) and then provide grants to the user 
through those roles. And the objects were given direct grants to those 
roles. Similarly here in postgres we were granting "read" or "write" 
privileges on objects to the roles and letting the users login to the 
database using those roles and thus getting all the read/write 
privileges assigned to those roles. Are we doing anything wrong?


Regards
Lok


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Grants not working on partitions

[Adrian Klaver]

On 9/28/24 08:56, Lok P wrote:



On Sat, Sep 28, 2024 at 8:46 PM Adrian Klaver > wrote:


On 9/28/24 04:02, Lok P wrote:
 > Hi,
 > While we are creating any new tables, we used to give SELECT
privilege
 > on the newly created tables using the below command. But we are
seeing
 > now , in case of partitioned tables even if we had given the
privileges
 > in the same fashion, the user is not able to query specific
partitions
 > but only the table. Commands like "select * from
 > schema1. " are erroring out with the "insufficient
 > privilege" error , even if the partition belongs to the same table.
 >
 > Grant SELECT ON  to ;
 >
 > Grant was seen as a one time command which needed while creating the
 > table and then subsequent partition creation for that table was
handled
 > by the pg_partman extension. But that extension is not creating or
 > copying any grants on the table to the users. We were expecting ,
once
 > the base table is given a grant , all the inherited partitions
will be
 > automatically applied to those grants. but it seems it's not working
 > that way. So is there any other way to handle this situation?


The docs are there for a reason:


https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman.md#child-table-property-inheritance
 


"Privileges & ownership are NOT inherited by default. If enabled by
pg_partman, note that this inheritance is only at child table creation
and isn't automatically retroactive when changed (see
reapply_privileges()). Unless you need direct access to the child
tables, this should not be needed. You can set the inherit_privileges
option if this is needed (see config table information below)."


And:

"reapply_privileges(
      p_parent_table text
)
RETURNS void

      This function is used to reapply ownership & grants on all child
tables based on what the parent table has set.
      Privileges that the parent table has will be granted to all child
tables and privileges that the parent does not have will be revoked
(with CASCADE).
      Privileges that are checked for are SELECT, INSERT, UPDATE,
DELETE,
TRUNCATE, REFERENCES, & TRIGGER.
      Be aware that for large partition sets, this can be a very long
running operation and is why it was made into a separate function to
run
independently. Only privileges that are different between the parent &
child are applied, but it still has to do system catalog lookups and
comparisons for every single child partition and all individual
privileges on each.
      p_parent_table - parent table of the partition set. Must be
schema
qualified and match a parent table name already configured in
pg_partman.
"



Thank you. I was not aware about this function which copies the grants 
from parent to child ,so we can give a call to this function at the end 
of the pg_partman job call which is happening through the cron job. But 
I see , the only issue is that this function only has one parameter 
"p_parent_table" but nothing for "child_table" and that means it will 
try to apply grants on all the childs/partitions which have been created 
till today and may already be having the privileges already added in them.


And we have just ~60 partitions in most of the table so hope that will 
not take longer but considering we create/purge one partition daily for 
each partition table using the pg_partman, every time we give it a call, 
it will try to apply/copy the grants on all the partitions(along with 
the current day live partition), will it cause the existing running 
queries on the live partitions to hard parse? or say will it cause any 
locking effect when it will try to apply grant on the current/live 
partitions , which must be inserted/updated/deleted data into or being 
queries by the users?





1) You seem to have missed the first part of the answer:

"Privileges & ownership are NOT inherited by default. If enabled by 
pg_partman, note that this inheritance is only at child table creation 
and isn't automatically retroactive when changed (see 
reapply_privileges()). Unless you need direct access to the child 
tables, this should not be needed. **You can set the 
inherit_privileges** option if this is needed (see config table 
information below)."


Read ** ...** part.

2) This is open source the code is available for you to see what is 
actually going on:


https://github.com/pgpartman/pg_partman/blob/master/sql/functions/reapply_privileges.sql

which in turn uses:

https://github.com/pgpartman/pg_partman/blob/master/sql/functions/apply_privileges.sql


3) This is something that is easily tested on you end.


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Synchronize the dump with a logical slot with --snapshot

[Justin]

On Sat, Sep 28, 2024 at 1:04 AM Durgamahesh Manne 
wrote:

> Hi Team
>
> Can anyone respond to my question from respected team members ?
>
> Durga Mahesh
>
> On Thu, Sep 26, 2024 at 2:23 AM Durgamahesh Manne <
> maheshpostgr...@gmail.com> wrote:
>
>> Hi Team
>>
>> --snapshot=snapshotname
>> (Use the specified synchronized snapshot when making a dump of the
>> database
>>
>> This option is useful when needing to synchronize the dump with a logical
>> replication slot) as per the pgdg
>>
>> How do we synchronize the dump with a logical replication slot with
>> --snapshot?
>>
>> I am using the postgresql 14 version which supports only
>> pg_create_logical_replication_slot. How to generate a internal snapshot
>> with it ?
>>
>> Below CREATE_REPLICAION_SLOT not supported by postgresql 14
>>
>> example osdb_lsr=# CREATE_REPLICATION_SLOT lsr_sync_01 LOGICAL pgoutput;
>>   slot_name | consistent_point | snapshot_name | output_plugin
>> -+--+-+---
>>  lsr_sync_01 | 0/C000110 | 0003-0002-1 | pgoutput
>>
>> Regards,
>>
>> Durga Mahesh
>>
>>
>>
>> On Fri, 20 Sept, 2024, 01:27 Durgamahesh Manne, <
>> maheshpostgr...@gmail.com> wrote:
>>
>>> Hi Team
>>>
>>> --snapshot=*snapshotname*
>>>
>>> (Use the specified synchronized snapshot when making a dump of the
>>> database
>>>
>>> This option is useful when needing to synchronize the dump with a
>>> logical replication slot) as per the pgdg
>>>
>>> How do we synchronize the dump with a logical replication slot
>>> with --snapshot?
>>>
>>> I am using the postgresql 14 version which supports only
>>> pg_create_logical_replication_slot. How to generate a snapshot with it ?
>>>
>>> Below CREATE_REPLICAION_SLOT not supported by postgresql 14
>>>
>>> example osdb_lsr=# CREATE_REPLICATION_SLOT lsr_sync_01 LOGICAL pgoutput;
>>>   slot_name  | consistent_point |snapshot_name| output_plugin
>>> -+--+-+---
>>>  lsr_sync_01 | 0/C000110| 0003-0002-1 | pgoutput
>>>
>>> Regards,
>>>
>>> Durga Mahesh
>>>
>>

Hi Durgamahesh,

I am not sure what you are after with matching pg_dump and replication slot
together unless you are trying to get a dump to handle the initial data
sync. There is not a benefit to doing that as the WAL is going to build up
on the publisher...

You have to create a snapshot using the export function

https://www.postgresql.org/docs/current/sql-set-transaction.html

https://www.postgresql.org/docs/17/functions-admin.html#FUNCTIONS-SNAPSHOT-SYNCHRONIZATION

Then you can create the logical replication slot with using that slotname
option
https://www.postgresql.org/docs/17/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-SLOT-NAME
and no sync option.

Then you tell pg_dump to use that snapshot name snapshot with this option
--snapshot=snapshotname

https://www.postgresql.org/docs/current/app-pgdump.html

Once pg_restore is done on the destination , you can create a subscription
using that slotname option probably and specify copy_data = false.

Keep in mind the WAL will build up during this process,  not sure what the
benefit would be just allowing logical replication to do the initial sync.


Thanks
Justin

Re: Synchronize the dump with a logical slot with --snapshot

[Durgamahesh Manne]

Hi justin

I raised question based on the reference link
https://opensource-db.com/unlocking-initial-sync-for-logical-replication-in-aws-rds-for-postgresql/
  .. you can also go through it to see the steps
This worked on postgres 10version but on postgres 14 I can go through the
info you provided to implement the same

Thanks for your valuable information

Regards,
Durga Mahesh


On Sat, 28 Sept, 2024, 23:10 Justin,  wrote:

>
>
> On Sat, Sep 28, 2024 at 1:04 AM Durgamahesh Manne <
> maheshpostgr...@gmail.com> wrote:
>
>> Hi Team
>>
>> Can anyone respond to my question from respected team members ?
>>
>> Durga Mahesh
>>
>> On Thu, Sep 26, 2024 at 2:23 AM Durgamahesh Manne <
>> maheshpostgr...@gmail.com> wrote:
>>
>>> Hi Team
>>>
>>> --snapshot=snapshotname
>>> (Use the specified synchronized snapshot when making a dump of the
>>> database
>>>
>>> This option is useful when needing to synchronize the dump with a
>>> logical replication slot) as per the pgdg
>>>
>>> How do we synchronize the dump with a logical replication slot with
>>> --snapshot?
>>>
>>> I am using the postgresql 14 version which supports only
>>> pg_create_logical_replication_slot. How to generate a internal snapshot
>>> with it ?
>>>
>>> Below CREATE_REPLICAION_SLOT not supported by postgresql 14
>>>
>>> example osdb_lsr=# CREATE_REPLICATION_SLOT lsr_sync_01 LOGICAL pgoutput;
>>>   slot_name | consistent_point | snapshot_name | output_plugin
>>> -+--+-+---
>>>  lsr_sync_01 | 0/C000110 | 0003-0002-1 | pgoutput
>>>
>>> Regards,
>>>
>>> Durga Mahesh
>>>
>>>
>>>
>>> On Fri, 20 Sept, 2024, 01:27 Durgamahesh Manne, <
>>> maheshpostgr...@gmail.com> wrote:
>>>
 Hi Team

 --snapshot=*snapshotname*

 (Use the specified synchronized snapshot when making a dump of the
 database

 This option is useful when needing to synchronize the dump with a
 logical replication slot) as per the pgdg

 How do we synchronize the dump with a logical replication slot
 with --snapshot?

 I am using the postgresql 14 version which supports only
 pg_create_logical_replication_slot. How to generate a snapshot with it ?

 Below CREATE_REPLICAION_SLOT not supported by postgresql 14

 example osdb_lsr=# CREATE_REPLICATION_SLOT lsr_sync_01 LOGICAL pgoutput;
   slot_name  | consistent_point |snapshot_name| output_plugin
 -+--+-+---
  lsr_sync_01 | 0/C000110| 0003-0002-1 | pgoutput

 Regards,

 Durga Mahesh

>>>
>
> Hi Durgamahesh,
>
> I am not sure what you are after with matching pg_dump and replication
> slot together unless you are trying to get a dump to handle the initial
> data sync. There is not a benefit to doing that as the WAL is going to
> build up on the publisher...
>
> You have to create a snapshot using the export function
>
> https://www.postgresql.org/docs/current/sql-set-transaction.html
>
>
> https://www.postgresql.org/docs/17/functions-admin.html#FUNCTIONS-SNAPSHOT-SYNCHRONIZATION
>
> Then you can create the logical replication slot with using that slotname
> option
>
> https://www.postgresql.org/docs/17/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-SLOT-NAME
> and no sync option.
>
> Then you tell pg_dump to use that snapshot name snapshot with this option
> --snapshot=snapshotname
>
> https://www.postgresql.org/docs/current/app-pgdump.html
>
> Once pg_restore is done on the destination , you can create a subscription
> using that slotname option probably and specify copy_data = false.
>
> Keep in mind the WAL will build up during this process,  not sure what the
> benefit would be just allowing logical replication to do the initial sync.
>
>
> Thanks
> Justin
>
>

Context variable in application and trigger code

[yudhi s]

Hi,
We have a trigger function called from a trigger which executes before
inserting rows in the table (say TAB1). This trigger function does some
conversion of code to description and persists the description in the table
in respective columns. We want to keep this trigger as light as possible as
there will be 100's million rows inserted into this table from multiple
input sources and the conversion should only happen  the inserts which
happen from a specific input data stream.

There are 4-5 different input sources which will ingest data into this
table (some are using file based processing framework and some are using
other streaming technology). Say for example we want this description to
only be fetched for input source - 1, but not others.  We don't have any
such column persisted in the table which can be checked for the input data
stream such as this code to describe decoding can be made conditional.

Are there any techniques possible in which we can set some parameter or
session context variable in application code level to determine the input
data source, which can then be checked within the trigger function code at
the very first and thus will avoid querying the "CODE" table every time the
trigger executes?


*Existing sample trigger code:-*

CREATE OR REPLACE FUNCTION code_to_desc( )
RETURNS trigger
LANGUAGE plpgsql
AS $function$
declare code_description code.code%TYPE;
 begin
 select currency_description_text into code_description
 from code
where code = new.code_input;

  IF FOUND THEN NEW.code_input := code_description;
  END IF;

return NEW;
 end;
$function$;

Regards
Yudhi

Re: Context variable in application and trigger code

[Adrian Klaver]

On 9/28/24 14:37, yudhi s wrote:

Hi,
We have a trigger function called from a trigger which executes before 
inserting rows in the table (say TAB1). This trigger function does some 
conversion of code to description and persists the description in the 
table in respective columns. We want to keep this trigger as light as 
possible as there will be 100's million rows inserted into this table 
from multiple input sources and the conversion should only happen  the 
inserts which happen from a specific input data stream.


There are 4-5 different input sources which will ingest data into this 
table (some are using file based processing framework and some are using 
other streaming technology). Say for example we want this description to 
only be fetched for input source - 1, but not others. We don't have any 
such column persisted in the table which can be checked for the input 
data stream such as this code to describe decoding can be made conditional.


Are there any techniques possible in which we can set some parameter or 
session context variable in application code level to determine the 
input data source, which can then be checked within the trigger function 
code at the very first and thus will avoid querying the "CODE" table 
every time the trigger executes?


There is:

https://www.postgresql.org/docs/16/runtime-config-logging.html#GUC-APPLICATION-NAME

Though if you have multiple inputs happening concurrently I am not sure 
how you would sort out which is supplying the data for a given trigger 
instance. Also, by this time you are basically done anyway so I don't 
see how would save enough time.


Seems to me this calls for either doing the conversion in the 
application or include a throw way field in the stream data that 
indicates it is coming from the stream or stream to an intake table and 
then convert when transferring to final table.






*Existing sample trigger code:-*

CREATE OR REPLACE FUNCTION code_to_desc( )
RETURNS trigger
LANGUAGE plpgsql
AS $function$
declare code_description code.code%TYPE;
  begin
      select currency_description_text into code_description
      from code
     where code = new.code_input;

   IF FOUND THEN NEW.code_input := code_description;
END IF;

     return NEW;
  end;
$function$;

Regards
Yudhi


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Regarding publish_via_partiton_root with pglogical

[Greg Sabino Mullane]

Please do not spam the mailing lists with requests for follow ups. In this
particular case, you received an answer two days after you posted it. It
went to both psql-general, pgsql-in-general, and to you directly, so there
seems little excuse for missing it.

Also note that pglogical is a third-party extension and as such you may
have more success in the future asking the maintainers of it via its github
page (as I see you have done, although in a somewhat impolite way:
https://github.com/2ndQuadrant/pglogical/issues/487)

Cheers,
Greg

Faster `&&` intersection for sorted arrays

[Adrian Garcia Badaracco]

I was wondering if I could do better than `&&` for sorted arrays by doing
binary search and came up with some really interesting results.
I only did a quick spin of this, an LLM generated most of the code after I
gave it the algorithm and I don't plan on using it in production (I'm going
to normalize my schema instead) but I wanted to share this in case it's
useful to anyone.

https://gist.github.com/adriangb/1b68bb2e408423ddcb90fb0136a00ba8

Re: Grants not working on partitions

[Lok P]

On Sat, Sep 28, 2024 at 8:46 PM Adrian Klaver 
wrote:

> On 9/28/24 04:02, Lok P wrote:
> > Hi,
> > While we are creating any new tables, we used to give SELECT privilege
> > on the newly created tables using the below command. But we are seeing
> > now , in case of partitioned tables even if we had given the privileges
> > in the same fashion, the user is not able to query specific partitions
> > but only the table. Commands like "select * from
> > schema1. " are erroring out with the "insufficient
> > privilege" error , even if the partition belongs to the same table.
> >
> > Grant SELECT ON  to ;
> >
> > Grant was seen as a one time command which needed while creating the
> > table and then subsequent partition creation for that table was handled
> > by the pg_partman extension. But that extension is not creating or
> > copying any grants on the table to the users. We were expecting , once
> > the base table is given a grant , all the inherited partitions will be
> > automatically applied to those grants. but it seems it's not working
> > that way. So is there any other way to handle this situation?
>
>
> The docs are there for a reason:
>
>
> https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman.md#child-table-property-inheritance
>
> "Privileges & ownership are NOT inherited by default. If enabled by
> pg_partman, note that this inheritance is only at child table creation
> and isn't automatically retroactive when changed (see
> reapply_privileges()). Unless you need direct access to the child
> tables, this should not be needed. You can set the inherit_privileges
> option if this is needed (see config table information below)."
>
>
> And:
>
> "reapply_privileges(
>  p_parent_table text
> )
> RETURNS void
>
>  This function is used to reapply ownership & grants on all child
> tables based on what the parent table has set.
>  Privileges that the parent table has will be granted to all child
> tables and privileges that the parent does not have will be revoked
> (with CASCADE).
>  Privileges that are checked for are SELECT, INSERT, UPDATE, DELETE,
> TRUNCATE, REFERENCES, & TRIGGER.
>  Be aware that for large partition sets, this can be a very long
> running operation and is why it was made into a separate function to run
> independently. Only privileges that are different between the parent &
> child are applied, but it still has to do system catalog lookups and
> comparisons for every single child partition and all individual
> privileges on each.
>  p_parent_table - parent table of the partition set. Must be schema
> qualified and match a parent table name already configured in pg_partman.
> "
>
>
>
Thank you. I was not aware about this function which copies the grants from
parent to child ,so we can give a call to this function at the end of the
pg_partman job call which is happening through the cron job. But I see ,
the only issue is that this function only has one parameter
"p_parent_table" but nothing for "child_table" and that means it will try
to apply grants on all the childs/partitions which have been created till
today and may already be having the privileges already added in them.

And we have just ~60 partitions in most of the table so hope that will not
take longer but considering we create/purge one partition daily for each
partition table using the pg_partman, every time we give it a call, it will
try to apply/copy the grants on all the partitions(along with the current
day live partition), will it cause the existing running queries on the live
partitions to hard parse? or say will it cause any locking effect when it
will try to apply grant on the current/live partitions , which must be
inserted/updated/deleted data into or being queries by the users?

Grants not working on partitions

[Lok P]

Hi,
While we are creating any new tables, we used to give SELECT privilege on
the newly created tables using the below command. But we are seeing now ,
in case of partitioned tables even if we had given the privileges in the
same fashion, the user is not able to query specific partitions but only
the table. Commands like "select * from schema1. " are
erroring out with the "insufficient privilege" error , even if the
partition belongs to the same table.

Grant SELECT ON  to ;

Grant was seen as a one time command which needed while creating the table
and then subsequent partition creation for that table was handled by the
pg_partman extension. But that extension is not creating or copying any
grants on the table to the users. We were expecting , once the base table
is given a grant , all the inherited partitions will be automatically
applied to those grants. but it seems it's not working that way. So is
there any other way to handle this situation?

In other databases(say like Oracle) we use to create standard
"roles"(Read_role, Write_role etc..) and then provide grants to the user
through those roles. And the objects were given direct grants to those
roles. Similarly here in postgres we were granting "read" or "write"
privileges on objects to the roles and letting the users login to the
database using those roles and thus getting all the read/write privileges
assigned to those roles. Are we doing anything wrong?

Regards
Lok