Re: "permission denied to COPY to or from an external program" even with GRANT pg_execute_server_program

> > > Pretty sure since you choose not to allow justintestin to inherit stuff > you will need to issue a “set role to pg_execute_server_program” before you > attempt the copy command. > > David J. > That was it! Blind paranoia bites my rear again. Thanks!

Re: "permission denied to COPY to or from an external program" even with GRANT pg_execute_server_program

Chema writes: > been banging my head against this one for a couple days. Googling and > StackExchange were just as useful, so you're my last hope. I've been > unable to get a non-admin user to run Copy From Program even after granting > pg_execute_server_program, and everything else I could thin

Re: "permission denied to COPY to or from an external program" even with GRANT pg_execute_server_program

On Wednesday, June 12, 2024, Chema wrote: > > Create Role justintestin noinherit login password 'qwer'; > > > GRANT pg_execute_server_program TO justintestin; > > > Pretty sure since you choose not to allow justintestin to inherit stuff you will need to issue a “set role to pg_execute_server_prog

"permission denied to COPY to or from an external program" even with GRANT pg_execute_server_program

Dear Postgreezers, been banging my head against this one for a couple days. Googling and StackExchange were just as useful, so you're my last hope. I've been unable to get a non-admin user to run Copy From Program even after granting pg_execute_server_program, and everything else I could think o

Is NVMe RAID useless (performance-wise) with PostgreSQL?

Hi all, While building a new PostgreSQL server, I realized that the performance with a single disk is the same or better than with a RAID0 4-disk array. All benchmarks were conducted using pgbench with a scaling factor of 2000. For a typical run with pgbench -j 4 -c 512 -P 60 -r -T 300 -b tpcb-

Re: Definging columns for INSERT statements

On 6/12/24 16:24, Rich Shepard wrote: On Wed, 12 Jun 2024, Adrian Klaver wrote: Assuming 'people_person_nbr_seq' is the sequence attached to person_nbr and the other DEFAULTs are the column defaults then the syntax would be: INSERT INTO people (person_nbr, lname, fname, job_title, company_nbr,

Re: Definging columns for INSERT statements

On Wed, 12 Jun 2024, Adrian Klaver wrote: Assuming 'people_person_nbr_seq' is the sequence attached to person_nbr and the other DEFAULTs are the column defaults then the syntax would be: INSERT INTO people (person_nbr, lname, fname, job_title, company_nbr,loc_nbr, direct_phone, cell_phone,emai

Re: PG16.1 security breach?

On Wed, Jun 12, 2024 at 3:57 PM Tom Lane wrote: > Ron Johnson writes: > > On Wed, Jun 12, 2024 at 4:36 PM David G. Johnston < > > david.g.johns...@gmail.com> wrote: > >> I think my point is that a paragraph like the following may be a useful > >> addition: > >> > >> If one wishes to remove the d

Re: Definging columns for INSERT statements

On 6/12/24 14:11, Rich Shepard wrote: I have > 100 rows to add to a table using INSERT INTO statements. I want the PK to be the next value in the sequence. Would this be the appropriate syntax for the columns to be entered? INSERT INTO people (person_nbr DEFAULT('people_person_nbr_seq'),lname,

Re: PG16.1 security breach?

Ron Johnson writes: > On Wed, Jun 12, 2024 at 4:36 PM David G. Johnston < > david.g.johns...@gmail.com> wrote: >> I think my point is that a paragraph like the following may be a useful >> addition: >> >> If one wishes to remove the default privilege granted to public to execute >> all newly crea

Re: UPDATE with multiple WHERE conditions

On Wed, 12 Jun 2024, Rob Sargent wrote: Add "begin;" to that and try it.  If you don't get exactly UPDATE 295 reported, then "rollback;"; Got it, thanks. Rich

Re: UPDATE with multiple WHERE conditions

On Wed, 12 Jun 2024, Ron Johnson wrote: A plain UPDATE might work. UPDATE to_be_updated a SET bool_col = true FROM other_table b WHERE a.pk = b.pk AND b.field3 = mumble; (You can join them, right?) Thanks, Ron. Rich

Re: Definging columns for INSERT statements

On Wed, 12 Jun 2024, David G. Johnston wrote: INSERT INTO people (person_nbr DEFAULT('people_person_nbr_seq'),lname,fname,job_title DEFAULT 'Contact',company_nbr,loc_nbr,direct_phone,cell_phone,email,active DEFAULT('true')) VALUES https://www.postgresql.org/docs/current/sql-insert.html Not s

Re: UPDATE with multiple WHERE conditions

On Wed, 12 Jun 2024, David G. Johnston wrote: I'll often just use a spreadsheet to build the 295 update commands and copy-paste them into psql or whatnot. David, I'll create and use a temporary table. Thanks, Rich

Re: Definging columns for INSERT statements

On Wed, Jun 12, 2024 at 2:11 PM Rich Shepard wrote: > I have > 100 rows to add to a table using INSERT INTO statements. I want > the > PK to be the next value in the sequence. Would this be the appropriate > syntax for the columns to be entered? > The whole point of the server is to parse text a

Re: UPDATE with multiple WHERE conditions

On Wed, Jun 12, 2024 at 2:28 PM Rich Shepard wrote: > I have a table with 3492 rows. I want to update a boolean column from > 'false' to 'true' for 295 rows based on the value of another column. > > Is there a way to access a file with those condition values? > I'll often just use a spreadsheet

Re: PG16.1 security breach?

On Wed, Jun 12, 2024 at 2:37 PM Ron Johnson wrote: > On Wed, Jun 12, 2024 at 4:36 PM David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> On Mon, Jun 10, 2024 at 2:21 AM Laurenz Albe >> wrote: >> >>> > How is it that the default privilege granted to public doesn’t seem to >>> care who t

Re: UPDATE with multiple WHERE conditions

On 6/12/24 15:48, Ron Johnson wrote: On Wed, Jun 12, 2024 at 5:28 PM Rich Shepard wrote: I have a table with 3492 rows. I want to update a boolean column from 'false' to 'true' for 295 rows based on the value of another column. Is there a way to access a file with those conditio

Re: UPDATE with multiple WHERE conditions

On Wed, Jun 12, 2024 at 5:28 PM Rich Shepard wrote: > I have a table with 3492 rows. I want to update a boolean column from > 'false' to 'true' for 295 rows based on the value of another column. > > Is there a way to access a file with those condition values? If not, should > I create a temporary

Re: PG16.1 security breach?

On Wed, Jun 12, 2024 at 4:36 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Mon, Jun 10, 2024 at 2:21 AM Laurenz Albe > wrote: > >> > How is it that the default privilege granted to public doesn’t seem to >> care who the object creator >> > is yet when revoking the grant one suppo

Re: Defining columns for INSERT statements

On Wed, 12 Jun 2024, Rich Shepard wrote: VALUES (nextval('people_person_nbr_seq'), ... Correction. Rich

Re: UPDATE with multiple WHERE conditions

On Thu, 13 Jun 2024, Muhammad Salahuddin Manzoor wrote: You can use Temporary table. You could create a temporary table with one column containing the condition values and then use it to update your main table. This approach can be more flexible and cleaner than writing a script with multiple up

Re: UPDATE with multiple WHERE conditions

Greetings, You can use Temporary table. You could create a temporary table with one column containing the condition values and then use it to update your main table. This approach can be more flexible and cleaner than writing a script with multiple update statements. -- Create a temporary table w

UPDATE with multiple WHERE conditions

I have a table with 3492 rows. I want to update a boolean column from 'false' to 'true' for 295 rows based on the value of another column. Is there a way to access a file with those condition values? If not, should I create a temporary table with one column containing those values, or do I write

Definging columns for INSERT statements

I have > 100 rows to add to a table using INSERT INTO statements. I want the PK to be the next value in the sequence. Would this be the appropriate syntax for the columns to be entered? INSERT INTO people (person_nbr DEFAULT('people_person_nbr_seq'),lname,fname,job_title DEFAULT 'Contact',compa

Re: PG16.1 security breach?

On Mon, Jun 10, 2024 at 2:21 AM Laurenz Albe wrote: > > How is it that the default privilege granted to public doesn’t seem to > care who the object creator > > is yet when revoking the grant one supposedly can only do so within the > scope of a single role? > > I don't understand what you wrote.

Re: Question about UNIX socket connections and SSL

> On 12 Jun 2024, at 21:17, Tom Lane wrote: > > Casey & Gina writes: >> So why can't I use SSL when connecting from a client to a UNIX socket? > > (1) It'd add overhead without adding any security. Data going through > a UNIX socket will only pass through the local kernel, and if that's > comp

Re: Question about UNIX socket connections and SSL

Casey & Gina writes: > So why can't I use SSL when connecting from a client to a UNIX socket? (1) It'd add overhead without adding any security. Data going through a UNIX socket will only pass through the local kernel, and if that's compromised then it's game over anyway. (2) I'm less sure abou

Re: DROP COLLATION vs pg_collation question

> > DROP COLLATION IF EXISTS pg_catalog."" > > Yes, that will delete a row from "pg_collation". Many thanks. > Note that with DROP COLLATION you can only remove collations > that belong to the encoding of your current database. A-ha ! Can that bit be found anywhere in the docs ? IOW, t

Question about UNIX socket connections and SSL

It seems that libpq (maybe?) disables SSL when connecting through a UNIX socket to the database. My setup involves a HA database cluster managed by Patroni. To route RW or RO connections to the correct node(s), we use haproxy, running locally on each application node. In the interest of being

Re: Questions on logical replication

On Tue, Jun 11, 2024 at 5:43 PM Koen De Groote wrote: > > If there are any errors during the replay of WAL such as missing indexes > for Replica Identities during an Update or Delete this will cause the main > subscriber worker slot on the publisher to start backing up WAL files > > And also if

Re: Does trigger only accept functions?

On Tue, 11 Jun 2024 at 18:25, Ron Johnson wrote: Since all the functions are going to be similar, I'd write a shell script > to generate all the triggers, one per relevant. If you're going to record > every field, then save effort, and don't bother enumerating them. You'll > need to dig into th

Re: postgres table statistics

Chandy G schrieb am 12.06.2024 um 09:47: > Eventhough pg jdbc driver provides a way to set fetch size to tune > the driver to achieve better throughput, the JVM fails at the driver > level when records of large size (say 200mb each) flows through. > this forces to reduce the fetch size (if were

Re: Oracle Linux 9 Detected RPMs with RSA/SHA1 signature

On 6/12/24 02:54, Hans Schou wrote: Hi On my test server I have Oracle Linux 8.10 installed. Here I have installed postgresql 16.1 from postgresql.org repository. Upgrade to Oracle Linux 9: When doing a »leapp preupgrade --oraclelinux« I get the message below. I want

Oracle Linux 9 Detected RPMs with RSA/SHA1 signature

Hi On my test server I have Oracle Linux 8.10 installed. Here I have installed postgresql 16.1 from postgresql.org repository. Upgrade to Oracle Linux 9: When doing a »leapp preupgrade --oraclelinux« I get the message below. I want to have postgresql.org as my repo for PostgreSQL and Oracle Linu

Re: postgres table statistics

On Wed, Jun 12, 2024 at 3:48 AM Chandy G wrote: > Hi, > We have postgres 13.9 running with tables thats got billions of records > of varying sizes. Eventhough pg jdbc driver provides a way to set fetch > size to tune the driver to achieve better throughput, the JVM fails at the > driver level

postgres table statistics

Hi,   We have postgres 13.9 running with tables thats got billions of records of varying sizes. Eventhough pg jdbc driver  provides a way to set fetch size to tune the driver to achieve better throughput, the JVM fails at the driver level when records of large size (say 200mb each) flows throug

Re: DROP COLLATION vs pg_collation question

On Tue, 2024-06-11 at 23:15 +0200, Karsten Hilbert wrote: > maybe a naive question but I was unable to find an answer in > the fine manual (sv_SE being an example) > > Does running > > DROP COLLATION IF EXISTS pg_catalog."sv_SE" > > also remove the corresponding row from pg_collation (assu