Re: create policy statement USING clause

Thank you for the reply. I appreciate it very much. I checked the data for null in the column values, but I didn't any. I started over from the beginning with a fresh clone of the database, and followed the set up in ordered fashion, including a little routine to assure valid data in the column

Re: Fwd: A million users

On Wed, Nov 13, 2024, 4:42 PM Achilleas Mantzios - cloud < a.mantz...@cloud.gatewaynet.com> wrote: > > Exactly! In the later versions, security gets more and more refined and > strengthened. So ppl should think about moving away from "public" , and > start implementing finer grained schemes of sec

Fwd: A million users

-- Forwarded message - From: Kaare Rasmussen <> Hi A simple question before design. Would there be performance issues having perhaps a million users, each having several roles? I could imagine a user would have on average 10-20 roles. I tried to grant select permissions to 5000

Re: Fwd: Fwd: Postgres attach partition: AccessExclusive lock set on different tables depending on how attaching is performed

On 2024-Nov-10, Tom Lane wrote: > This surprised me a bit too, because I thought we took a > slightly-less-than-exclusive lock for FK additions or deletions. > Tracing through it, I find that CloneFkReferencing opens the > referenced relation with ShareRowExclusiveLock as I expected. > But then we

Re: Fwd: A million users

Hi Dominique Hi. Sure, that's a good point, and a best practice IMHO. But I already do that, and the original question remain, i.e. how does PostgreSQL with thousands or millions of roles? Sorry if my original post was unclear, but I don't expect that there will be much more than perhaps

Re: PostgreSQL logical replication

On Tue, Nov 12, 2024 at 7:47 PM Ron Johnson wrote: > The documentation tells you, down in the Notes section. > > https://www.postgresql.org/docs/16/sql-createpublication.html > Thank you.

DB wal file disabled --_Query

Hi Team, We have disabled walfile ion database level. While I do manual switch wal file generating in wal file location. Here is my question if we disable it. Walfile won't generate am i correct? Kindly clarify my doubts. postgres=# select name,setting from pg_settings where name like 'archive%

Re: DB wal file disabled --_Query

On 11/13/24 08:15, jayakumar s wrote: Hi Team, We have disabled walfile ion database level. While I do manual switch wal file generating in wal file location. Here is my question if we disable it. Walfile won't generate am i correct? Kindly clarify my doubts. postgres=# select name,setting

Re: DB wal file disabled --_Query

On Wed, Nov 13, 2024 at 9:16 AM jayakumar s wrote: > postgres=# select name,setting from pg_settings where name like 'archive%'; > name | setting > -+ > archive_command | (disabled) > You cannot disable WAL. What is disabled here is copying the WAL that

Re: Fwd: A million users

On Wed, Nov 13, 2024 at 11:29 AM Alvaro Herrera wrote: > On 2024-Nov-13, Vijaykumar Jain wrote: > > I tried to grant select permissions to 5000 different roles on one table, > > It failed with row size too big already at 2443. > > But you can grant select to one "reader" role, and grant that one r

Re: Fwd: A million users

On 2024-Nov-13, Vijaykumar Jain wrote: > I tried to grant select permissions to 5000 different roles on one table, > It failed with row size too big already at 2443. But you can grant select to one "reader" role, and grant that one role to however many other roles you want. This way you can have

Re: Fwd: A million users

On 11/13/24 12:29, Alvaro Herrera wrote: On 2024-Nov-13, Vijaykumar Jain wrote: I tried to grant select permissions to 5000 different roles on one table, It failed with row size too big already at 2443. But you can grant select to one "reader" role, and grant that one role to however many ot

Re: Fwd: A million users

On Wed, Nov 13, 2024, 5:00 PM Vijaykumar Jain < vijaykumarjain.git...@gmail.com> wrote: > > > On Wed, Nov 13, 2024, 4:42 PM Achilleas Mantzios - cloud < > a.mantz...@cloud.gatewaynet.com> wrote: > >> >> Exactly! In the later versions, security gets more and more refined and >> strengthened. So ppl

Re: Fwd: A million users

Dominique Devienne: Hi. Sure, that's a good point, and a best practice IMHO. But I already do that, and the original question remain, i.e. how does PostgreSQL with thousands or millions of roles? In my use case, 1000 LOGIN users, and 10'000 schema related ROLEs, is possible, with can translate to

Extract values from XML content

Good afternoon, I have a table 'metadata' with 2000 records. With one column 'id' and one column 'data' with XML content. I need to extract for all records the values regarding the Organisation names. I success in querying without error message thanks to this following sql query : SELECT id, xpath

work_mem RAM Accounting in PostgreSQL

Hello Everyone, I have some questions regarding how the "work_mem" parameter affects the overall RAM usage of PostgreSQL processes within a physical host or container. Each backend process during SQL execution may allocate N * "work_mem" simultaneously. For example, if "work_mem" is set to 32MB a

Help with restoring database from old version of PostgreSQL

I'm seeking help in restoring a database that was created with PostgreSQL 9.6. I backed it up successfully and have restored it before, but that was when PostgreSQL 9.6 was still supported. After going through the installation of PostgreSQL 16.3, I opened up pgAdmin 4 to try to verify the PostgreSQ

Re: Extract values from XML content

On Wednesday, November 13, 2024, celati Laurent wrote: > > > SELECT id, > xpath('/contact/CI_ResponsibleParty/organisationName/CharacterString/text()', > > CAST(data AS XML)) AS organisation_name > > > > codeList="http://standards.iso.org/iso/19115/resources/Code

Re: Fwd: A million users

On 2024-Nov-13, Kaare Rasmussen wrote: > Sorry if my original post was unclear, but I don't expect that there > will be much more than perhaps a hundred roles. Each may have from a > few up to a million users in them, though. In Postgres, a user is a role. So if you have a hundred roles and a mi

Re: Extract values from XML content

Am 13.11.24 um 15:58 schrieb celati Laurent: I have a table 'metadata' with 2000 records. With one column 'id' and one column 'data' with XML content. I need to extract for all records the values regarding the Organisation names. I success in querying without error message thanks to this followi

Re: Help with restoring database from old version of PostgreSQL

On 11/13/24 10:50, Catherine Frock wrote: I'm seeking help in restoring a database that was created with PostgreSQL 9.6. I backed it up successfully and have restored it before, but that was when PostgreSQL 9.6 was still supported. After going Do you still have the Postgres 9.6 instance runni

Re: Help with restoring database from old version of PostgreSQL

On Wed, Nov 13, 2024 at 1:50 PM Catherine Frock wrote: > I'm seeking help in restoring a database that was created with PostgreSQL > 9.6. I backed it up successfully and have restored it before, but that was > when PostgreSQL 9.6 was still supported. After going through the > installation of Post

Row level security policy

Given a database table with one policy statement FOR SELECT applied, it is necessary to apply additional policy statements for insert, update, and delete operations? My testing indicates that this is case but I haven’t found an explanation of this requirement in the documentation. - Mark

Re: Row level security policy

On Wednesday, November 13, 2024, Mark Phillips wrote: > Given a database table with one policy statement FOR SELECT applied, it is > necessary to apply additional policy statements for insert, update, and > delete operations? > It isn’t necessary but most conventional use cases would involve est

Re: Row level security policy

Thank you. I will revisit my test cases to be sure I have the use cases covered. - Mark, out and about.On Nov 13, 2024, at 5:36 PM, David G. Johnston wrote:On Wednesday, November 13, 2024, Mark Phillips wrote:Given a database table with one policy statement FOR SELECT app

Re: Row level security policy

Well, things did not work as I expected, which means there is more for me to learn. I am new to RLS usage. I want to implement this in a proper manner, so is the behavior described below correct? Are there other aspects of this I need to study? Thanks, in advance. Advice and links to articles a