Bryn is retiring. Last day at work Friday 18-Aug-2023

presently using my private email address. Regards, Bryn Llewellyn

Re: Using "exit" to bring "goto" functionality.

> pavel.steh...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> As it happens, Oracle's PL/SQL has a "goto" statement. But PL/pgSQL does >> not. (I assume that this is because "goto" is considered a bad thing.) But >> PL/SQL programmers do use it. However, the doc section: > > The reason

Re: Using "exit" to bring "goto" functionality.

> x...@thebuild.com wrote: > >> b...@yugabyte.com wrote: >> >> What is the rationale for supporting what seems to be on its face this >> strange functionality? > > It allows you to EXIT or CONTINUE a loop that is not the innermost one, by > naming the label of an outer loop. One can debate

Using "exit" to bring "goto" functionality.

I wanted to see what error(s) would be reported if "exit" or "continue" is used, in PL/pgSQL, outside of a loop—and in particular if these were reported as syntax errors or runtime errors. There were no surprises with "continue". But I was surprised by this: create procedure p() language

Re: What happened to the tip "It is good practice to create a role that has the CREATEDB and CREATEROLE privileges..."

laurenz.a...@cybertec.at wrote: > >> b...@yugabyte.com wrote: >> >> I do see that a role that has "createdb" and "createrole" is pretty powerful >> because, for example, a role with these attributes can use "set role" to >> become any other non-superuser (see the example below). > > A user

Re: What happened to the tip "It is good practice to create a role that has the CREATEDB and CREATEROLE privileges..."

> adrian.kla...@aklaver.com wrote: > >> b...@yugabyte.com wrote: >> >> Here's the examples that I mentioned. Please confirm that the changes >> brought by the commit referred to above won't change how it behaves in >> Version 15.2. > > The commit was over only documentation files > >

Re: What happened to the tip "It is good practice to create a role that has the CREATEDB and CREATEROLE privileges..."

> jer...@musicsmith.net wrote: > >> b...@yugabyte.com wrote: >> >> This tip >> >> « >> It is good practice to create a role that has the CREATEDB and CREATEROLE >> privileges, but is not a superuser, and then use this role for all routine >> management of databases and roles. This approach

What happened to the tip "It is good practice to create a role that has the CREATEDB and CREATEROLE privileges..."

This tip « It is good practice to create a role that has the CREATEDB and CREATEROLE privileges, but is not a superuser, and then use this role for all routine management of databases and roles. This approach avoids the dangers of operating as a superuser for tasks that do not really require

Re: PL/pgSQL's "open cur for execute" provides workarouned... Re: cursors with prepared statements

> adrian.kla...@aklaver.com wrote: > >> b...@yugabyte.com wrote: >> >> It seems that there must be different underlying mechanisms at work and that >> this explains why creating a cursor using SQL to execute a prepared >> statement fails but doing this using PL/pgSQL succeeds. What's going on

PL/pgSQL's "open cur for execute" provides workarouned... Re: cursors with prepared statements

> b...@yugabyte.com wrote: > >> david.g.johns...@gmail.com wrote: >> >>> b...@yugabyte.com wrote: >>> >>> I found this email from Peter Eisentraut: >>> https://www.postgresql.org/message-id/762cc764-74f0-13fb-77ed-16f91c90f40d%402ndquadrant.com >>> >>> It caused the 42601 error, « syntax error

Re: cursors with prepared statements

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> I found this email from Peter Eisentraut: >> https://www.postgresql.org/message-id/762cc764-74f0-13fb-77ed-16f91c90f40d%402ndquadrant.com >> >> It caused the 42601 error, « syntax error at or near “execute” ». So it >>

Re: cursors with prepared statements

I found this email from Peter Eisentraut: https://www.postgresql.org/message-id/762cc764-74f0-13fb-77ed-16f91c90f40d%402ndquadrant.com > I have developed a patch that allows declaring cursors over prepared > statements... This is an SQL standard feature. ECPG already supports it (with >

Re: Why does "fetch last from cur" always cause error 55000 for non-scrollable cursor?

> t...@sss.pgh.pa.us wrote: > >> b...@yugabyte.com wrote: >> >> But why does "fetch last" fail here: >> >> -- Test Two >> start transaction; >> declare cur no scroll cursor without hold for >> select g.val as k, g.val*100 as v >> from generate_series(1, 10) as

Why does "fetch last from cur" always cause error 55000 for non-scrollable cursor?

I’m using Version 15.2. I did this test: -- Test One start transaction; declare cur no scroll cursor without hold for select g.val as k, g.val*100 as v from generate_series(1, 10) as g(val) order by g.val; fetch first from cur; fetch next from cur; fetch first from cur; rollback;

Re: My tests show that a WITH HOLD cursor has subtly different semantics from a WITHOUT HOLD cursor

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> *Summary* >> >> My tests show that, when a WITHOUT HOLD cursor has to cache results (see >> Note 1), then the WHERE clause (if present) is stripped off the cursor's >> defining SELECT statement and the entire unrestricted

My tests show that a WITH HOLD cursor has subtly different semantics from a WITHOUT HOLD cursor

*Summary* My tests show that, when a WITHOUT HOLD cursor has to cache results (see Note 1), then the WHERE clause (if present) is stripped off the cursor's defining SELECT statement and the entire unrestricted result set is cached. But when a WITH HOLD cursor is used, then it’s the

Re: Is the PL/pgSQL refcursor useful in a modern three-tier app?

> adrian.kla...@aklaver.com wrote: > >> b...@yugabyte.com replied to laurenz.a...@cybertec.at: >> >> Thanks for the link to your SQL file at the line where you get the row count >> in the way that you describe... I noted that neither of these functions has >> a refcursor formal argument and

Re: Is the PL/pgSQL refcursor useful in a modern three-tier app?

> laurenz.a...@cybertec.at wrote: > >> b...@yugabyte.com wrote: >> >>> laurenz.a...@cybertec.at wrote: >>> >>> I recently used cursor scrollability, so I can show you a use case: >>> >>> github.com/cybertec-postgresql/db_migrator/blob/master/db_migrator--1.0.0.sql#L49 >> >> However, source

Re: Is the PL/pgSQL refcursor useful in a modern three-tier app?

> laurenz.a...@cybertec.at wrote: > > ...I understand that you ask questions to gain deeper understanding. > >> b...@yugabyte.com wrote: >> >> ...I had never come across use cases where [scrollability] was beneficial. I >> wanted, therefore, to hear about some. I thought that insights here

Re: Is the PL/pgSQL refcursor useful in a modern three-tier app?

> adrian.kla...@aklaver.com wrote: > >> b...@yugabyte.com wrote: >> >>> adrian.kla...@aklaver.com wrote: >>> >>> I have a hard time fathoming why someone who writes documentation does not >>> actually read documentation. >> >> >> Ouch. In fact, I had read the whole of the "43.7. Cursors"

Re: Schema/user/role

> david.g.johns...@gmail.com wrote: > >> adapt...@comcast.net wrote: >> >> Is there any good reference to explain the best usage of each of these >> structures. I am coming from Oracle. What is the best analog to Oracle's >> "user". > > A schema is a namespace mechanism for objects. It has

Re: Is the PL/pgSQL refcursor useful in a modern three-tier app?

> adrian.kla...@aklaver.com wrote: > > I have a hard time fathoming why someone who writes documentation does not > actually read documentation. Ouch. In fact, I had read the whole of the "43.7. Cursors" section in the "PL/pgSQL" chapter (www.postgresql.org/docs/15/plpgsql-cursors.html). And

Re: Is the PL/pgSQL refcursor useful in a modern three-tier app?

> adrian.kla...@aklaver.com wrote: > >> b...@yugabyte.com wrote: >> >>> laurenz.a...@cybertec.at wrote: >>> >>> You seem to think that a client request corresponds to a single database >>> request >> >> …I can’t picture a concrete use case where, not withstanding the "where" >> restriction

Re: Is the PL/pgSQL refcursor useful in a modern three-tier app?

> laurenz.a...@cybertec.at wrote: > >> b...@yugabyte.com wrote: >> >> Section "43.7. Cursors” in the PL/pgSQL chapter of the doc >> (www.postgresql.org/docs/current/plpgsql-cursors.html#PLPGSQL-CURSOR-DECLARATIONS) >> starts with this: >> >> « >> [...] >> A more interesting usage is to return

Re: Is the PL/pgSQL refcursor useful in a modern three-tier app?

> adrian.kla...@aklaver.com wrote: > >> b...@yugabyte.com wrote: >> >> Section "43.7. Cursors” in the PL/pgSQL chapter of the doc >> (www.postgresql.org/docs/current/plpgsql-cursors.html#PLPGSQL-CURSOR-DECLARATIONS) >> starts with this: >> « >> Rather than executing a whole query at once, it

Is the PL/pgSQL refcursor useful in a modern three-tier app?

Section "43.7. Cursors” in the PL/pgSQL chapter of the doc (www.postgresql.org/docs/current/plpgsql-cursors.html#PLPGSQL-CURSOR-DECLARATIONS) starts with this: « Rather than executing a whole query at once, it is possible to set up a cursor that encapsulates the query, and then read the query

Re: Practice advice for use of %type in declaring a subprogram's formal arguments

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> Do you (all) think that, notwithstanding this, it's a good plan to use >> "%type" for the declaration of a formal argument just for the reason that it >> documents your intention explicitly? > > If my function is likely

Practice advice for use of %type in declaring a subprogram's formal arguments

I'm thinking about "language plpgsql" subprograms—but I expect that my question can be generalized to cover "language sql" too. The payload for "create [or replace]" for a "language plpgsql" subprogram specifies various metadata elements like its qualified name, list of formal arguments and

Re: select (17, 42)::s.t2 into... fails with "invalid input syntax"

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> « >> SELECT select_expressions INTO [STRICT] target FROM …; >> >> where target can be a record variable, a row variable, or a comma-separated >> list of simple variables and record/row fields. >> » >> >> In plain English,

Re: select (17, 42)::s.t2 into... fails with "invalid input syntax"

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> r := (my_c1, my_c2)::s.t; >> >> If you write s.x there it will also work. > > Your first and third assignments are identical in syntax/nature. These are > both the first examples here[1] > > Yes, the behavior of

Re: select (17, 42)::s.t2 into... fails with "invalid input syntax"

> t...@sss.pgh.pa.us wrote: > >> b...@yugabyte.com writes: >>select (17, 42)::s.t2 into r2; >> [ doesn't work ] > > This would work as > > select 17, 42 into r2; > > In general, SELECT INTO with a composite target expects to see a source > column per target field. If you want to assign

select (17, 42)::s.t2 into... fails with "invalid input syntax"

I have a horrible feeling that I'm missing the blindingly obvious here. But I can't spot it. Help! This simple setup produces the expected result: create type s.t1 as (c1 text, c2 text); select ('cat', 'dog')::s.t1; This is the result: (cat,dog) create type s.t2 as (c1 int, c2 int); select

Re: Behavior of PL/pgSQL function following drop and re-create of a table that it uses

> t...@sss.pgh.pa.us wrote: > >> david.g.johns...@gmail.com wrote: >> >> So I found where this difference in behavior is at least explicitly noted: >> >> /* >> * If it's a named composite type (or domain over one), find the typcache >> * entry and record the current tupdesc ID, so we can detect

Re: Behavior of PL/pgSQL function following drop and re-create of a table that it uses

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >>> david.g.johns...@gmail.com wrote: >>> b...@yugabyte.com wrote: Regard a DDL on any object that an application uses as unsafe while the app is in use. You must terminate all client-sessions before

Re: Behavior of PL/pgSQL function following drop and re-create of a table that it uses

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> create table s.t(k serial primary key, v text); >> create function s.f(k_in in int) >> select s.f(1); > > text, function is now compiled with that type resolution fixed. > I think mostly attributable to: > > > The mutable

Behavior of PL/pgSQL function following drop and re-create of a table that it uses

I copied my self-contained testcase, and its output (using PG Version 15.2), at the end. I read the doc section "43.11. PL/pgSQL under the Hood": www.postgresql.org/docs/15/plpgsql-implementation.html Is my mental model,

Re: transaction_isolation vs. default_transaction_isolation

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> I found a discussion with the same title as this emails’s subject here: >> >> https://postgrespro.com/list/thread-id/1741835 >> >> It dates from 2009. But it seems to be unresolved. The current PG doc here: >> >> 20.11.

transaction_isolation vs. default_transaction_isolation

I found a discussion with the same title as this emails’s subject here: https://postgrespro.com/list/thread-id/1741835 It dates from 2009. But it seems to be unresolved. The current PG doc here: 20.11. Client Connection Defaults https://www.postgresql.org/docs/15/runtime-config-client.html has

Re: Thanks! Re: Who adds the "start transaction" and "commit" to the intended SQL statement in "autocommit" mode?

>> b...@yugabyte.com wrote: >> >> I’ve no idea how I might have found this without human help. > > x...@thebuild.com wrote: > > That sounds like an excellent documentation patch! Well, it’s already documented clearly enough. The question is how to find it—especially if you don’t know that the

Re: Thanks! Re: Who adds the "start transaction" and "commit" to the intended SQL statement in "autocommit" mode?

>> b...@yugabyte.com wrote: >> >> It seems a bit odd that psql has no syntax to ask for this in its >> interactive mode. > > dan...@manitou-mail.org wrote: > > Backslash-semicolon is the syntax. Thanks, Daniel. Yes, that works. And the server’s SQL statement log confirms this. I’ve no idea

Re: Thanks! Re: Who adds the "start transaction" and "commit" to the intended SQL statement in "autocommit" mode?

> x...@thebuild.com wrote: > >> b...@yugabyte.com wrote: >> >> 2. If I send over "begin" and then "insert into s.t(v) values(42)", then (so >> far) a second session will not see the effect of my SQL's. It sees this only >> when I send over "commit". (If I send over "rollback" instead of

Thanks! Re: Who adds the "start transaction" and "commit" to the intended SQL statement in "autocommit" mode?

> b...@yugabyte.com wrote: > > ...it's not clear who actually implements the opening "start transaction" > and the closing "commit" around every submitted SQL statement when > autocommit is "on". Is this done in client-side code (maybe implying > three round trips per

Who adds the "start transaction" and "commit" to the intended SQL statement in "autocommit" mode?

I’ve searched in vain for an account of how "autocommit" mode actually works. (I tried the built-in search feature within the PG docs. And I tried Google.) It seems clear enough that turning "autocommit" mode "on" or "off" is done by using a client-env-specific command like "\set" is psql, or

Re: Order of rows in simple "select r from table_fn()"

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> I've found that a table function with "returns table(r text)" provides a >> convenient way to write a nicely formatted report using psql that can be >> easily directed to a file with the "\o" metacommand. In general, for

Order of rows in simple "select r from table_fn()"

In general, the order of the rows in a result set is stated to be unpredictable without an "order by" at the outermost level. Famously, beginners observe what seems to be reliably reproducible ordering in some queries that don't have an "order by"—and it can take some effort to persuade them

Re: Intervals and ISO 8601 duration

> ken.tan...@gmail.com wrote: > > Here's an example. Note that they come out formatted differently with > to_char, but evaluate as equal. The explanation(1) was that they were Equal > but not Identical. I was thinking getting the raw data about how they are > stored would get at the

Re: What is the best setup for distributed and fault-tolerant PG database?

>>> age.apa...@gmail.com wrote: >>> >>> I am new to postgres, and I am also not a DBA. I am a solo developer who is >>> trying to evaluate what database to use for my hybrid multi-tenancy >>> sub-apps i.e. users of the application will be authorised to use part or >>> whole of the application

Re: Test if a database has any privilege granted to public

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> select datname::text >> from pg_database >> where 0::oid = any(select (aclexplode(datacl)).grantee) >> or datacl is null; >> >> That's easy if you know that you need to write this. But the need to do so >> seems to depend

Re: Test if a database has any privilege granted to public

> t...@sss.pgh.pa.us wrote: > >> ronljohnso...@gmail.com writes: >> >> Off-topic, but you don't need all those text casts. > > Indeed. Something like this ought to do it: > > select datname from pg_database where 0::oid = any(select > (aclexplode(datacl)).grantee); > > datname >

Test if a database has any privilege granted to public

I want to adopt a rule that no database in my cluster has any privilege granted to public. It suits me best to encapsulate the test as a boolean function thus: function mgr.db_has_priv_granted_to_public(db in name) where "mgr" is a convenient schema for various admin utilities. I have

Re: Seeking practice recommendation: is there ever a use case to have two or more superusers?

> adrian.kla...@aklaver.com wrote: > >> b...@yugabyte.com wrote: >> >> Consider this wording. It also uses “good practice”. >> >> « >> It is good practice to limit the number of superuser roles that exist in a >> cluster to exactly one: the inevitable bootstrap superuser. This recognizes >>

Re: Seeking practice recommendation: is there ever a use case to have two or more superusers?

> adrian.kla...@aklaver.com wrote: > >> b...@yugabyte.com: >> >> Consider this wording. It also uses “good practice”. >> « >> It is good practice to limit the number of superuser roles that exist in a >> cluster to exactly one: the inevitable bootstrap superuser. This recognizes >> the fact

Re: Seeking practice recommendation: is there ever a use case to have two or more superusers?

> adrian.kla...@aklaver.com wrote: > >> karsten.hilb...@gmx.net: >> >>> adrian.kla...@aklaver.com wrote: >>> b...@yugabyte.com Thanks to all who offered their views on my question. It seems that different people will reach different conclusions. I’ll take this as

Re: Seeking practice recommendation: is there ever a use case to have two or more superusers?

> hjp-pg...@hjp.at wrote: > >> ronljohnso...@gmail.com wrote: >> >> [developers or devops folks] like to "fix" things without documenting what >> they did, and then, when >> something breaks, denying they did anything (or honestly not believing that >> whatever "trivial" thing they did could

Re: Seeking practice recommendation: is there ever a use case to have two or more superusers?

> hjp-pg...@hjp.at wrote: > > b...@yugabyte.com wrote: > >> The detail below leads to a simply stated question: >> >> Given that the bootstrap superuser must exist, is there ever a reason to >> create >> another role with "superuser"? >> >> My intuition tells me that the answer is a

Seeking practice recommendation: is there ever a use case to have two or more superusers?

The detail below leads to a simply stated question: Given that the bootstrap superuser must exist, is there ever a reason to create another role with "superuser"? My intuition tells me that the answer is a resounding "No!". — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — *Detail*

Re: "set role" semantics

> adrian.kla...@aklaver.com wrote: > >> b...@yugabyte.com wrote: >> >> Anyway, all this is moot (except in that thinking about it helps me to >> enrich my mental model) because the privilege notions here will never change. > > So, I want it but not really. I’d rather say “I’d very much prefer

Re: "set role" semantics

adrian.kla...@aklaver.com wrote: > >> b...@yugabyte.com wrote: > > Connecting to database and the role that is in play inside a session are two > different things. Making them the same would make things [security define vs > "security invoker"] go sideways. I said nothing to suggest that the

Re: "set role" semantics

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> Is there anything that can be done to limit the scope of the ability to end >> up in a database like I'd thought would be possible? (A little test showed >> me that "set role" doesn't fire an event trigger.) >> >> I do

Re: "set role" semantics

the roles, "clstr$mgr, "d1$mgr", or "d2$mgr". >> >> You didn't have to since PUBLIC gets that privilege and you didn't revoke it. >> >> https://www.postgresql.org/docs/current/ddl-priv.html > > Revoking PUBLIC has been explained

"set role" semantics

I created a little test to demonstrate to myself how “set role” works. I ran it in a freshly-created PG 11.17 cluster on Ubuntu, installed and configured like I’ve recently discussed on this list. I copied my "pg-init.sh" script at the end. I then did this test, after starting like this (as

Re: Putting the O/S user for "local" "peer" authentication in the "postgres" group vs chmod'ing the "pg*.conf" files to be readable by "all"

> adrian.kla...@aklaver.com wrote: > >> b...@yugabyte.com wrote: >> >> So only "postgres" can edit the files that must be so edited. > > That is not true [sudo vi some-file] which opens [it for editing]. By all means. I didn't bother to spell that out; > It isn't you where using pg_ctl

Re: Putting the O/S user for "local" "peer" authentication in the "postgres" group vs chmod'ing the "pg*.conf" files to be readable by "all"

> david.g.johns...@gmail.com wrote: > > Some repetition of what Adrian just posted ahead... > >> b...@yugabyte.com wrote: >> >> How can it be that the PG doc itself leads you by the hand to a regime where >> you need to use undocumented features? > > The documentation tries to make clear that

Re: Putting the O/S user for "local" "peer" authentication in the "postgres" group vs chmod'ing the "pg*.conf" files to be readable by "all"

> adrian.kla...@aklaver.com wrote: > >> b...@yugabyte.com wrote: >> >> Adrian gave me this link: >> https://ubuntu.com/server/docs/databases-postgresql >> Of course I'd read that right at the outset. The subtext is loud and clear. >> You need to do some things as the "postgres" user and some

Re: Putting the O/S user for "local" "peer" authentication in the "postgres" group vs chmod'ing the "pg*.conf" files to be readable by "all"

> david.g.johns...@gmail.com EARLIER wrote: > > The postgres o/s user should be able to login using peer. It is a one-way > idea though. Wanting to login using peer says nothing about whether the user > getting that capability should be allowed to mess with the running server in > the

Re: Putting the O/S user for "local" "peer" authentication in the "postgres" group vs chmod'ing the "pg*.conf" files to be readable by "all"

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> It would seem proper to put any user who you want to set up for "local", >> "peer" authentication into the "postgres" group > > Did you really mean to write that? > > The postgres o/s user should be able to login using

Putting the O/S user for "local" "peer" authentication in the "postgres" group vs chmod'ing the "pg*.conf" files to be readable by "all"

I followed Peter's recommendation NOT to put my "clstr_mgr" O/S user in the "postgres" group—having earlier had it there. But doing so brought this content-free error message on an attempt to authorize using the intended method: Error: Invalid data directory for cluster 11 main A bit of

Re: CASE CLOSED... Re: "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> This is what I see. I have Ubuntu 20.04 LTS VM using Parallels Desktop >> Version 18. >> >> # adduser 'dog$house' >> adduser: To avoid problems, the username should consist only of >> letters, digits, underscores, periods,

Re: CASE CLOSED... Re: "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should

hjp-pg...@hjp.at wrote: > >> b...@yugabyte.com wrote: >> >> However, Linux (at least) simply disallows O/S users that have a dollar sign >> in the name. > > This is getting quite off-topic, but that isn't true: > > trintignant:~ 22:46 :-) 1015# useradd -m -s /bin/bash 'mac$crooge' >

Re: CASE CLOSED... Re: "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should

> adrian.kla...@aklaver.com wrote: > >> b...@yugabyte.com wrote: >> >>> adrian.kla...@aklaver.com wrote: >>> b...@yugabyte.com wrote: This, on the other hand: psql -d postgres -U 'clstr$mgr' calls for "local", "peer"

Re: CASE CLOSED... Re: "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should

> adrian.kla...@aklaver.com wrote: > >> b...@yugabyte.com wrote: >> >> This, on the other hand: >> >> psql -d postgres -U 'clstr$mgr' >> >> calls for "local", "peer" authentication as so it does NOT require a >> password. That would be enough for me. But, naturally, and now that it's >>

CASE CLOSED... Re: "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should

INTRODUCTION Thanks to all who've helped me on this topic. Forgive me if I left out anybody on the "To" list. I suppose that I should have explained my use case more carefully. I did sketch it earlier on. But, not surprisingly, this got lost in the noise. I was afraid of being accused of

Re: Unix users and groups... Was: "peer" authentication...

> hjp-pg...@hjp.at wrote: > >> b...@yugabyte.com: >> >> For the purpose of the tests that follow, I set up the O/S users "bob" and >> "mary" so that "id bob mary postgres" shows this: >> >> id=1002(bob) gid=1001(postgres) groups=1001(postgres) >> uid=1003(mary) gid=1001(postgres)

Re: "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should

> adrian.kla...@aklaver.com wrote: > >> b...@yugabyte.com wrote: >> >> ...What am I doing wrong? > > You skipped over this part of my post and the documentation (Section 21.2): >

"peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should

Adrian, thank you for your reply to my « Seeking the correct term of art for the (unique) role that is usually called "postgres"... » thread here: https://www.postgresql.org/message-id/e75abfa8-72af-701c-cf6f-5336a1a35...@aklaver.com

Re: Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all

> t...@sss.pgh.pa.us> wrote: > >> david.g.johns...@gmail.com wrote: >> >> Yes, the description for --username probably should be modified to read: >> >> "Selects the user name of the cluster's bootstrap superuser." > > Yeah, perhaps. The term "bootstrap superuser" is reasonably well

Re: Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all

> david.g.johns...@gmail.com> wrote: > >> b...@yugabyte.com wrote: >> >> This invariant must hold if an "ordinary" within-cluster superuser is to >> qualify as the cluster's "bootstrap superuser": >> >> the name of the bootstrap superuser's within-cluster role >> >> AND >> >> the name of

Re: Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> « >> You can start a session without specifying the name of the cluster role as >> which to authorize, its password, and the name of the database to which to >> connect, ONLY when these things are true: >> >> 1. The

Re: Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> The fact that the "bootstrap superuser" term of art denotes a matching pair >> of two principals (an O/S user and a within-cluster role) > > No, it does not. It denotes only the PostgreSQL role. "service user" is >

Re: Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all

> jer...@musicsmith.net wrote: > >> b...@yugabyte.com wrote: >> >> I can now characterize what I'd observed more clearly, thus: only a >> bootstrap super user (as defined above) can start a session without >> mentioning the name of the database to which to connect

Re: Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all

> adrian.klaver@aklaver.comwrote: > >> b...@yugabyte.com wrote >> >> The descriptive designation "the role that owns the SQL part of the >> implementation of PostgreSQL" is too much of a mouthful for daily use. And >> anyway, this notion captures only part of the story that makes "postgres"

Re: Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all

(David and Ian, I'm resending this because, I see that I managed to omit "pgsql-general@lists.postgresql.org " from the addressee list. So, of course, it didn't show up in the "pgsql-general" archive.) > barw...@gmail.com

Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all

The descriptive designation "the role that owns the SQL part of the implementation of PostgreSQL" is too much of a mouthful for daily use. And anyway, this notion captures only part of the story that makes "postgres" uniquely what it is—at least on Ubuntu. MORE... Here's what my empirical

Re: COMMIT IN STORED PROCEDURE WHILE IN A LOOP

It seems that I made a thread-discipline error when I asked a question that had nothing to do with the frequency, or the cost, of committing when I saw this reply (paraphrased for brevity here) from Christophe: > You [cannot] commit in [a] BEGIN / END [block statement] that has an > exception

Re: COMMIT IN STORED PROCEDURE WHILE IN A LOOP

> x...@thebuild.com wrote: > >> s_ravikris...@aol.com wrote: >> >> I am getting error at COMMIT -> cannot commit while a subtransaction is >> active... > > You can commit in a loop, but not in BEGIN / END block that has an exception > handler: that creates a subtransaction for the duration of

Re: ('dog$house' = quote_ident('dog$house')) is surprisingly FALSE

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> (3) The PG doc on quote_ident says this in large friendly letters: >> >>> Quotes are added only if necessary… >> >> Notice "only". I now know that this is very much not the case. You can >> compose an effectively

Re: ('dog$house' = quote_ident('dog$house')) is surprisingly FALSE

> list.pg.ga...@pendari.org wrote: > >> b...@yugabyte.com wrote: >> >> Does this imply a risk that a future PG version will go against the SQL >> standard and reject any non-latin name that is free of all punctuation >> characters, when used in the role of a SQL identifier, unless it's double

Re: ('dog$house' = quote_ident('dog$house')) is surprisingly FALSE

> karsten.hilb...@gmx.net wrote: > >> b...@yugabyte.com wrote: >> >> What we deal with in our ordinary professional work is SQL texts, program >> source texts, within these, SQL identifier texts, and then the conventional >> display of the results of SQL and program execution. To emphasize the

Re: ('dog$house' = quote_ident('dog$house')) is surprisingly FALSE

> x...@thebuild.com wrote: > > There is no first-class "identifier" type in PostgreSQL, so a function can't > "return an identifier." It returns a string which might, when placed into a > larger string and processed as SQL, be lexically correct as an identifier. It takes huge discipline

Re: ('dog$house' = quote_ident('dog$house')) is surprisingly FALSE

> adrian.kla...@aklaver.com wrote: > > The way I see is if it where an actual identifier then this: > > select * from quote_ident('$dog'); > > quote_ident > - > "$dog" > > would be equal to this: > > select * from "$dog"; I think that the clue here is to go into philosophical

Re: ('dog$house' = quote_ident('dog$house')) is surprisingly FALSE

> t...@sss.pgh.pa.us wrote: > >> david.g.johns...@gmail.com writes: >> >> So I can see an argument for the existing behavior. It doesn't seem worth >> changing in any case. And I don't really see the documentation being >> improved by covering this corner case in detail when the current

('dog$house' = quote_ident('dog$house')) is surprisingly FALSE

The doc for "quote_ident()" says this: « https://www.postgresql.org/docs/14/functions-string.html Returns the given string suitably quoted to be used as an identifier in an SQL statement string. Quotes are added only if necessary (i.e., if the string contains non-identifier characters or would

Re: Names of run-time configuration parameters (was: Limiting the operations that client-side code can perform upon its database backend's artifacts)

> t...@sss.pgh.pa.us wrote: > >> b...@yugabyte.com writes: >> >> get stacked diagnostics msg = message_text; >> if msg != 'relation "pg_temp.flag" does not exist' then > > This is pretty fragile --- eg, it will fall over with translated messages. I > think you could presume that if the error

Re: Names of run-time configuration parameters (was: Limiting the operations that client-side code can perform upon its database backend's artifacts)

> t...@sss.pgh.pa.us wrote: > > Up to now, there's been an intentional policy of not documenting > > « > 20.16. Customized Options > https://www.postgresql.org/docs/14/runtime-config-custom.html > > » > > very prominently[*],

Re: Limiting the operations that client-side code can perform upon its database backend's artifacts

> hjp-pg...@hjp.at wrote: > >> b...@yugabyte.com wrote: >> >> Paraphrasing Peter, the design of the application's RDBMS backend has to >> implement its own notions of roles and privileges as a new layer on top of >> whatever the native RDBMS mechanisms provide. Some RDBMSs have native >>

Re: Limiting the operations that client-side code can perform upon its database backend's artifacts

> t...@sss.pgh.pa.us wrote: > >> b...@yugabyte.com writes: >> >>> rjuju...@gmail.com wrote: >>> >>> I'm not convinced... that the authorization system can prevent an untrusted >>> user with a direct SQL access from actually hurting you. >> >> What do you mean by "untrusted"? Any person who is

Re: Limiting the operations that client-side code can perform upon its database backend's artifacts

> rjuju...@gmail.com wrote: > > b...@yugabyte.com wrote: >> >> Now back to my new thread. I interpreted what Tom wrote to mean that he >> flatly rejected the idea that a database design was possible that prevented >> a client session that authorized as a role, that's designed for that >>

Re: Limiting the operations that client-side code can perform upon its database backend's artifacts

> rjuju...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> hjp-pg...@hjp.at wrote: > rjuju...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> [Bryn] My demo seems to show that when a program connects as "client", >> it can perform exactly and only the

Re: Limiting the operations that client-side code can perform upon its database backend's artifacts

> hjp-pg...@hjp.at wrote: > >> rjuju...@gmail.com wrote: >> >>> b...@yugabyte.com wrote: >>> >>> My demo seems to show that when a program connects as "client", it can >>> perform exactly and only the database operations that the database design >>> specified. Am I missing something? In other

Re: Limiting the operations that client-side code can perform upon its database backend's artifacts

> rjuju...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> My demo seems to show that when a program connects as "client", it can >> perform exactly and only the database operations that the database design >> specified. Am I missing something? In other words, can anybody show me a >>

  1   2   3   4   >