Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario

david.g.johns...@gmail.com wrote:b...@yugabyte.com wrote:What do you all think?That bug reports should be sent to the -bugs mailing list with a succinct test case demonstrating the bug.I explained that the bug doesn't allow a short testcase because there are many degrees of freedom and you don't kn

Two questions about "pg_constraint"

*Question 1: why does "pg_constraint" have a "connamespace" column?* I created this temporary view (using PG 14.4): create temporary view all_constraints(t_owner, t_schema, t_name, c_name, same) as select r.rolname, s.nspname, c.relname, x.conname, (x.connamespace = c.relnamespace) fro

Re: Two questions about "pg_constraint"

> adrian.kla...@aklaver.com wrote: > >> b...@yugabyte.com wrote: >> >> Question 2: what happened to the column "consrc"? > > It was in 11 but not later, so let's look at release notes... HERE: https://www.postgresql.org/docs/12/release-12.html > « > Remove obsolete pg_constraint.consrc column

Greg Sabino Mullane ? Re: Two questions about "pg_constraint"

> adrian.kla...@aklaver.com wrote: > >> b...@yugabyte.com wrote: >> >> Thanks, Adrian. I should have tried Google for "remove pg_constraint >> consrc". This finds the quote as the top hit. > > To see all release notes together go here: > > https://bucardo.org/postgres_all_versions > > Then yo

Re: Two questions about "pg_constraint"

> t...@sss.pgh.pa.us wrote: > >> b...@yugabyte.com writes: >> >> Question 1: why does "pg_constraint" have a "connamespace" column? > > You appear to be assuming that every pg_constraint entry is tied to a table. > This isn't so. I see how this might have appeared to be the case. But I did, ve

Re: Two questions about "pg_constraint"

> adrian.kla...@aklaver.com wrote: > >> bryn@yugabyte.comwrote: >> >> Question 1: why does "pg_constraint" have a "connamespace" column? > > create table c1 (id integer, constraint pk1 primary key(id)); > > create table c2 (id integer, constraint pk1 primary key(id)); > ERROR: relation "pk1" a

Re: Two questions about "pg_constraint"

> x...@thebuild.com wrote: > >> b...@yugabyte.com wrote: >> [...] > > I've read this a few times, and I am having trouble understanding what > behavior you were expecting out of PostgreSQL, and what behavior you received > that you didn't think was correct. If it is "pg_constraint has a column

Re: Two questions about "pg_constraint"

> x...@thebuild.com wrote: > >> b...@yugabyte.com wrote: >> [...] > > I'm still not clear on what you are proposing. Are you proposing a change to > PostgreSQL to remove the "connamespace" column from the "pg_constraint" > table, since it can be derived from other tables? No, I’m not proposing

Re: Two questions about "pg_constraint"

That's the answer I was seeking. So it's case closed for both of my « Two questions about "pg_constraint" ». Thanks, Tom. And thanks to the others who've contributed to this thread. t...@sss.pgh.pa.us wrote: > b...@yugabyte.com writes: > > My other question was about the "connamespace" column.

Is it possible to stop sessions killing eachother when they all authorize as the same role?

I'll use "kill" here a shorthand for using the "pg_terminate_backend()" built-in function. I read about it in the "Server Signaling Functions" section of the enclosing "System Administration Functions" section of the current doc: www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADM

Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> …I'm troubled by the notion that (as it seems) one session that authorizes >> as the role "r1" can easily list all other concurrent sessions that are also >> authorized as "r1"—and kill them all without restriction. (The do

Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> revoke execute on function pg_terminate_backend(int, bigint) from public; > > I just did this very thing in v16 (head-ish) and it worked as expected, > preventing the non-superuser role from executing the function: > > Ses

Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?

x...@thebuild.com wrote: > > b...@yugabyte.com wrote: >> >> The implication is that every client program must follow every database call >> with defensive code to detect error "57P01" and programmatically re-try. > > That situation exists even without the ability for a role to kill other > ses

Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?

> t...@sss.pgh.pa.us wrote: > >> b...@yugabyte.com wrote: >> >> My non-superuser normalrole with direct login, "u1", is *still* able to >> invoke pg_terminate_backend() and kill other "u1" sessions—even after this >> (as a super-user): > > Really? I did this in 14.5: > > revoke execute on fu

Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?

> x...@thebuild.com wrote: > >> b...@yugabyte.com wrote: >> >> There must be some-or-other non-standard setting in my environment that >> results in the behavior that I see and that other's don't. > > From the documentation: > >> superuser status: A database superuser bypasses all permission c

Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?

> x...@thebuild.com wrote: > >> b...@yugabyte.com wrote: >> >> What are you seeing that I'm failing to? > > It works correctly for me, on MacOS: > > create user r1; > revoke execute on function pg_terminate_backend from r1; > > (reconnect as r1) > > select pg_terminate_backend(123); > > ERRO

Re: Is it possible to stop sessions killing eachother when they all authorize as the same role? >> CASE CLOSED

x...@thebuild.com wrote: > >> t...@sss.pgh.pa.us wrote: >> >> Perhaps you'd already revoked from public in this database? > > Very possible! You all forgot to tell me to put this aside and go out for a walk. I just told myself to do that. And it struck me then. Tom just said it here—albeit pa

Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?

> jer...@musicsmith.net wrote: > >> b...@yugabyte.com wrote: >> >> I say this only to emphasize that there are always things that are critical >> that are elided in a testcase that tries to be minimal. >> >> So it seems that there's something critical about my env that I'm failing to >> tell y

Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?

> guilla...@lelarge.info wrote: > > This won't answer your question It has been answered now. See my "case closed" email here: www.postgresql.org/message-id/B33C40D9-2B79-44C7-B527-86E672BEA71A%40yugabyte.com > …but still… I usually really like your scripts, it's nicely written, but this > par

Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?

> gogala.mla...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> I'll use "kill" here a shorthand for using the "pg_terminate_backend()" >> built-in function. I read about it in the "Server Signaling Functions" >> section of the enclosing "System Administration Functions" section of the >>

Where's the doc for "array()" — as in "select array(values (17), (42))"

I noticed that "array()" is used in the query that implements the "\du" psql meta-command. It has some similarity with "array_agg()" thus: create temporary view x(v) as (values (17), (42)); select array_agg(v) from x; select array(select v from x); But there are differences. The "array()" functi

Re: Where's the doc for "array()" — as in "select array(values (17), (42))"

> adrian.kla...@aklaver.com wrote: > >> b...@yugabyte.com wrote: >> >> I noticed that "array()" is used in the query that implements the "\du" psql >> meta-command. It has some similarity with "array_agg()" thus: >> >> create temporary view x(v) as (values (17), (42)); >> select array_agg(v) fr

Re: Where's the doc for "array()" — as in "select array(values (17), (42))"

> adrian.kla...@aklaver.com wrote: > > b...@yugabyte.com wrote: > >> Thanks. I can't, even now, formulate a search that finds this—unless I know >> that it's a kind of array constructor. (I'll concede, with the benefit of >> hindsight, that I should have thought of that.) I hope that I'll be ab

Re: Where's the doc for "array()" — as in "select array(values (17), (42))"

> t...@sss.pgh.pa.us wrote: > >> b...@yugabyte.com wrote: >> >> Why is the "array()" constructor not found in "pg_proc"? > > Because it isn't a function. Yeah, it kind of looks like one, but its > argument is a subquery. If SQL had first-class functions and closures, maybe > ARRAY() could be i

I slipped up so that no existing role allows connection. Is rescue possible?

*Summary* Is rescue possible? Or must I simply remove my cluster and use "pg_ctl" to create a new one within my existing PG 14.5 software env? *Detail* This is a sandbox PostgreSQL 14.5 cluster on my MacBook and it contains nothing of value. I was doing some empirical destructive tests with a

Re: I slipped up so that no existing role allows connection. Is rescue possible?

> t...@sss.pgh.pa.us wrote: > >> b...@yugabyte.com writes: >> >> Is rescue possible? Or must I simply remove my cluster and use "pg_ctl" to >> create a new one within my existing PG 14.5 software env? > > Stop the cluster, start a single-user session ("postgres --single"), re-grant > superuser

Re: I slipped up so that no existing role allows connection. Is rescue possible?

> adrian.kla...@aklaver.com wrote: > >> b...@yugabyte.com wrote: >> >>> t...@sss.pgh.pa.us wrote: >>> >>> ... >> >> So nice to find this waiting for me when I got back to the keyboard after a >> late lunch. Thanks, Tom. And thanks to David, too who said much the same. >> I'll note how I spell

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

> On 14-Sep-2022, t...@sss.pgh.pa.us wrote: > > …. Therefore, if you don't trust another session that is running as your > userID, you have already lost. That session can drop your tables, or corrupt > the data in those tables to an arbitrary extent, and the SQL permissions > system will not sq

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 >> vul

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: >> 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

> 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 >> purpo

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

> 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 >> primi

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[*], becau

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

('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 b

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 behavio

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 over

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 always

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

> 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

> 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 unlimited

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: 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 h

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 obs

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 wro

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

> 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 and

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 > proba

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 within

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 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

> 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 established

"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: "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): > https://www.postgresql.org/docs/current/auth-pg-hba-con

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) grou

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 writi

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 >> worki

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" authen

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' > trintignan

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,

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 Googlin

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 pee

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 operating

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 thi

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: >> >> 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 an

"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 the

Re: "set role" semantics

y of 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

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 see

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 r

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

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: 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 resounding

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 any

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 perm

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 tha

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 >> th

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 implement

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 >

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 o

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 b

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 identicali

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 tha

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 ca

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 "S

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 in

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 "commit

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?

>> 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

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: 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. Cl

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, describe

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 n

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 doing

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

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: 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 a

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 INTO

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,

<    1   2   3   4   >