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

[Bryn Llewellyn]

I’m about to hang up my working boots. My last day of employment at Yugabyte, 
Inc (or anywhere at all) is imminent. My bryn-at-yugabyte-dot-com email address 
will stop working at 17:00 US/Pacific, Friday 18-Aug-2023. I’ll unsubscribe  
from this and all other postgresql-dot-org lists a few hours before that 
cut-off time.

Thanks very much to everybody who’s answered my questions. I know that I’ve 
annoyed many of you. But, from my side, the exchanges have been enormously 
helpful. It would have been so nice to have been able to ask my questions 
face-to-face so the whomever I talked to could see at a glance why I just 
didn’t get something that they said and thought was obvious. But those days are 
long gone now. The Brave New World has totally and irrevocably replaced that 
old regime—albeit somewhat later than Huxley forecast way back in 1931.

Who knows… I might re-subscribe presently using my private email address.

Regards, Bryn Llewellyn

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

[Bryn Llewellyn]

> 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 why PL/pgSQL has not "goto" statement is mainly technological. 
> PL/pgSQL is an interpreter of high level commands.  For this kind of 
> interpreter the "goto" - unstructured jump cannot be effectively implemented. 
> PL/pgSQL is very simple, and relatively fast (expressions are slow due 
> evaluation by SQL executor), but "goto" cannot be implemented there. 
> Interpreter of PL/pgSQL is very different from the more usual p-code 
> interpreter.

It’s interesting to know that the reason that PL/pgSQL doesn’t support “goto” 
is an implementation restriction rather than a purist stance. Thanks!

I mentioned PL/SQL only to say that it does not support the premature exit from 
a block statement that PL/pgSQL _does_ support (and document). I accept, now, 
that I’ll never know the rationale for this.

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

[Bryn Llewellyn]

> 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 endlessly whether it's good 
> or bad to include that functionality, but it definitely has use cases.  I 
> would assume that at this point, it's not going anywhere. The relevant 
> documentation is here:
> 
> https://www.postgresql.org/docs/15/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS

Sorry, I should’ve said that I’d read this:

« When used with a BEGIN block, EXIT passes control to the next statement after 
the end of the block. Note that a label must be used for this purpose; an 
unlabeled EXIT is never considered to match a BEGIN block. (This is a change 
from pre-8.4 releases of PostgreSQL, which would allow an unlabeled EXIT to 
match a BEGIN block.) »

Yes, I see that the doc implies that the functionality that I asked about 
(premature exit from a block statement) will be supported indefinitely.

I can only assume that somebody implemented this back in the day and that 
nobody thought to say that it was a bad idea.

FYI, I tied both “exit” and “continue” outside of a loop in PL/SQL (using a 
fairly recent version of Oracle Database). There, each of these causes "must 
appear inside a loop" or "label 'B1' must label a LOOP statement".

Using "exit" to bring "goto" functionality.

[Bryn Llewellyn]

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 plpgsql
as $body$
begin
  exit;
end;
$body$;

It causes the 42601 syntax error: EXIT cannot be used outside a loop, unless it 
has a label

"unless it has a label"? Eh?

I Googled this for that message and found this:

https://postgrespro.com/list/thread-id/2364409 

“Thread: Better testing coverage and unified coding for plpgsql loops”, Tom 
Lane, 31 December 2017

The message is mentioned. But I couldn't find any opinion about the intention. 
I tried this (using Version 15.3):

create function f(n in int)
  returns text
  language plpgsql
as $body$
declare
  v text not null := 'a';
begin
  <>begin
v := v||'1';
exit b1 when length(v) > n;
v := v||'2';
exit b1 when length(v) > n;
v := v||'3';
  end b1;
  return v;
end;
$body$;

It completed without error. I tried "select f(1)" and then with the actuals "2" 
and "99"—and I got the outcomes that the code asks for. In other words, "exit" 
can, in at least some scenarios, be used to emulate "goto". (Rather like a 
premature "return" in the middle of a procedure.)

What is the rationale for supporting what seems to be on its face this strange 
functionality?

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:

https://www.postgresql.org/docs/current/plpgsql-porting.html 

"Porting from Oracle PL/SQL"

doesn't mention "goto". But "exit " might help.

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

[Bryn Llewellyn]

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 with CREATEROLE can make herself a member of 
> "pg_execute_server_program", which in turn allows a clever attacker on a 
> normal installation to make herself superuser.

Yes, that's how the thread that Robert Haas started here begins.

https://www.postgresql.org/message-id/CA%2BTgmobGds7oefDjZUY%2Bk_J7p1sS%3DpTq3sZ060qdb%3DoKei1Dkw%40mail.gmail.com

It seems odd that this realization comes so late. And it seems odd to respond 
by removing the tip in question rather than by adding to it to explain that 
risk.

There's already a precedent for causing an error if a role with "createdb" 
attempts to grant itself a role with "super". A naïve observer like me would 
think that it would be possible to add other similar checks to cause an error 
in these other troublesome cases so that the now-removed tip could really have 
the value that whoever wrote it thought it already had.

(I'm assuming that the hackers must grant themselves special permission to 
change existing behavior to fix critical security bugs.)

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

[Bryn Llewellyn]

> 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
> 
> doc/src/sgml/ref/alter_role.sgml  
> doc/src/sgml/ref/create_role.sgml 
> doc/src/sgml/ref/createuser.sgml  
> doc/src/sgml/user-manag.sgml
> 
> so I don't see how it can change behavior.

The account of the commit that Jeremy Smith referred to, here:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=1c77873727dfd2e48ab2ece84d1fb1676e95f9a5

had a reference to an email thread on the pgsql-hackers with subject "fixing 
CREATEROLE". It was started by Robert Haas and it begins thus:

> https://www.postgresql.org/message-id/CA%2BTgmobGds7oefDjZUY%2Bk_J7p1sS%3DpTq3sZ060qdb%3DoKei1Dkw%40mail.gmail.com
> 
> The CREATEROLE permission is in a very bad spot right now. The biggest 
> problem that I know about is that it allows you to trivially access the OS 
> user account under which PostgreSQL is running, which is expected behavior 
> for a superuser but simply wrong behavior for any other user. This is because 
> CREATEROLE conveys powerful capabilities not only to create roles but also to 
> manipulate them in various ways, including granting any non-superuser role in 
> the system to any new or existing user, including themselves.


The thread goes on forever. And it branches too. It's talking about possibly 
patching the code—precisely to bring about a change in behavior. And I'm asking 
if the fix(es) under discussion would change the behavior of the code that I 
showed.

The upshot of it all seems to be that the putative benefit of using a role the 
has only "createrole" and not "super" is marginal because such a role can grant 
itself shipped dangerous roles like "pg_execute_server_program" and 
"pg_write_server_files"
which are trivially exploitable.

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

[Bryn Llewellyn]

> 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 avoids the dangers of 
>> operating as a superuser for tasks that do not really require it.
>> » 
>>  
>> used to be found in all versions of the PG doc... What was the rationale for 
>> removing it? The practice recommendation makes sense to me. And I've 
>> implemented a scheme for database and role provisioning that uses just such 
>> a non-superuser with CREATEDB and CREATEROLE. I'm pleased with it.
> 
> It was removed in this commit:
> 
> git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=1c77873727dfd2e48ab2ece84d1fb1676e95f9a5
> 
> According to the commit comment, there's little security advantage to using a 
> role with CREATEDB and CREATEROLE privileges.  

I looked at some of the discussion here:

postgr.es/m/ca+tgmozbspl8nphvyecx7igo5qpdrqa9k_acaw1sbojugay...@mail.gmail.com

It's rather dense and I'll have to defer studying it. Anyway, I noticed an 
encouragement there to stop discussing it.

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

However, a role with just "createdb" and "createrole" is definitely less 
powerful than one that has "superuser". For example, a role without "superuser" 
cannot drop an object that's owned by a role that has "superuser". Nor can a 
role without "superuser" execute, for example, "alter database... set 
log_error_verbosity...". And especially any cunning scheme that somebody might 
hatch to authorize as a role with "createdb" and "createrole" and without 
"superuser" to end up where the current_role has "superuser" fails—just like 
the doc says.

The principle of least privilege is generally regarded as a good thing. And I 
like to follow it. I'm able to do the database provisioning and role 
provisioning tasks that I need to with a role that has just "createdb" and 
"createrole"—like the now-removed tip recommended. And it would be 
tautologically not least privilege to use a role with "superuser" instead—and 
therefore a bad thing.

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.

\c postgres postgres
\c postgres postgres
create role supr with superuser login password 'p';

\c postgres supr
create role joe with createdb createrole login password 'p';
create role mary with createdb createrole login password 'p';

\c postgres joe
grant postgres to joe; -- error 42501

grant mary to joe; --OK
set role mary; -- OK
select session_user, current_role;

\c postgres joe
create database d0;
alter database d0 set log_error_verbosity = terse; -- error 42501

\c postgres postgres
alter database d0 set log_error_verbosity = terse; -- OK

create schema s;
create table s.t(k int primary key);

\c postgres joe
drop table s.t; -- error 42501

\c postgres supr
drop table s.t; -- OK

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

[Bryn Llewellyn]

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 it.
»

used to be found in all versions of the PG doc from (at least) Version 11 
through Version 15. It was in the chapter "Role Attributes" (22.2 in "current", 
21.2 in Version 11). It immediately followed this: «A role's attributes can be 
modified after creation with ALTER ROLE. See the reference pages for the CREATE 
ROLE and ALTER ROLE commands for details.»

You can still find it in "privately" staged copies of the PG doc like, for 
example, here:

postgrespro.com/docs/postgresql/10/role-attributes
access.crunchydata.com/documentation/postgresql15/15.1/role-attributes.html

But now it's simply vanished from both the Version 11 doc and the "current" 
doc. (I didn't check the versions in between.)

What was the rationale for removing it? The practice recommendation makes sense 
to me. And I've implemented a scheme for database and role provisioning that 
uses just such a non-superuser with CREATEDB and CREATEROLE. I'm pleased with 
it.

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

[Bryn Llewellyn]

> 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 
>> under the covers?
> 
> Pretty sure:
> 
> www.postgresql.org/docs/current/spi.html

I think that I see what you mean, Adrian. I had read the "PL/pgSQL Under the 
Hood" section to mean that, at run time, ordinary SQL calls were invariably 
made whenever the point of execution reached anything that implied SQL 
functionality (including, famously, expression evaluation). I'd assumed, 
therefore, that when the PL/pgSQL has an "open" statement, and when this is 
encountered at run time, the ordinary SQL "declare" statement was invoked.

But it seems, rather, that the SQL "declare" and the PL/pgSQL "open" each as 
its own implementation by lower-level prinitives—and that these differ in their 
details and in their power of expression. That would explain why the 
"pg_cursors.statement" text differs for cursors with identical properties (like 
scrollability), and the identically spelled subquery, like I showed in my 
earlier email.

It seems odd that the two approaches each has its own  limitation(s).

— You can't create a cursor to execute a prepared statement using the SQL API; 
and you can't create a holdable cursor using the (static) PL/pgSQL API but can 
work around this with dynamic SQL.

—You have to dive down to a lower-level API like "Embedded SQL in C" (a.k.a. 
ECPG) to create a holdable cursor to execute a prepared statement.

But I appreciate that this comes with the territory and that anyone who feels 
strongly about this, and who knows how to do it, can develop their own patch 
and submit it for consideration.

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

[Bryn Llewellyn]

> 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 at or near “execute” ». So it 
>>> looks like Peter’s patch hasn’t yet been adopted. What is the likelihood 
>>> that it will be adopted in a future version?
>> 
>> Closer to zero than one I'd say, given how that thread ended and not 
>> subsequent activity on the feature in the five years since.
> 
> Thanks. Shall I assume, too, that there’s no under-the-hood functionality for 
> cursors, analogous to what happens with a PL/pgSQL program at run-time, that 
> does the moral equivalent of on-demand prepare for a cursor’s defining 
> subquery?

I tried this test.

create procedure s.p(n in int)
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$
declare
  c0  constant refcursor not null := 'c0';
  c1  constant refcursor not null := 'c1';
  c2  constant refcursor not null := 'c2';

  r   int;
  r0  int[];
  r1  int[];
  r2  int[];
begin
  execute 'declare c0 scroll cursor for select s.v from generate_series(1, 10) 
as s(v)';
  open c1 for execute 'select s.v from generate_series(1, $1) as s(v)' using n;
  open c2 for execute format('execute series(%s)', n);

  for j in 1.. 100 loop
fetch c0 into r; r0[j] := r;
exit when not found;
  end loop;

  for j in 1.. 100 loop
fetch c1 into r; r1[j] := r;
exit when not found;
  end loop;

  for j in 1.. 100 loop
fetch c2 into r; r2[j] := r;
exit when not found;
  end loop;

  assert (r1 = r0) and (r2 = r1);
end;
$body$;

prepare series(int) as select s.v from generate_series(1, $1) as s(v);
start transaction;
  call s.p(10);
  select name, statement from pg_cursors order by name;
rollback;

 I expected to get an error from the PL/pgSQL statement that, after "format()" 
has been consumed, boils down to this:

open c2 for execute 'execute series(10)';

And I wondered how this would be reported. But it ran without error. The loop 
over each cursor showed that the result sets from the three alternatives are 
identical. And this is what the "pg_cursors" query showed:

 name |  statement  

--+-
 c0   | declare c0 scroll cursor for select s.v from generate_series(1, 10) as 
s(v)
 c1   | select s.v from generate_series(1, $1) as s(v)
 c2   | execute series(10)
 c9   | execute series(10)

The difference in the value of "pg_cursors.statement" for two cursors based on 
the identical subquery where one is created with the SQL "declare" and  the 
other is created with the PL/pgSQL "open" has puzzled me from the first time 
that I noticed it.

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 under the covers?

Re: cursors with prepared statements

[Bryn Llewellyn]

> 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 
>> looks like Peter’s patch hasn’t yet been adopted. What is the likelihood 
>> that it will be adopted in a future version?
> 
> Closer to zero than one I'd say, given how that thread ended and not 
> subsequent activity on the feature in the five years since.

Thanks. Shall I assume, too, that there’s no under-the-hood functionality for 
cursors, analogous to what happens with a PL/pgSQL program at run-time, that 
does the moral equivalent of on-demand prepare for a cursor’s defining subquery?

Re: cursors with prepared statements

[Bryn Llewellyn]

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 
> different internals). Internally, this just connects existing functionality 
> in different ways, so it doesn't really introduce anything new.

I tried this in Version 15.2 (avoiding the question of how to bind actual 
arguments to placeholders):

prepare series as select s.v from generate_series(1, 10) as s(v);
start transaction;
declare cur scroll cursor for execute series;

It caused the 42601 error, « syntax error at or near “execute” ». So it looks 
like Peter’s patch hasn’t yet been adopted. What is the likelihood that it will 
be adopted in a future version?

p.s. The section doc "PL/pgSQL under the Hood” explains that expressions and 
static SQL statements in a PL/pgSQL source text are effectively prepared when 
first encountered at run time in a session. But simple tests show that there’s 
no evidence if this in the “pg_prepared_statements” catalog view. Is it 
possible that the subquery that is used to declare a cursor (using ordinary top 
level SQL or the PL/pgSQL API) is also invisibly prepared under the hood?

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

[Bryn Llewellyn]

> 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 g(val)
>>  order by g.val;
>> 
>>  fetch first from cur;
>>  fetch last  from cur;
>> rollback;
> 
> FETCH LAST is implemented as "run forward to the end, then back up one". You 
> could imagine adding more infrastructure to allow doing it without backup, 
> but it'd be complicated (not least because the cursor's ending position would 
> be subtly wrong).
> 
>> Finally, I tried the same tests in PG 11.19. There, in Test One, the second 
>> "fetch first" succeeds (and gets the right result). But it has to scroll 
>> backwards to do this. I'm guessing that the Version 11 behavior was regarded 
>> as a bug—and was fixed. When did the behavior change here?
> 
> Probably here:
> 
> git.postgresql.org/gitweb/?p=postgresql.git=commitdiff=c1b7a6c27

Thanks for the quick response, Tom. "Could be done—but not cost-effective 
effort" works for me.

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

[Bryn Llewellyn]

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;

The first "fetch first" succeeds—presumably (given that the current position 
for a freshly-declared cursor is before the first row of the result set) 
because scrolling forwards takes you to the first row. And "fetch next" 
succeeds too. But the next "fetch first" fails with error 55000 ("cursor can 
only scan forward"). This makes perfect sense. It's not the spelling of the 
statement that matters; rather, it's the requested effect, given the cursor's 
current position.

I can imagine, therefore, that  "fetch last" might fail if the last row has 
already been fetched (for example with "fetch all") and you've fallen off the 
end of the result set.

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 g(val)
  order by g.val;

  fetch first from cur;
  fetch last  from cur;
rollback;

It needs only a forward scroll to get to the last row from this starting point.

I tried this as a sanity check:

-- Test Three
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 absolute 10 from cur;
  fetch next from cur;
rollback;

No errors. (But "fetch next" gets an empty result, as expected.) In other 
words, I can fetch the last row, when my current position is before it, by 
addressing it absolutely. But I can't do this by spelling it "last".

Am I missing something? Or might this be a bug?

Finally, I tried the same tests in PG 11.19. There, in Test One, the second 
"fetch first" succeeds (and gets the right result). But it has to scroll 
backwards to do this. I'm guessing that the Version 11 behavior was regarded as 
a bug—and was fixed. When did the behavior change here?

However, Test Two and Test Three behave the same in Version 11 as in Version 
15. So my question about "fetch last" applies there too.

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

[Bryn Llewellyn]

> 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 result set is cached. 
>> But when a WITH HOLD cursor is used, then it’s the *restricted* result set 
>> that’s cached.
>> 
>> I do see that this wouldn't have a detectable effect when the cursor's 
>> defining query doesn't involve any volatile functions. But it does seem that 
>> too much data is cached in the "not holdable" case—and this seems to be a 
>> bad thing for space use and for speed.
> 
> IIUC, all you've demonstrated here is the (sometimes) case for the WITHOUT 
> HOLD cursor where a cache is not used (i.e., the typical case). In this 
> situation the executor, when asked to rewind back to the beginning, goes and 
> restarts execution at the beginning (executor nodes form a tree, it is 
> probable that certain nodes are more efficient at this "start over" thing 
> that others - e.g., I suspect a materialize node sitting in the tree would 
> prevent a sequential scan node from being asked to "start over"), which 
> necessarily involves potentially re-evaluating volatile functions/expressions 
> as noted.

Forgive me. I don't understand your reply. I do understand (having read 
Laurenz's blog post) that sometimes the execution plan for the cursor's 
defining SELECT cannot be run backwards. I'm not sure that it matters whether 
this case is typical or not. It's enough that it can occur. And this is the 
case that I'm interested in. Laurenz says that in this case, for a WITHOUT HOLD 
cursor, the results must be cached to allow scrollability. And the results of 
my tests are consistent with this—up to a point.

However, my results show that for the WITHOUT HOLD case, the restriction that 
the cursor's SELECT might have is *not* applied to what's cached. But the 
restriction *is* applied when the WITH HOLD cache is populated.

And it's this that I'm asking about.

Forget that I ever said "volatile". I just edited the code that I included in 
my previous post. I globally replaced "rndm_series" with "series". And I 
globally replaced "rndm_filter" with "filter". I also removed the "create 
procedure init_rndm()" statement and removed the calls of the procedure. Here 
are the new implementations of "series()" and "filter()""

create function series()
  returns table(v int)
  set search_path = s1, pg_catalog, pg_temp
  language plpgsql
as $body$
begin
  raise info 'series() invoked';

  for v in (select generate_series(1, 10))loop
return next;
  end loop;
end;
$body$;

and

create function filter()
  returns boolean
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$
begin
  raise info 'filter() invoked';
  return true;
end;
$body$;

Then I ran the four tests by hand because I don't know how to spool the "raise 
info" output to a file. In all cases, the "cursor_rows()" invocation just 
reports the ten rows with values in 1 through 10 — of course.

Here's what I saw:

* (1) open_holdable_cursor(holdable=>false, filter_series=>false, caption=>'') *

The "open_holdable_cursor()" call completes silently.

The first "cursor_rows()" invocation reports "series() invoked" once.

Subsequent "cursor_rows()" invocations produce their rows without that message.

* (2) call open_holdable_cursor(holdable=>false, filter_series=>true, 
caption=>'') *

The "open_holdable_cursor()" call completes silently again.

The first "cursor_rows()" invocation again reports "series() invoked" once. And 
then it reports "filter() invoked" ten times.

The second "cursor_rows()" invocation again does *not* report "series() 
invoked". But it *does* report "filter() invoked" ten times. This tells me that 
its the *unrestricted* results that are cached.

It's the same for the third invocation (and any more that I care to do).

*  (3) open_holdable_cursor(holdable=>true, filter_series=>false, caption=>'') *

The "open_holdable_cursor()" call now reports "series() invoked".

The first, and all subsequent, "cursor_rows()" invocations do not say "series() 
invoked".

* (4) open_holdable_cursor(holdable=>true, filter_series=>true, caption=>'') *

The "open_holdable_cursor()" call now reports "series() invoked" followed by 
"filter() invoked" ten times.

The first, and all subsequent, "cursor_rows()" invocations do not bring any 
"raise info" output because the *restricted* results are cached.

I hope that my question is clearer now.

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

[Bryn Llewellyn]

*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 *restricted* result set that’s cached.

I do see that this wouldn't have a detectable effect when the cursor's defining 
query doesn't involve any volatile functions. But it does seem that too much 
data is cached in the "not holdable" case—and this seems to be a bad thing for 
space use and for speed.

I'd like to know the rationale for this design choice but I've been unable to 
find any mention of it in the PG doc (see Note 2).

My test design used this statement:

select v from rndm_series() [where rndm_filter()]

And I used a trivial table function "cursor_rows()” that simply iterated over 
all the rows that the cursor defined, from first through last, to display the 
cursor's result set three times in succession.

This lead to four runs: "WITH or WITHOUT HOLD" by "with or without the WHERE 
clause".

I might just as well have used a simple "series()" function wrapped around 
"generate_series()" that used "raise info" to report that it was called 
together with a simple "filter()" function that always returned true and, 
again, that used "raise info" to report that it was called. But I find it too 
hard to divert "raise info" output to a spool file and have it interleave 
properly with regular SQL output. So I used a volatile "rndm_series() that 
always returned ten rows but (using the random() built-in function) produced a 
different set of values on each call. And I used a volatile "rndm_filter()" 
function that similarly randomly returned TRUE or FALSE. These devices 
dramatized the effect I'm that reporting here and made it simple to record the 
results ordinarily with the \o meta-command.

I'm assuming that my PL/pgSQL functions are opaque to the planner and so that 
it cannot run the execution plan in backwards order and must, therefore, cash 
the cursor's result set.

I would have expected the *restricted* result set to be cached for both flavors 
of cursor—holdable and not. After all, we're taught to expect nonsense results 
when a volatile function's results are cached—so a cursor should be no 
different w.r.t. this thinking.

(Of course, I've read the CAUTION in the PG doc for the DECLARE statement that 
says « Scrollable cursors may give unexpected results if they invoke any 
volatile functions » and I wouldn't use a volatile function in real life.)
——

*Note 1*

I read Laurenz's blogpost "WITH HOLD cursors and transactions in PostgreSQL" 
(www.cybertec-postgresql.com/en/with-hold-cursors-and-transactions-in-postgresql/)
 and I noted this:

«
[Some] execution plans [for a WITHOUT HOLD cursor] require the explicit keyword 
SCROLL for the cursor to become scrollable. Such cursors incur an overhead, 
because the server must cache the entire result set.
»

and then this:

«
PostgreSQL must calculate the complete result set [for a WITH HOLD cursor] at 
COMMIT time and cache it on the server.
»

In other words, both a WITH HOLD cursor (always) and a WITHOUT HOLD cursor 
(sometimes) must cache the entire result set.
——

*Note 2*

Laurenz also wrote this in the "Is the PL/pgSQL refcursor useful in a modern 
three-tier app?" thread that I started:

«
 I understand your confusion, and I believe that the documentation could be 
improved... I personally find that reading the PostgreSQL documentation gets 
you far, but only so far: for deep understanding, you have to read the code... 
I have come to see it as an extension of the documentation that covers the 
details.
»
——

*Testcase*

I used PG Version 15.2. The code that follows is self-contained. You need just 
to connect as an ordinary user to a database where it has the CREATE privilege. 
Copy it into, say, "t.sql" and start it in psql. (Make sure that you have the 
usual « AUTOCOMMIT = 'on' » setting.)

\c :db :u1

drop schema if exists s1 cascade;
create schema s1;
set search_path = s1, pg_catalog, pg_temp;

create procedure init_rndm()
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$
begin
  perform setseed(0.0::double precision);
end;
$body$;

create function rndm_filter()
  returns boolean
  set search_path = pg_catalog, pg_temp
  language sql
as $body$
  select (random() > 0.7::float8);
$body$;

-- Ensure that the planner cannot know how the rows are delivered.
create function rndm_series()
  returns table(v int)
  set search_path = s1, pg_catalog, pg_temp
  language plpgsql
as $body$
declare
  val int not null := 0;
  ctr int not null := 0;
begin
  loop
val := val + 1;
if (random() > 0.9::float8) then
  ctr := ctr + 1;
  exit when ctr > 10;
  v := val; return next;
end if;
  end loop;
end;
$body$;

create procedure 

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

[Bryn Llewellyn]

> 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 that, rather, you open (i.e. create) each of 
>> the three cursors that you use within the two functions that uses them.
> 
> CREATE FUNCTION materialize_foreign_table(...)
> 
> CREATE FUNCTION db_migrate_refresh(...) 
> 
> What is not formal about the above?

I used the term of art "formal argument" ordinarily to denote what's listed in 
parentheses at the start of a subprogram definition. The term stands in 
contrast to "actual argument"—meaning the expression that's used in a 
subprogram invocation to provide a value for the corresponding formal argument. 
Go to this page:

dotnettutorials.net/lesson/types-of-function-arguments-in-python/

and search in it for "Example: formal and actual function arguments in python". 
The blurb that comes just before the example says what I just said. It seems to 
be more common to talk about formal and actual "parameters". But the PG doc 
prefers "argument".

I didn't at all mean that "formal" is good and that Laurenz's code is not 
formal and therefore is bad! So sorry if you took it to mean this. 

> Though it does not matter as, back to the docs again:
> 
> www.postgresql.org/docs/current/plpgsql-cursors.html
> 
> "All access to cursors in PL/pgSQL goes through cursor variables, which are 
> always of the special data type refcursor. One way to create a cursor 
> variable is just to declare it as a variable of type refcursor..." Though 
> maybe you are trying to differentiate between bound and unbound refcursor's...


Ah... you missed my point entirely. Looks like my prose was opaque. I was 
referring to this paradigm as described at the start of the page that you 
referenced:

«
A more interesting usage is to return a reference to a cursor that a function 
has created, allowing the caller to read the rows. This provides an efficient 
way to return large row sets from functions.
»

 This seems to me to imply that the producer function with a "refcursor" 
return is "security definer" and that the consumer subprogram cannot access the 
tables of interest with explicit "select”. I thinks that's nice. 

I needed to know that the definition and use of each "refcursor" variable, in 
Laurentz's code, are in the same subprogram because it's only when this is the 
case that using an ordinary "select count(*)" and an ordinary "for" loop, where 
the "select" is written in place within the "for" clause, are viable 
alternatives.

> Also order by is not relevant for getting a count.

Yes, of course. I know that. (But it does matter in many cases of results 
presentation.) I was simply noting that Laurenz’s use-case didn’t need ordering 
and that the "select" statements will be faster than if ordering were needed. 
Sorry if I implied something that I didn’t mean too. (The example in Laurenz's 
post, www.cybertec-postgresql.com/en/pagination-problem-total-result-count/ 
, 
does use "order by".)

>> I did a little test an saw that this:
>> 
>> move last in cur;
>> get diagnostics n = row_count;
>> 
>> reported just 1—in contrast to what you used:
>> 
>> move forward all in cur;
> 
> Because they are different actions. The first directly moves to the last row 
> in the cursor and counts just that row. The second moves through all the rows 
> left in the cursor and counts all the rows it traversed. It is spelled out 
> here:
> 
> https://www.postgresql.org/docs/current/sql-move.html
> 
> "The count is the number of rows that a FETCH command with the same 
> parameters would have returned (possibly zero)."

Yes, I'm afraid that I expressed myself poorly again. I meant to say that while 
I know nothing about the internals, I can't see how "move last in cur" can know 
where the last row is unless it traverses the entire result set from its 
current position until it finds no more rows. And while its doing this, it may 
just as well count the rows it traverses. It would seem, therefore, that a 
single operation that moves to where you asked for and that tells you how many 
rows it traversed would be enough—rather than two that both achieve the same 
effect in the same time where one gives you the count of rows traversed and the 
other doesn't. But never mind. The simple way to see it is that the operations 
simply do what they do—and I won't worry about how they might have been 
conceived differently.

I did some timing tests. I’ve copied the results and the code at the end—just 
for completeness. Here’s what I concluded.

(1) (and this is unremarkable), if you can find a way to meet your requirement 
with just a single SQL statement, then this is bound to be best. I chose a 
simple requirement: read from a table and 

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

[Bryn Llewellyn]

> 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 code famously reveals only what it does and not what the 
>> author's intention,
>> and overall design philosophy, is. I looked at the README accounts for these 
>> two functions here:
>> 
>> github.com/cybertec-postgresql/db_migrator#materialize_foreign_table
>> github.com/cybertec-postgresql/db_migrator#db_migrate_refresh
>> 
>> But these accounts say nothing about the design of their implementations.
> 
> The documentation describes the purpose of the function and how to use it, 
> not how I went about implementing it. You are interpreting too much. I just 
> wanted to show you a case where scrollable cursors can be useful.
> 

>> The upshot, therefore, is that I'm afraid that I can only guess at why you 
>> use "open, fetch, close" on a refcursor rather than an ordinary cursor for 
>> loop. After all, you use the technique only to traverse metadata tables 
>> about partitions, subpartitions, and columns. I'd be astonished if such 
>> tables have humongous numbers of rows (like hundreds of millions).
> 
> No, I don't expect large result sets, and concurrency won't be a problem. I 
> explained why I used scrollable cursors: to save myself an extra query for 
> the total result set count.

It's only natural to want to understand the design philosophy and things like 
data volumes and possible concurrency concerns when it's recommended that I 
look at the 200 line source code for a function. I wanted only to assure you 
that I'd done my due diligence to get some answers. Thanks for confirming my 
guess about the data volumes and concurrency concerns. Right, I do see that the 
approach that you used very naturally expresses what needs to be done.

— Create a txn-duration "pg_cursors" row for the outer "select" of interest.
— Traverse it to get the row count, using "move" so's not to see the rows.
— Fetch the first row and do something special that needs the row count as well 
as the row.
— Loop over the remaining rows and do more pre-row actions.
— Similar logic in an inner loop for a different "select".
— Close the cursors explicitly.

It seems to me (given what you promised me about data volumes and concurrency) 
that using explicit "select count(*)" with ordinary loops that have the 
"select" statements in the "for" header (and that would use the cursor 
mechanisms under the hood) would work correctly and without harming 
performance. But I'll agree that such code might seem to be less expressive of 
its purpose then your approach.

> Here is another account of how cursors can be useful:
> www.cybertec-postgresql.com/en/pagination-problem-total-result-count/

Thanks! It's a very interesting and helpful read.

You wrote « a WITH HOLD cursor... fetches the complete result set at the end of 
the transaction and materializes it on the server. »

I checked the current doc for the 'declare' statement. It says:

«
In the current implementation, the rows represented by a held cursor are copied 
into a temporary file or memory area so that they remain available for 
subsequent transactions.
»

I tested this with "table t(k int primary key, v int not null)" with 100 
Million rows. (I'm using psql's default mode for "AUTOCOMMIT" to ask for the 
server's behavior "as is"—so no explicit "start transaction" unless I need 
this. And I'm using a PostgreSQL server in a Ubuntu VM on my Mac Book with a 
psql client in the native macOS. First I timed a "with hold" cursor creation.

-- Took ~30 sec.
declare "With Hold" scroll cursor with hold for select k, v from t order by k;

Then I timed a "without hold" cursor creation for the same "select":

start transaction;
  -- Took less than ~20 ms
  declare "Without Hold" scroll cursor without hold for select k, v from t;

This certainly bears out what you and the doc said about "with hold".

W.r.t. this thread's "Subject", how do you fetch successive batches from a 
session duration cursor in successive requests from a browser when these go 
through a connection pool and the general philosophy is "check out connection, 
use it, release connection" so that successive browser requests will each use a 
different session? You refer to this with your « transaction level connection 
pooling won’t work with WITH HOLD cursors, since they are bound to the database 
connection ». (In this context, your point about showing an approximate result 
is well-taken.) Does anybody use interactive client-server apps these days? Or 
are you thinking of non-interactive report generation running on a client 
machine dedicated to that purpose?

W.r.t. your « With WITH HOLD cursor pagination, the whole result set is 
calculated in the beginning, so the result set count comes for free. », I timed 
this:

-- Took 

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

[Bryn Llewellyn]

> 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 would 
>> help me understand the mechanics.
> 
> 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
> 
> The goal is to get the query result count right away, without having to run a 
> second query for it: you declare the cursor, move to the end of the result 
> set, fetch the ROW_COUNT, then move back to the beginning of the result set 
> and start fetching the result rows.
> 
> ...I personally find that reading the PostgreSQL documentation gets you far, 
> but only so far: for deep understanding, you have to read the code. It is 
> usually well
> documented and readable, and I have come to see it as an extension of the 
> documentation that covers the details.

Thanks for the link to your SQL file at the line where you get the row count in 
the way that you describe. I saw that this is in the PL/pgSQL source text for 
function "materialize_foreign_table()" (~200 lines). And I saw that you use the 
cursor mechanism that we're discussing here in only one other function, 
"db_migrate_refresh()" (~480 lines). But this second one doesn't use the "move 
forward all" device to get a row count. I looked only at these two functions.

I noted that neither of these functions has a refcursor formal argument and 
that, rather, you open (i.e. create) each of the three cursors that you use 
within the two functions that uses them. I noted, too,  that for the three 
"select" statements that you use to open your refcursors, none of these has an 
"order by". I noted that your code implements "create table destination" and 
"insert into destination... select from source..." where order doesn't matter. 

However, source code famously reveals only what it does and not what the 
author's intention, and overall design philosophy, is. I looked at the README 
accounts for these two functions here:

github.com/cybertec-postgresql/db_migrator#materialize_foreign_table
github.com/cybertec-postgresql/db_migrator#db_migrate_refresh

But these accounts say nothing about the design of their implementations. The 
accounts, and the bigger picture, seem to imply that read consistency in the 
presence of concurrent activity from other sessions is not a concern. I'm 
guessing that this is accounted for upstream from how the code that I'm looking 
at operates—i.e. that the source database is extracted to staging tables like, 
say, export does so that your code operates as the only session that reads 
from, creates, and populates the tables that it references.

The upshot, therefore, is that I'm afraid that I can only guess at why you use 
"open, fetch, close" on a refcursor rather than an ordinary cursor for loop. 
After all, you use the technique only to traverse metadata tables about 
partitions, subpartitions, and columns. I'd be astonished if such tables have 
humongous numbers of rows (like hundreds of millions).

As a sanity test, I did this:

create function s.t_count()
  returns int
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$
declare
  cur refcursor := 'cur';
  n int not null := 0;
begin
  open cur for select v from s.t;
  move forward all in cur;
  get diagnostics n = row_count;
  move absolute 0 in cur;
  return n;
end;
$body$;

Then I timed these two alternatives on a test table with ten million rows.

select count(*) from s.t;
select s.t_count();

They both got the same answer. But my function took about twice as long as the 
native count(*). Mind you, with "only" 10 million rows (and using a PG cluster 
in a VM on my Mac Book) the times were tiny: ~600ms versus ~300ms.

Then I tried this:

create procedure s.p(tab in text, mode in text)
  set search_path = pg_catalog, pg_temp
  security definer
  language plpgsql
as $body$
declare
  cur   constant refcursor not null := 'cur';
  stmt  constant text  not null := format('select k, v from s.%I', 
tab);
  cnt_stmt  constant text  not null := format('select count(*) from s.%I', 
tab);

  kk   int not null := 0;
  vv   int not null := 0;
  kint;
  vint;
  nint not null := 0;
  cnt  int not null := 0;
begin
  case mode
when 'naive' then
  execute cnt_stmt into cnt;
  for kk, vv in execute stmt loop
n := n + 1;
assert kk = n and vv = n*2;
  end loop;
  assert n = cnt;

when 'refcursor' then
  open cur for execute stmt;
  move forward all in cur;
  get diagnostics cnt = row_count;
  move absolute 0 in cur;

  loop
fetch cur into k, v;
exit when not found;
n := n + 1;
assert k = n and v = n*2;
  end loop;
  close cur; -- Just as a formality.
 

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

[Bryn Llewellyn]

> 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" section in the 
>> "PL/pgSQL" chapter. And the sections in the "SQL Commands" chapter for 
>> "declare", "fetch" and "close". But several of the key concepts didn't sink 
>> in and this prevented me not only from understanding what some of the 
>> examples showed but, worse, from being able to use the right vocabulary to 
>> express what confused me.
> 
> Given this from your original question:
> 
> « Anyway, without anything like Oracle PL/SQL's packages, you have no 
> mechanism to hold the opened cursor variable between successive server calls. 
> »
> 
> What part of this [a particular code example] did not make sense in that 
> context?

First off, despite the fact that I've clearly annoyed you (for which I 
apologize), I have found these exchanges very helpful. So thank you very much.

Your questions can be summarized as "Why couldn't you understand the doc? And 
why did you mix questions about use-cases with questions about the mechanics?" 
The answer has to do with psychology. I probably can't explain this 
convincingly. That's why it's taken me a long time to respond. I also had to do 
lots of testing before responding to make sure that the mental model that I've 
formed for myself is consistent with these.

You may not be interested in what follows. But, anyway, here goes.

— I happen to have many years of experience with Oracle Database and PL/SQL. 
The latter has analogous features to PL/pgSQL's "refcursor". But the 
differences between the notions in the two environments are enormous. My 
attempt to understand the latter was hindered by my understanding of the 
former. I accept that this is *my* problem and that I could never expect that 
the PG doc would cater for such a reader.

— Oracle Database doesn't expose scrollability for PL/SQL's equivalent of 
"refcursor". So I had never come across use cases where this was beneficial. I 
wanted, therefore, to hear about some. I thought that insights here would help 
me understand the mechanics. But I didn't get anything beyond "Scrollability is 
what it is. If you don't need it, don't use it."

Anyway, never mind all that now. Here's what I now (think that) I 
understand—with some comments on what made it hard for me to grasp.

— The key notion is what is referred to sometimes as "portal" and sometimes as 
"cursor". This is the thing that's global within, and private to, a session, 
that's uniquely identified by a bare name, that, and that's listed in 
"pg_cursors". I believe that in typical use, a cursor has only transaction 
duration. But (and only when you use the SQL API) you can create a cursor with 
(up to) session duration

— The doc pages for the "declare", "fetch", and "close" SQL statements don't 
mention "portal" and use only "cursor". They use the term to mean the 
underlying phenomenon and use wording like: "DECLARE allows a user to create 
cursors"; "You can see all available cursors by querying the pg_cursors system 
view"; "FETCH retrieves rows using a previously-created cursor"; "CLOSE frees 
the resources associated with an open cursor. After the cursor is closed, no 
subsequent operations are allowed on it. A cursor should be closed when it is 
no longer needed." However, these pages leave the term "open" undefined, though 
it's used. It seems that it has no meaning. Rather, a cursor with a particular 
name either exists or not. You create it with "declare" and drop it with 
"close". And that's it. If "open" means anything, it's just another word for 
"exists". (The fact that "pg_cursors" doesn't have a boolean column called 
"open" supports this understanding.) The sentence "After the cursor is closed, 
no subsequent operations are allowed on it." is equivalent to "After a table is 
dropped, no subsequent operations are allowed on it." But who would bother to 
say that? Notice that "pg_cursors" has a column called "creation_time" — and 
not "declaration time".

— On the other hand, the doc page "43.7. Cursors" uses "portal" a lot—and never 
says that it means exactly the same as "cursor" qua term of art (and not qua 
keyword). It does say "...a so-called portal containing the active query for 
the cursor." This suggests a subtle difference in meaning between "portal" and 
"cursor" and a notion of containment. I can't make any sense of that. It says 
things like "Before a cursor can be used to retrieve rows, it must be opened. 
(This is the equivalent action to the SQL command DECLARE CURSOR.)" This is the 
closest that it comes to saying that the SQL API and the PL/pgSQL API both 
manipulate the same thing—what you see in "pg_cursors". The sentence that I 
quoted is equivalent to saying "Before you can insert a row into a table, the 
table 

Re: Schema/user/role

[Bryn Llewellyn]

> 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 no relationship to 
> roles aside from the possibility, if you so choose, to define a schema to 
> have the same name as a role, in which case that schema becomes parts of that 
> role's default search_path.
> 
> There is no low-level difference between role and user.  A user is a role 
> with the login privilege.

I came from Oracle, too. I soon came to see that these facts about PG are an 
improvement on Oracle Database:

— In ORCL, "user" and "role" are distinct notions but in PG they collapse into 
one. This means that the nodes in a PG role hierarchy can all own objects. And 
schemas are among these owned objects.

— In ORCL, "user" and "schema" are 1:1 and so, informally, collapse into a 
single notion. In PG, a role can own many schemas and this can be used to 
advantage as a classification scheme for objects with the same owner.

However, there's more to say.

— "set role" (to a role upon which the current role is a grantee) has to be 
re-learned. For example, it cannot be governed by a required password. And it 
has the same effect on "current_role" (versus "session_role") that a "security 
definer" subprogram has (but with no push-pop notion).

— It's not enough to say, for example, "grant select on table s1.t to r2" (when 
s1.t is owned by, say, r1 and the schema s1 is not owned by r2). You have, at a 
coarser granularity, to also say "grant usage on schema s1 to r2". (This is 
nice because you can prevent r2 from using any of r1's objects with just a 
single "revoke".)

— The "search_path" notion sounds at first to be appealing. And, loosely, it 
makes up for the fact that PG has no synonym notion. However, just as in ORCL 
there's a whole discussion about how nefarious actors can capture a synonym 
with a bogus local object, so is there a similar discussion in PG about 
nefarious misuse of redefining the search path (no privilege governs this). 
This discussion is further complicated by the fact that "pg_temp" and 
"pg_catalog" are inevitably on the search path whether or not you mention them 
(and that when you don't, their positions in the search order is surprising). 
My personal conclusion is that you must always use a schema-qualified 
identifier for all objects in real application code (verbosity 
notwithstanding). This is rather like the ORCL practice never to create 
synonyms and to refer to ORCL-shipped objects as "sys.dbms_output" and the like.

— Closely related, a freshly-created database has a "public" schema (unless you 
customize the "template1" database to change this. This is very useful for ad 
hoc testing when you're learning something, But it's a nuisance in the database 
that a serious application uses.

— Having said this, a caveat is probably needed for "pg_catalog" objects 
because even common-or-garden objects like the "+" operator are implemented 
ordinarily via various objects in the "pg_catalog" schema. And the syntax for 
invoking an operator using a schema-qualified identifier is baroque:

select ((2+3) operator(pg_catalog.=) (1+4))::text;

I decided, eventually, to use schema-qualified identifiers for everything 
except for "pg_catalog" objects and always to set the search path thus:

set search_path = pg_catalog, pg_temp;

and especially always to use that list as an attribute in a subprogram's source 
code.

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

[Bryn Llewellyn]

> 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 the 
sections in the "SQL Commands" chapter for "declare", "fetch" and "close". But 
several of the key concepts didn't sink in and this prevented me not only from 
understanding what some of the examples showed but, worse, from being able to 
use the right vocabulary to express what confused me.

It's very much clearer now than when I started this thread, about twenty-four 
hours ago. Here's (some of) what I believe that I now understand.

"refcursor" is a base type, listed in pg_type. This sentence seems to be key:

«
A refcursor value is simply the string name of a so-called portal containing 
the active query for the cursor. This name can be passed around, assigned to 
other refcursor variables, and so on, without disturbing the portal.
»

Maybe it's better to say that a portal has a defining "select" statement and 
acts as a pointer to the potential result set that its select statement 
defines. A portal also represents the position of the current 
(next-to-be-fetched) row it that set. The doc that I've found doesn't make it 
clear how much of the entire result set is materialized at a time. But the 
implication is that it's materialized only in portions and that one portion is 
purged to make room for another.

You can create a portal instance using either top-level SQL (with the "declare" 
statement) or using PL/pgSQL by declaring a refcursor variable for its name and 
then using that as the argument of "open". Only in top-level SQL, the "with 
hold" option for "declare" lets you create a portal instance outside of a 
transaction block. This has session duration. (Or you can pre-empt this with 
the "close" statement.) Otherwise, you must use the "declare" statement within 
an ongoing transaction. With this choice, it vanishes when the transaction 
ends. You can also create a portal instance by using PL/pgSQL. (There's no 
"with hold" option here.)

A portal instance exists within the session as a whole, even though you can 
declare the refcursor to denote it as a PL/pgSQL subprogram's formal parameter 
or as a PL/pgSQL local variable. This means that you can create a portal 
instance using PL/pgSQL and (when you know its name) fetch from it using 
top-level SQL

The open portal instances in a particular session are listed in pg_cursors. 
(Why not pg_portals?) When the instance was created with the SQL "declare" 
statement, pg_cursors.statement shows the verbatim text that follows the 
"declare" keyword. (In other words, not a legal SQL statement.) When the 
instance was created using PL/pgSQL, pg_cursors.statement shows the verbatim 
text that follows (in one creation approach variant) "open  for" in 
the defining block statement's executable section. (In other words, and with a 
caveat about placeholders, this is a legal SQL statement.)

A portal instance is uniquely identified by just its name. (You cannot use a 
schema-qualified identifier to create it or to refer to it.) And (just like a 
prepared statement) the name must be unique only within a particular session.

There are many ways to set the name of a portal instance. Here are some 
examples. First top-level SQL:

begin;
declare "My Refcursor" cursor for select k, v from s.t order by k;
select name, statement from pg_cursors;
fetch forward 5 in "My Refcursor";
end;

I noticed that 'select pg_typeof("My Refcursor")' within the ongoing txn fails 
with '42703: column "My Refcursor" does not exist'.

Now, PL/pgSQL:

create function s.f(cur in refcursor = 'cur')
  returns refcursor
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$
begin
  open cur for select k, v from s.t order by k;
  return cur;
end;
$body$;

begin;
select s.f('My Cursor');
select name, statement from pg_cursors;
fetch forward 5 in "My Cursor";
end;

Arguably, it's pointless to use a function to return the name of the portal 
instance that you supplied as an input—and you might just as well write this:

create procedure s.p(cur in refcursor = 'cur')
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$
begin
  open cur for select k, v from s.t order by k;
end;
$body$;

begin;
call s.p('My Cursor');
select name, statement from pg_cursors;
fetch forward 5 in "My Cursor";
end;

You could sacrifice the ability to name the portal instance at runtime like 
this:

create procedure s.p()
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$
declare
  cur refcursor := 'My Cursor';
begin
  open cur for select k, v from s.t order by k;
end;
$body$;

begin;
call s.p();
select name, statement from pg_cursors;
fetch forward 5 in "My Cursor";
end;

You can even let the runtime system make up a name for you. But you need to go 
back to the function 

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

[Bryn Llewellyn]

> 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 that my "select" used, I can't tell how much of the result set 
>> I'll need or where reading result #n1 informs me that I next need to scroll 
>> and read result #n2. So I was looking for a convincing example.
> 
> Huh?
> 
> You provided your own example earlier:
> 
> "Of course, it all falls into place now. I can see how I could write a client 
> app in, say, Python to write a humongous report to a file by fetching 
> manageably-sized chunks, time and again until done with a function like my 
> "g()" here, from a cursor that I'd opened using a function like my "f()"."

My “Humongous report via client-side Python” example doesn’t call for me to 
abandon it part way through. Nor does it call for me to leap forwards as I 
discover facts along the way that make me realize that I need immediately to 
see a far distant fact by scrolling to where it is (and especially by scrolling 
backwards to what I’ve already seen). It was an example of this that I was 
asking for. The bare ability to do controlled piecewise materialization and 
fetch is clear.

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

[Bryn Llewellyn]

> 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 a reference to a cursor that a 
>> function has created, allowing the caller to read the rows. This provides an 
>> efficient way to return large row sets from functions.
>> »
>> 
>> I can't convince myself that this division of labor is useful. And 
>> especially I can't convince myself that the "pipeling" capability is 
>> relevant in a three-tier app with a stateless browser UI.
> 
> You seem to think that a client request corresponds to a single database 
> request, but that doesn't have to be the case.  Satisfying a client request 
> can mean iterating through a result set.
> 
> Cursors shine wherever you need procedural processing of query results, or 
> where you don't need the complete result set, but cannot tell in advance how 
> much you will need, or where you need to scroll and move forward and backward 
> through a result set.

Re « You seem to think that a client request corresponds to a single database 
request », I meant no more than what psql models when you hit "return"after 
terminating an ordinary SQL statement with semi-colon (i.e. not a 
meta-command). I used "client" to mean a program that connects directly to the 
PostgreSQL server (using TCP/IP)—and not the ultimate human client who's using, 
say, a browser. Perhaps I should have said "top-level SQL call". (To keep this 
simple, let's assume that I don't use "backslash-semicolon" to send two or more 
top-level SQL calls in one round trip.)

So in this example:

begin;
declare cur cursor for select k, v from s.t order by k;
fetch forward 10 in cur;
fetch absolute 90 in cur;
fetch forward 10 in cur;
commit;

where I hit "return" after each semicolon, I'd say that I made six top-level 
SQL calls—and that lines up with the fact that each of "begin". "declare", 
"fetch", and "commit" has its own page in the "SQL Commands" section of the PG 
doc.

It sounds like you prefer "database request" for this. Is that right?

I was trying to get at the distinction between issuing a particular SQL 
statement as a top-level SQL call and issuing it from a PL/pgSQL subprogram.

I've read wording like what you wrote following "Cursors shine wherever you 
need …" elsewhere. But I can't picture a concrete use case where, not 
withstanding the "where" restriction that my "select" used, I can't tell how 
much of the result set I'll need or where reading result #n1 informs me that I 
next need to scroll and read result #n2. So I was looking for a convincing 
example.

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

[Bryn Llewellyn]

> 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 possible to set up a 
>> cursor that encapsulates the query, and then read the query result a few 
>> rows at a time. One reason for doing this is to avoid memory overrun when 
>> the result contains a large number of rows. (However, PL/pgSQL users do not 
>> normally need to worry about that, since FOR loops automatically use a 
>> cursor internally to avoid memory problems.) A more interesting usage is to 
>> return a reference to a cursor that a function has created, allowing the 
>> caller to read the rows. This provides an efficient way to return large row 
>> sets from functions.
>> »
>> On its face, it seems to make sense. And I’ve written a few proof-of-concept 
>> tests. For example, I wrote a “security definer” function that's owned by a 
>> role that can select from the relevant table(s) that returns refcursor. And 
>> I called it from a subprogram that's owned by a role that cannot select from 
>> the relevant table(s) to loop through the rows. But I can't convince myself 
>> that this division of labor is useful. And especially I can't convince 
>> myself that the "pipeling" capability is relevant in a three-tier app with a 
>> stateless browser UI. Here, the paradigm has the client-side app checking 
>> out a connection from the pool, generating the entire response to the 
>> end-user's request, releasing the connection, and sending the response back 
>> to the browser. This paradigm isn't consistent with allowing the end user to 
>> navigate forwards and backwards in a scrollable cursor that is somehow held 
>> in its open state in in the sever by the the middle tier client on behalf of 
>> a browser session that comes back time and again to its dedicated middle 
>> tier client and thence yo its dedicated database server session. (Anyway, 
>> without anything like Oracle PL/SQL's packages, you have no mechanism to 
>> hold the opened cursor variable between successive server calls.)
> 
> I guess that would depend on how you define a server call:
> 
> www.postgresql.org/docs/current/plpgsql-cursors.html#PLPGSQL-CURSOR-USING
> 
> "Once a cursor has been opened, it can be manipulated with the statements 
> described here.
> 
> These manipulations need not occur in the same function that opened the 
> cursor to begin with. You can return a refcursor value out of a function and 
> let the caller operate on the cursor. (Internally, a refcursor value is 
> simply the string name of a so-called portal containing the active query for 
> the cursor. This name can be passed around, assigned to other refcursor 
> variables, and so on, without disturbing the portal.)
> 
> All portals are implicitly closed at transaction end. Therefore a refcursor 
> value is usable to reference an open cursor only until the end of the 
> transaction."
> 
>> Is it fair to say that the PL/pgSQL refcursor is useful, at best, only in 
>> very special use-cases?

Ah… I see. I had read this wrongly:

> « Internally, a refcursor value is simply the string name of a so-called 
> portal containing the active query for the cursor. This name can be passed 
> around, assigned to other refcursor variables, and so on, without disturbing 
> the portal. »


I thought that it was an "under the hood" explanation and that the only thing 
that you could see after entry into the PL/pgSQL subprogram that will be the 
top of the stack would be an opaque value (sometimes called a handle in other 
contexts) that was accessible only from PL/pgSQL.

I hadn't yet tried this in psql:

create schema s;
create table s.t(k int primary key, v text not null);
insert into s.t(k, v) values (1, 'cat'), (2, 'dog');

create function s.f(k_in in int)
  returns refcursor
  language plpgsql
as $body$
declare
  s_f_cur cursor(k_in int) for select v from s.t where k = k_in;
begin
  open s_f_cur(k_in);
  return s_f_cur;
end;
$body$;

create function s.g(cur in refcursor)
  returns text
  language plpgsql
as $body$
declare
  v text;
begin
  fetch cur into v;
  return v;
end;
$body$;

begin;
select ||s.f(1)|| as cur
\gset
select s.g(:cur) as result;
end;

I just did. And the result of "select s.g(:cur)" was the expected "cat".

It requires a leap of imagination, or help from the pgsql-general list, to get 
to this. So thanks!

Might the doc add an example like this?

Of course, it all falls into place now. I can see how I could write a client 
app in, say, Python to write a humongous report to a file by fetching 
manageably-sized chunks, time and again until done with a function like my 
"g()" here, from a cursor that I'd opened using a function like my "f()".

B.t.w., when I said "top-level call", I meant the SQL statement that a client 
issues—in this case most 

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

[Bryn Llewellyn]

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 result a few rows at a 
time. One reason for doing this is to avoid memory overrun when the result 
contains a large number of rows. (However, PL/pgSQL users do not normally need 
to worry about that, since FOR loops automatically use a cursor internally to 
avoid memory problems.) A more interesting usage is to return a reference to a 
cursor that a function has created, allowing the caller to read the rows. This 
provides an efficient way to return large row sets from functions.
»

On its face, it seems to make sense. And I’ve written a few proof-of-concept 
tests. For example, I wrote a “security definer” function that's owned by a 
role that can select from the relevant table(s) that returns refcursor. And I 
called it from a subprogram that's owned by a role that cannot select from the 
relevant table(s) to loop through the rows. But I can't convince myself that 
this division of labor is useful. And especially I can't convince myself that 
the "pipeling" capability is relevant in a three-tier app with a stateless 
browser UI. Here, the paradigm has the client-side app checking out a 
connection from the pool, generating the entire response to the end-user's 
request, releasing the connection, and sending the response back to the 
browser. This paradigm isn't consistent with allowing the end user to navigate 
forwards and backwards in a scrollable cursor that is somehow held in its open 
state in in the sever by the the middle tier client on behalf of a browser 
session that comes back time and again to its dedicated middle tier client and 
thence yo its dedicated database server session. (Anyway, without anything like 
Oracle PL/SQL's packages, you have no mechanism to hold the opened cursor 
variable between successive server calls.)

Is it fair to say that the PL/pgSQL refcursor is useful, at best, only in very 
special use-cases?

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

[Bryn Llewellyn]

> 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 to be table-specific I would define its signature to 
> be:
> 
> function(i_var tablename)
> 
> i.e., they accept a row of said table as the input.
> 
> As most functions don't, and likely shouldn't, care from what source their 
> arguments come from, but instead perform an operation based upon the data 
> types of the inputs, using %type is not something I've ever really had 
> occasion to use.
> 
> Given that the source of record loses that metadata anyway that would be 
> further reason why I would just pretend the %type syntax doesn't exist.  I 
> suspect that on rare occasion its use could be beneficial, and maybe if I 
> remember it exists while writing one of those use cases up I'll use it, but 
> there doesn't seem to be much downside to just using concrete types 
> everywhere.  Code comments can handle intent well enough here, as opposed to 
> some, frankly, esoteric feature/syntax (which seems not all that SQL-ish but 
> I don't see where it is specifically noted as our extension).
> 
> You comments do seem, at first glance at least, to refute the statement in 
> the documentation:
> 
> > The type of a column is referenced by writing table_name.column_name%TYPE. 
> > Using this feature can sometimes help make a function independent of 
> > changes to the definition of a table.
> 
> I mean, while it is indeed "independent of changes to the definition of a 
> table" so does simply writing "text"...the %type syntax seems like it should 
> follow the changes of the definition of a table...

Suppose you have a masters-and-details table pair where each table uses an 
autogenerated PK. A masters row, following the text book, will also have a 
unique business key. Similarly, and in one plausible design, a details row will 
have a unique business identifier within the context of its masters row so that 
its unique business key will have two parts. Now you want to insert a new 
master row and a few details for it. This is a fine use case for a PL/pgSQL 
procedure with these input formal arguments:

— the new master’s unique business key, and some other facts for it.
— an array of “new within-master details, each with its within-master business 
ID and some other facts for it”

The code writes itself: "insert into masters values... returning PK into m_pk" 
followed by "insert into details... select... unnest(details_arr_in)". This, at 
least on the face of it, would be an obvious candidate for using %type. Both 
for the various input arguments and for the local variable, "m_pk", for the 
masters PK that gets inserted into the details table. Except for the fact that 
it doesn't live up to its promise. David said "the %type syntax seems like it 
should follow the changes of the definition of a table". I agree. But it 
doesn't. And I don't suppose that it ever will.

However, the requirement for "single point of definition" (hereinafter SPOD) is 
broader than just PL/pgsql local variables and ideally (but not usably) 
subprogram formal arguments. For example, route distance between two points on 
the surface of the earth, with agreed units, scale, precision, and the 
requirement to fall between zero and a sensible upper limit, is a good 
candidate for SPOD-ification. A domain gives you exaclty the mechanism you need.

I did this little test:

-- in cr-function.sql
create function s.f(v_in in s.num)
  returns text
  security definer
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$
declare 
  r text not null := '';
begin
  select k::text into strict r from s.t where v = v_in;
  return r;
end;
$body$;

and

-- in test.sql
-- Deliberate poor definition of domain "s.num" (upper bound is too small).
create domain s.num as numeric constraint num_ok check(value > 0.0 and value <= 
10.0);

create table s.t(k integer primary key, v s.num);
insert into s.t(k, v) values (1, 5);

\ir cr-function.sql
select s.f(5.0);

-- Improved definition of domain "s.num".
-- Using "create" rather than "alter" for maximum generality.
-- No might want to change the base type, too, in a different use case.
create domain s.num_new as numeric constraint num_ok check(value > 0.0 and 
value <= 20.0);

alter table s.t add column v_new s.num_new;
update s.t set v_new = v::s.num_new;
alter table s.t rename column v to v_old;
alter table s.t rename column v_new to v;
alter table s.t drop column v_old;
drop domain s.num cascade; --> drop cascades to function s.f(s.num)
alter domain s.num_new rename to num;
insert into s.t(k, v) values (2, 14.5);

\ir cr-function.sql
select s.f(14.5);

Using the domain, and everything that this implies when you want to change its 
definition, means that you're forced to accept using "delete domain... 

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

[Bryn Llewellyn]

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 their modes and datatypes, "security invoker/definer" and so on 
together with the enquoted actual PL/pgSQL source text.

When the DDL succeeds, the PL/pgSQL source text is recorded verbatim in 
"pg_proc". But the meaning of everything else is parsed out and represented as 
individual fields in "pg_proc" and other tables like "pg_namespace". This is 
reflected by the canonical form that "\sf" uses, for example:

create table s.t(k integer primary key, v integer);

create procedure s.p(k_in in t.k%type, v_in t.v%type)
  language plpgsql
as $body$
begin
 /* ... */
end;
$body$

\sf s.p

This is the output:

CREATE OR REPLACE PROCEDURE s.p(k_in integer, v_in integer)
 LANGUAGE plpgsql
AS $procedure$
begin
 /* ... */
end;
$procedure$

This shows that my use of "%type"was consumed at "create" time and then 
recorded in the catalog as what it translated to. The consequence is that if 
the table is dropped and re-created thus:

drop table s.t;
create table s.t(k integer primary key, v text);

the metadata is not changed in sympathy and so "\sf" shows the same as before. 
This means that I have to find my original DDL script and re-run it—albeit 
without making any changes to its text.

In other words, the benefit of using "%type" for the declaration of a formal 
argument is less than using it for the declaration of a local variable.

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?

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

[Bryn Llewellyn]

> 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, the "into" target cannot be a local variable whose data 
>> type is a composite type. That comes as a complete shock. Moreover, it isn't 
>> true—as Tom's advice shows. (I tried it and it worked.) I don't know what 
>> "simple" (as in "simple variable" means. I'm guessing that it means 
>> "single-valued" in the Codd-and-Date sense so that, for example, 42 and 
>> 'dog' are simple but array[1, 2, 3] isn't. My test (below), with a column 
>> "c1" and a local variable "arr", both of data type "int[]",  showed that 
>> "select… c2… into arr…" works fine here. So the wording in the doc that I 
>> copied above could be improved.
> 
> Reading the meaning of "simple" to be "not record or row variables" seems 
> like the safe bet, since those are covered in the first part of the sentence. 
>  As a composite type is the umbrella term covering both record and row that 
> sentence most certainly does allow for such a variable to be the target.  But 
> when it is, each individual column of the result gets mapped to individual 
> fields of the composite type.  This seems like a reasonable use case to 
> define behavior from.
> 
>> postgresql composite type constructor
>> 
>> For example, "8.16. Composite Types" 
>> (www.postgresql.org/docs/current/rowtypes.html#id-1.5.7.24.6) talks only 
>> about anonymous "row". And this little test seems to show that "row" and 
>> "record" mean the same thing—but you seed to write (or you see) different 
>> spellings in different contexts:
>> 
>> with
>>   c(r) as (values(row(1, 'a', true)))
>> select c.r, pg_typeof(c.r) from c;
> 
> Composite types that don't have a system-defined name are instead named 
> "record".  "Row" usually means that not only is the composite type named but 
> the name matches that of a table in the system.  IOW, as noted above, 
> "composite type" is a type category or umbrella that covers all of these 
> cases.
>  
>> Confusing, eh? There seems to be some doc missing here too that defines 
>> "type constructor" and that uses "(f1, ..fn)::qualified_type_id". (The 
>> "create type" account should x-ref it.)
> 
> You just pointed to the relevant documentation, and adding it to create type 
> doesn't seem like a good fit but if someone wanted to I'm not sure I'd veto 
> it.
>  
>>   -- Tom's approach. Not nice.
>>   -- Two separate "select" statements to avoid
>>   -- 42601: record variable cannot be part of multiple-item INTO list.
>>   select (a.c1).a1, (a.c1).a2 into r  from s.t as a where a.k = 1;
>>   select a.c2 into arrfrom s.t as a where a.k = 1;
> 
> Yeah, I can see this as a natural consequence of the "column per field" 
> behavior decision.  Maybe it should be covered better in the docs?  Seems 
> like an unfortunate corner-case annoyance seldom encountered due to the 
> general disuse of composite types.
> 
>>   for the_row in (select a.c1, a.c2 from s.t as a order by a.k) loop
>> z := the_row.c1::text||' / '||the_row.c2::text;  
>>return next;
>>   end loop;
> 
> Doesn't seem like a terrible work-around even in the single-row case...

If "composite type" is the umbrella term that covers "row", "record", and the 
result of "create type… as (…)", what is the term of art for the latter? The 
account of "pg_type.typtype" says this:

« b for a base type, c for a composite type (e.g., a table's row type), d for a 
domain, e for an enum type, p for a pseudo-type, or r for a range type. »

This wording doesn't help me because there are no DDLs to create occurrences of 
"row" or "record". And the companion for a table is a real schema-object, 
distinct from the table itself like the result of "create type… as (…)" using 
the names and data types of the table's columns. (I'm assuming here that no 
particular schema-object can have a row both in pg_class and pg_type.) So it 
seems wrong to call this a "row type" because doing so leaves no term of art 
left over for the ephemeral result of "row(1, 'a', true)" which pg_typeof 
anyway calls "record".

Finally, what terms of art do PG experts use to distinguish between 
single-valued data types like "integer", "text", "boolean" and so on and 
multi--valued data types like "array", "row", "record", and the result of 
"create type… as (…)"?

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

[Bryn Llewellyn]

> 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 in the second assignment is somewhat non-intuitive; 
> but covered here[2].  Probably it could use more examples.
> 
> The final form fits into a procedural flow better than the SQL-based one.  
> Since plpgsql allows for procedural flow this makes sense.  The composite 
> variable reference is simply: main_type_name.field_name  Hence the second 
> example here[1]
> 
> [1] 
> https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT
> [2] 
> https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW

Thanks for the links, David. I had earlier written this:

> "select (17, 42)::s.t2 into r2" doesn't work

and Tom replied thus:

> [use] "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
> composite value to the whole target, don't use SELECT INTO; use an assignment 
> statement.

Thanks, I see this now. This text from reference [2] explains it:

«
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, the "into" target cannot be a local variable whose data type 
is a composite type. That comes as a complete shock. Moreover, it isn't true—as 
Tom's advice shows. (I tried it and it worked.) I don't know what "simple" (as 
in "simple variable" means. I'm guessing that it means "single-valued" in the 
Codd-and-Date sense so that, for example, 42 and 'dog' are simple but array[1, 
2, 3] isn't. My test (below), with a column "c1" and a local variable "arr", 
both of data type "int[]",  showed that "select… c2… into arr…" works fine 
here. So the wording in the doc that I copied above could be improved.

Tom's "don't use SELECT INTO; use an assignment statement" is fine when the 
restriction guarantees to produce exactly one row. But otherwise, you need a 
"cursor for loop". Further, "an assignment statement" works only when the RHS 
is a scalar subquery. But sometimes you want a "select list" with more than one 
column—where any/all can have multi-valued data types.

It seems to me that "select into" or a "cursor for loop", where each has a 
single target declared as "record" is the most general approach.

I tried yet another test. Here's the set-up:

create type s.x as (a1 int, a2 text);

-- Write my own constrctor.
create function s.x(a1_in in int, a2_in in text)
  returns s.x
  language plpgsql
as $body$
declare
  r s.x;
begin
  r.a1 := a1_in;
  r.a2 := a2_in;
  return r;
end;
$body$;

do $body$
declare
  v1 constant s.x not null := s.x(17, 'dog'::text);
  v2 constant s.x not null := (17, 'dog'::text)::s.x;
begin
  assert v1 = v2;
end;
$body$;

create table s.t(k int primary key, c1 s.x, c2 int[]);
insert into s.t(k, c1, c2) values
  (1, (17, 'cat'::text)::s.x, array[11, 12, 13]),
  (2, (42, 'dog'::text)::s.x, array[21, 22, 23]);

The aim, here, was to demonstrate once and for all that (in this example), 
"(17, 'dog'::text)::s.x" is a perfectly fine type constructor for "s.x". There 
seems to be another documentation gap here. "Array constructor" is a 
well-defined term of art; and the "array[…]" syntax implements it. But Google 
search, and the PG doc's own search, get nothing useful for this:

postgresql composite type constructor

For example, "8.16. Composite Types" 
(www.postgresql.org/docs/current/rowtypes.html#id-1.5.7.24.6) talks only about 
anonymous "row". And this little test seems to show that "row" and "record" 
mean the same thing—but you seed to write (or you see) different spellings in 
different contexts:

with
  c(r) as (values(row(1, 'a', true)))
select c.r, pg_typeof(c.r) from c;

Confusing, eh? There seems to be some doc missing here too that defines "type 
constructor" and that uses "(f1, ..fn)::qualified_type_id". (The "create type" 
account should x-ref it.)

Anyway, back to my test… here's the rest of it.

create function s.f()
  returns table(z text)
  language plpgsql
as $body$
declare
  r s.x;
  arr int[];
  the_row record;
begin
  -- Tom's approach. Not nice.
  -- Two separate "select" statements to avoid
  -- 42601: record variable cannot be part of multiple-item INTO list.
  select (a.c1).a1, (a.c1).a2 into r  from s.t as a where a.k = 1;
  select a.c2 into arrfrom s.t as a where a.k = 1;
  z := r::text||' / '||arr::text;   
return next;

  z := '';  
return next;

  select a.c1, a.c2 into the_row from s.t as a where a.k = 1;
  z := 

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

[Bryn Llewellyn]

> 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
> composite value to the whole target, don't use SELECT INTO; use an assignment 
> statement.

Thanks for the clue. Your "select 17, 42 into r2" does indeed work. But I can't 
find a way to act on your "If you want to assign a
composite value to the whole target… use an assignment statement".

Here's my test. It's closer to what I wanted to do. I wanted to assign a value 
to a PL/pgSQL variable whose data type is a composite type from component 
values from a table. And I happened to start off with "select into" rather than 
with a subquery on the RHS of an assignment. In another context, I want to 
construct a value of my composite type from variables that represent its 
components.

create table s.t(k int primary key, c1 int, c2 int);
insert into s.t(k, c1, c2) values(1, 17, 42);
create type s.x as (c1 int,  c2 int);

create function s.f()
  returns table(z text)
  security definer
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$
declare
  my_c1 int := 17;
  my_c2 int := 42;
  r s.x;
  txt text;
begin
  r := (select (a.c1, a.c2)::s.x from s.t a where a.k = 1);
  z := (r.c1)::text||' / '||(r.c2)::text; return 
next;

  select a.c1, a.c2 into r from s.t a where a.k = 1;
  z := (r.c1)::text||' / '||(r.c2)::text; return 
next;

  begin
r := (my_c1, my_c2)::s.t;
  exception when cannot_coerce then
z := '"cannot_coerce" handled.';  return 
next;
  end;

  r.c1 := my_c1;
  r.c2 := my_c2;
  z := (r.c1)::text||' / '||(r.c2)::text; return 
next;
end;
$body$;

select s.f();

This is specific to "language plpgsql" subprograms. So I should find the rules 
that I need to understand in "Chapter 43. PL/pgSQL - SQL Procedural Language" 
(www.postgresql.org/docs/11/plpgsql.html). But I can't. However, I'm not very 
good at finding the relevant doc when I need it. Where is it?

Going from what you said, and my most recent test, here, the rules seem to be 
inscrutable—and non-composable.

I wonder if it all boils down to the strange-to-me anonymous, polymorphic 
"record" notion.

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

[Bryn Llewellyn]

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 (17, 42)::s.t2;

This is the result:

 (17,42)

(I know that plsql is doing an under-the-covers typecast to "text" to display 
the result. The error (or at least, to me, shock) comes when I bring PL/pgSQL 
into the picture:

create function s.f()
  returns table(z text)
  security definer
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$
declare
  r1 s.t1;
  r2 s.t2;
  txt text;
begin
  r1 := (select ('cat', 'dog')::s.t1);
  z := '1: '||r1.c1||' / '||r1.c2;return 
next;

  select ('cat', 'dog')::s.t1 into r1;
  z := '2: '||r1.c1||' / '||coalesce(r1.c2, '');return 
next;

  r2 := (select (17, 42)::s.t2);
  z := '3: '||(r2.c1)::text||' / '||(r2.c2)::text;return 
next;

  begin
select (17, 42)::s.t2 into r2;
  exception
-- invalid input syntax for type integer: "(17,42)"
when invalid_text_representation then
  z := 'invalid_text_representation caught';  return 
next;
  end;

  select (17, 42)::s.t2 into txt;
  r2 := txt;
  z := '4: '||(r2.c1)::text||' / '||(r2.c2)::text;return 
next;
end;
$body$;

select s.f();
txttxt
It produces this output:

 1: cat / dog
 2: (cat,dog) / 
 3: 17 / 42
 invalid_text_representation caught
 4: 17 / 42

Results #1 and #3, from "UDT-value := (scaler subquery)", are what I expected.

Result #2 tells me what seems to be going on—and it dumbfounds me. The first 
text field of my UDT value got "(cat,dog)"; and now that all input values have 
been consumed, "c2" got NULL.

Do you (all) expect this? And if so, what's the story?

This outcome seems to explain the error. The text value "(17,42)" for "c1", 
spirited up from "(17, 42)::s.t2", can't be converted to an integer.

Yet more mysterious is why the workaround, go via an intermediate text value, 
succeeds:

select (17, 42)::s.t2 into txt;
r2 := txt;

But if I compress it thus:

select (((17, 42)::s.t2)::text)::s.t2 into r2;

then I'm back to the same 22P02 error:

invalid input syntax for type integer: "(17,42)"

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

[Bryn Llewellyn]

> 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 changes
>> * (including drops). We don't currently support on-the-fly replacement
>> * of non-composite types, else we might want to do this for them too.
>> */
> 
> I'm not quite sure that that's related, really. That code is concerned with 
> detecting changes to an already-identified type (that is, type OID NNN has 
> different details now than it did before). It seemed to me that Bryn's 
> question was more about reacting to cases where a given string of source code 
> would resolve to a different type OID than it did a moment ago. We don't have 
> a great story on that, I'll agree. You can get into that sort of problem 
> without anywhere near the amount of complexity embodied in this example --- 
> for instance, I'm pretty sure we don't re-parse type references just because 
> somebody else executed an ALTER TYPE RENAME somewhere.

I tried a new test, inspired by what Tom wrote:

create table s.t(k int primary key, c1 int, c2 int, c3 int);
insert into s.t(k, c1, c2, c3) values(1, 17, 42, 57);
create type s.x as (c1 int, c2 int, c3 int);

create function s.f()
  returns text
  security definer
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$
declare
  r s.x;
begin
  r  := (select (a.c1, a.c2, a.c3)::s.x from s.t as a where a.k = 1);
  return r::text;
end;
$body$;

select s.f();

It produced the expected result:

 (17,42,57)

Then I did this (still in the same session):

alter type s.x drop attribute c3 cascade;
select s.f();

It produced this new result (with no reported error):

 (17,42)

Then I reconnected as the same user to the same database to force a fresh 
analysis of the the source code of "s.f()" on it's first execution:

\c - :the_user
select s.f();

Now I got a  42846 error, "cannot cast type record to s.x", with the detail 
"Input has too many columns".

Here's my conclusion. It's for the scenario that you have PL/pgSQL subprograms 
among the objects that your client-side app uses. It's rather obvious.

(1) If you do any DDLs that affect any of the objects that an application uses, 
then you should exit all of the client sessions (presumably this means stopping 
the connection pool for most apps) before you do the patching. The reasoning is 
simple. A few spot tests show how things can go wrong if you don't do this. And 
there's no doc to tell you what, if any, DDLs you might safely do without 
stopping all but the session(s) that do the patching.

(2) You have to take full responsibility for the impact analysis so that you 
can make all the changes that are needed to take you from the pre-patch 
mutually consistent state of all objects to the new post-patch mutually 
consistent state during the window when only the session(s) doing the patching 
are active. Native PG doesn't provide much metadata or tooling to help you 
here. You need your own reliable humanly written external doc of your system.

(3) The same general thinking extends to client-side code. Carefully specified 
and executed testing, using a dedicated and realistic test env,  is critical.

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

[Bryn Llewellyn]

> 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 such a 
 DDL and re-start them only when all such DDLs are done successfully.
>>> 
>>> No. If you simply "ADD COLUMN" to an existing table the "terminate all 
>>> client-sessions" action is excessive, IMO.
>> 
>> I tried another test. The results surprised me:
>> 
>> create table s.t(k int primary key, c1 text);
>> insert into s.t(k, c1) values (1, 'cat');
>> 
>> create function s.f(k_in in int)
>>   returns text
>>   security definer
>>   set search_path = pg_catalog, pg_temp
>>   language plpgsql
>> as $body$
>> declare
>>   r s.t%rowtype;
>> begin
>>   select * from s.t into strict r where t.k = k_in;
>>   return r::text;
>> end;
>> $body$;
>> 
>> select s.f(1);
>> 
>> This is the result (no surprises yet):
>> 
>>  (1,cat)
>> 
>> Now, still in the same session:
>> 
>> alter table s.t add c2 text;
>> update s.t set c2 = 'dog' where k = 1;
>> select s.f(1);
>> 
>> This is the new result. It surprised me:
>> 
>>  (1,cat,dog)
>> 
>> I had expected that %rowtype would be translated, and frozen, at "create" 
>> time into the columns "k" and "c1". So I expected the second execution of 
>> "s.f()" give some flavor of wrong answer.
>> 
>> Where can I read what I need in order to understand the difference here, 
>> using %rowtype, and in the first test that I posted, using %type? Why is the 
>> meaning of %type frozen at "create" time while (as it seems) %rowtype is 
>> re-evaluated at runtime—presumably on every execution of the subprogram?
>> 
>> I discovered a new surprise in this general space with this test:
>> 
>> create function s.g()
>>   returns text
>>   security definer
>>   set search_path = pg_catalog, pg_temp
>>   language plpgsql
>> as $body$
>> declare
>>   c1 text;
>>   c2 text;
>> begin
>>   select 'cat', 'dog', 'mouse' into c1, c2;
>>   return c1||' '||c2;
>> end;
>> $body$;
>> 
>> select s.g();
>> 
>> It runs without error and shows this:
>> 
>>  cat dog
>> 
>> Why don't I get a runtime error telling me that I have more "select list" 
>> items than "into" targets?
> 
> You may want to send this to the mailing list too, for posterity.

Oops… I somehow slipped up and replied only to David. Here it is, now, for the 
archive.

I also slipped up by saying « frozen, at "create" time ». Thanks for pointing 
this out, David. I did indeed mean to write « frozen, in a particular session 
and for the remainder of that session's duration, when the PL/pgSQL subprogram 
is first executed. »

I read the replies from David and Tom. But I must confess that I can't work out 
what the current consensus on what's intended is w.r.t. load-time versus 
execution-time response to a change definition of %type and %rowtype.

 (Never mind yet whether, or to what extent, this is currently documented.)

I believe that I'm hearing that there is thought to be a genuine bug, 
orthogonal to the main thing that I was asking about, thus: an attempt to 
select N1 items into N2 targets, where N1 and N2 differ, should cause a 
run-time error. (N1 and N2 might differ, as I demonstrated, simply because of a 
programmer-authored error. Or they might differ now, in some session, where 
they earlier didn't, because of changes in the environment with which this 
session's in-memory representation of the PL/pgSQL program has lost currency).

Returning to David's earlier comment, thus:

> If you simply "ADD COLUMN" to an existing table the "terminate all 
> client-sessions" action is excessive, IMO.


Why not err on the side of caution and (I trust) guaranteed currency of each 
session's in-memory representation of a PL/pgSQL program with the environment 
in which it executes?

After all, you add a column in order to use it. And this means that at the very 
least client-side code must be changed to do this. And this means quiescing use 
of the application and then re-starting it with new behavior. Is re-starting 
the connection pool before opening up the new app for use so expensive that 
it's worth trying to reason when it might be safe to avoid this re-start?

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

[Bryn Llewellyn]

> 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 nature of record variables presents another problem in this 
> > connection. When fields of a record variable are used in expressions or 
> > statements, the data types of the fields must not change from one call of 
> > the function to the next, since each expression will be analyzed using the 
> > data type that is present when the expression is first reached.
> 
> Though possibly… variable declarations [are] considered structural:
> 
> > The instruction tree fully translates the PL/pgSQL statement structure,
> 
>> drop table s.t cascade;
>> create table s.t(k serial primary key, v varchar(10));
>> select s.f(1);
> 
> still text as the compiled artifact is re-executed
>  
>> \c d0 d0$u0
>> select s.f(1);
> 
> now varchar as the function is recompiled during its first use in this 
> session.
> 
> Restarting everything is an approach to dealing with uncertainty. This 
> particular use case, though, isn't one that I'd be overly worried about. 
> Actually making DDL changes of this nature should be rare if not forbidden. 
> Once live on-the-fly column type changes just shouldn't happen so having a 
> plan in place that accommodates them is adding cost for no real benefit.

Thanks. I believe that you and I agree on the proper practice, paraphrased here 
slightly w.r.t. what I wrote in my point #6 in my email that started this 
thread:

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 such a DDL and 
re-start them only when all such DDLs are done successfully.

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

[Bryn Llewellyn]

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, described below, right?

1. With function "s.f()" as presented, the attempt to create it when table 
"s.t" doesn't yet exist fails with the 42601 syntax error: « invalid type name 
"s.t.v%type" ». This is expected because some tests must succeed at "create 
time" else the attempt becomes a no-op.

2. Following creating "s.f()" after creating table "s.t", executing it, and 
then dropping "s.t", the pg_proc row for "s.f()" remains intact with the 
original source text. This reflects the fact that the successful "create" 
didn't record any dependency info so that PG doesn't know what the human 
observer knows.

3. After "s.t" is re-instated, now with a different data type for "t.v", the 
SQL query reports the new column data type (and the new content). After all, 
this is just ordinary query behavior reflecting the current state of the db. 
However the reported type of the local variable "v_out" is still "text" (as it 
was at create time) and not "varchar" as it now is. This nominal error reflects 
the fact that the representation of the to-be-interpreted function, in session 
memory, was built when "s.f()" was first executed and is now cached. Because 
there are no dependencies, nothing tells PG to rebuild the representation of 
the to-be-interpreted function, in session memory.

5. After re-connecting, we have a brand-new session with as yet no cached info. 
Therefore, the representation of the to-be-interpreted function must be rebuilt 
when it's first referenced. And now, it sees table "s.t" with a "varchar" 
column.

6. All this leads to rather obvious practice advice: DDLs on objects that an 
application uses (at least when the app's database artifacts include PL/pgSQL 
subprograms) are unsafe while the app is in use. You must stop all 
client-sessions before doing such DDLs and re-start them only when all DDLs are 
done successfully.



-- Connect as an ordinary user to a convenient database.
\c d0 d0$u0

prepare f_prosrc as
select prosrc
from
  pg_proc p
  inner join
  pg_namespace n
  on p.pronamespace = n.oid
  inner join
  pg_roles r
  on p.proowner = r.oid
where p.proname = 'f'
and   n.nspname = 's'
and   r.rolname = $1;

create schema s;
create table s.t(k serial primary key, v text);
insert into s.t(v) values ('cat-1'), ('dog-1'), ('mouse-1');

create function s.f(k_in in int)
  returns text
  security definer
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$
declare
  v_type text  not null := '';
  v_out s.t.v%type not null := '';
begin
  select pg_typeof(t.v)::text, t.v
  into strict v_type, v_out
  from s.t
  where t.k = k_in;
  return 'pg_typeof(t.v): '  ||v_type ||' / '||
 'pg_typeof(v_out): '||pg_typeof(v_out)::text ||' / '||
 'value: '   ||v_out;
end;
$body$;

select '--';
select s.f(1);

drop table s.t cascade;
select '--';
execute f_prosrc('d0$u0');

create table s.t(k serial primary key, v varchar(10));
insert into s.t(v) values ('cat-2'), ('dog-2'), ('mouse-2');

-- "s.f()" still reports "text" for "pg_typeof(v_out)".
select '--';
select s.f(1);

\c d0 d0$u0
-- Only now have we lost the cached result of "compiling" the function "s.f()".
-- Now reports "character varying” for "pg_typeof(v_out)".
select '--';
select s.f(1);

RESULTS (using “\t on” mode)

 --

 pg_typeof(t.v): text / pg_typeof(v_out): text / value: cat-1

 --

 < The expected "language plpgsql" source text — verbatim as it was entered, 
including "%type" (untranslated). >

 --

 pg_typeof(t.v): character varying / pg_typeof(v_out): text / value: cat-2

 --

 pg_typeof(t.v): character varying / pg_typeof(v_out): character varying / 
value: cat-2

Re: transaction_isolation vs. default_transaction_isolation

[Bryn Llewellyn]

> 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. Client Connection Defaults
>> https://www.postgresql.org/docs/15/runtime-config-client.html
>> 
>> has an entry for each setting thus:
>> 
>> «
>> default_transaction_isolation (enum): Each SQL transaction has an isolation 
>> level, which can be either “read uncommitted”, “read committed”, “repeatable 
>> read”, or “serializable”. This parameter controls the default isolation 
>> level of each new transaction. The default is “read committed”.
>> 
>> transaction_isolation (enum): This parameter reflects the current 
>> transaction's isolation level. At the beginning of each transaction, it is 
>> set to the current value of default_transaction_isolation. Any subsequent 
>> attempt to change it is equivalent to a SET TRANSACTION command.
>> »
>> 
>> ...Is my hypothesis right?
> 
> I don't think so. There is a transaction when the SET is executed, it just 
> immediately commits and so doing that is basically pointless. I suppose the 
> difference in behavior when using SET TRANSACTION versus changing this 
> configuration variable might be surprising but all that is happening is the 
> one is giving you an error when you do something pointless and the other just 
> does the pointless thing without complaint.
> 
> Frankly, the non-default versions are simply the one and only way you can see 
> what the current value is.  That you can then SET them to change it is I 
> suppose convenient but since there is an actual SQL command to do the same 
> one should use that command, not the setting.

I believe that I see it now. Thanks. There's a Heisenberg effect lurking here. 
You can't observe anything, or change anything, except by using SQL. And most 
SQL statements have the effect that, if there's no transaction ongoing at the 
moment that such a statement is issued, then what I've decided to call "single 
statement automatic transaction mode" is started and then immediately ended. 
Otherwise, such a statement has its effect within the ongoing transaction—which 
continues to be ongoing after its completion.

Just a few statements, like "start transaction", "commit", "set transaction", 
and the like don't have the capacity to initiate single statement automatic 
transaction mode but, rather, have meanings and possible error outcomes 
according to whether or not a transaction is ongoing. Things would be a lot 
simpler if the "transaction_isolation" run-time parameter were read-only and 
the attempt to set it caused

55P02: parameter "transaction_isolation" cannot be changed now

Oh well, I'll simply adopt the practice never to set it and to read it only 
when (by virtue of the session history that I know) my session is currently in 
an ongoing explicitly started transaction.

transaction_isolation vs. default_transaction_isolation

[Bryn Llewellyn]

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 an entry for each setting thus:

«
default_transaction_isolation (enum): Each SQL transaction has an isolation 
level, which can be either “read uncommitted”, “read committed”, “repeatable 
read”, or “serializable”. This parameter controls the default isolation level 
of each new transaction. The default is “read committed”.

transaction_isolation (enum): This parameter reflects the current transaction's 
isolation level. At the beginning of each transaction, it is set to the current 
value of default_transaction_isolation. Any subsequent attempt to change it is 
equivalent to a SET TRANSACTION command.
»

The first ("default") notion makes sense because its account uses the word 
"new". The implication is that it's legal to set it during an ongoing txn; but 
that doing this won't have any effect until it has been ended. On the other 
hand, the "set transaction" SQL statement is legal only during an ongoing txn. 
(An attempt when there is none causes the 25P01 error, "SET TRANSACTION can 
only be used in transaction blocks". Moreover, if you do this:

begin;
insert into s.t(v) values(19);
set transaction isolation level serializable;

then you get the 25001 error "SET TRANSACTION ISOLATION LEVEL must be called 
before any query".

This implies that if, as the doc says, "set transaction_isolation: is 
equivalent to "SET TRANSACTION ISOLATION LEVEL", then you should get the same 
errors at the same moments. But you don't. This works fine when there's no 
ongoing txn:

set transaction_isolation = serializable;

But this:

begin;
insert into s.t(v) values(19);
set transaction_isolation = serializable;

causes the 25001 error.

This suggests that the semantics of "set transaction_isolation" is 
context-sensitive:

- when there's no ongoing txn. it has the same effect as "set 
default_transaction_isolation".
- when you're in a txn, it has the same effect as ""SET TRANSACTION ISOLATION 
LEVEL"

Is my hypothesis right?

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

[Bryn Llewellyn]

>> 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 feature that you’re looking for exists 
or not. The psql doc would print out at about thirty pages with a normal font 
size. So reading it from top to bottom would be quite a task.

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

[Bryn Llewellyn]

>> 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 how I might have found this without human help. (Neither generic 
Google search nor using the PG docs own search got me anywhere.) But now I know 
that what I asked about is possible and I’ve seen it work, I trust that I’ll 
remember the trick.

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

[Bryn Llewellyn]

> 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", 
>> then other sessions never know what I did.)
> 
> This may or may not be true. If the second session currently has a 
> transaction open in REPEATABLE READ or SERIALIZABLE mode, it *won't* see the 
> effects of that statement, since it took its snapshot at the start of the 
> transaction (to be technical, at the first statement in that transaction), 
> and holds it until commit time. However, a transaction in READ COMMITTED mode 
> *will* see the results after the statement completes.
> 
>> I can't see that a client-side "autocommit off" mode like psql supports 
>> brings me anything of value.
> 
> There's general agreement on that point.
> 
> https://www.cybertec-postgresql.com/en/disabling-autocommit-in-postgresql-can-damage-your-health/

Thanks, Christophe. Yes, I sacrificed correctness for brevity. I should have 
stipulated that observations made from a second concurrent session are to be 
done using a singleton "select" in its own txn—i.e. outside of an explicitly 
started txn (whether this is started by hand or using a client's implementation 
of "autocommit off"). Thanks, too, for the xref to the Cybertec post by Laurenz 
Albe.

And thanks, David, for your separate tip about using « psql -c ». I tried it 
and watched the server log. Sure enough, I saw this:

2023-02-20 12:42:44.993 PST [2540504] d0$u0@d0 LOG:  0: statement: insert 
into s.t(v) values(17); insert into s.t(v) values(42);
2023-02-20 12:42:44.993 PST [2540504] d0$u0@d0 LOCATION:  exec_simple_query, 
postgres.c:971

It seems a bit odd that psql has no syntax to ask for this in its interactive 
mode. But, yes, it doesn't actually matter because I can get the same semantics 
by starting a txn myself.

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

[Bryn Llewellyn]

> 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 intended SQL statement)? Or is it done server-side?
 
 rjuju...@gmail.com wrote
 
 It's always done on the client side, postgres itself doesn't know about 
 this feature.
>>> 
>>> x...@thebuild.com wrote:
>>> 
>>> It's done server-side.  Note that what really happens is that, when a 
>>> statement begins execution and there is no open transaction, a snapshot is 
>>> taken and then released when the statement finishes (just as happens in 
>>> READ COMMITTED mode).  No piece of code literally injects a BEGIN and a 
>>> COMMIT statement to make it happen.
>> 
>> x...@thebuild.com wrote:
>> 
>> PostgreSQL has no idea that mode even exists: it either sees statements 
>> without transactions, which run in their own transaction, or BEGIN / COMMIT 
>> statements.  Because client stacks have traditionally loved to provide their 
>> own transaction semantics, they might inject BEGIN and COMMIT statements, 
>> but that's not something PostgreSQL sees.
>> 
>> (And I have never liked the term "autocommit mode"; it really doesn't reveal 
>> much about what is going on.
> 
> david.g.johns...@gmail.com wrote:
> 
> To rephrase the other responses, the client-defined setting has no inherent 
> relationship to the concept of a PostgreSQL session.  How the client uses 
> that setting is internal to the client and whatever abstraction(s) it 
> provides the programmer.
> 
> So far as the server is concerned it is always auto (implicit) begin, and 
> also auto (implicit) commit - absent receiving a BEGIN SQL Command in which 
> case it disables implicit commit and (more or less) waits for a COMMIT or 
> ROLLBACK before ending the transaction that it implicitly started.

Thanks, all, for these replies. In summary, I think that it all makes sense to 
me now.

I've written a bit more, here, for my own benefit. Do please speak up if I got 
it wrong.

Christophe also said “I may have misinterpreted your question...” No worries. I 
didn’t have the proper vocabulary to ask a clear question. I might just as well 
have said “Tell me about autocommit—in all of the different domains where 
there's something to say. Anyway... it seems clear from the fact that nobody 
mentioned a doc reference that there is no useful account in the PG docs of 
what's been explained in this thread. Pity.

I think that I can summarize what seems to be the consensus expressed by 
David's and Christophe's replies with reference to a thought experiment. 
Imagine that I studied up the material described here:

Chapter 55. Frontend/Backend Protocol
https://www.postgresql.org/docs/current/protocol.html

and that I wrote a program using the low-level language of my choice to send 
and receive TCP/IP messages to/from my PostgreSQL server. Assume that I limit 
myself to so-called Simple Queries and that I don't use "prepare" and "execute".

You've told me that, at this level of communication, there is no "autocommit" 
mode to set. Rather, things just behave in a certain way, like you've described.

1. If I send over just "insert into s.t(v) values(17)" then a second session 
will see the effect of my "insert" immediately. (I don't need a trailing 
semicolon; but I'm allowed to have one if I like to.) Moreover, If I set « 
log_statement = 'all' » in my postgresql.conf and tail the log file, all I see 
is my bare insert statement. I don't see "begin" before it and "commit" after 
it. I conclude, therefore, that the defined semantics of sending over a single 
SQL statement are to start a transaction under the covers, to execute the 
statement, and then to commit it under the covers. It's tempting to say that 
the effect of my statement is automatically committed—or to say that PostgreSQL 
natively implements automatic commit. But I s'pose that it's enough simply to 
describe what happens without inventing any terminology.

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", then 
other sessions never know what I did.)

3. Chapter 55 also has a section "Multiple Statements In A Simple Query". But 
this feature seems to do no more semantically beyond implicitly achieving what 
I could do by surrounding several statements explicitly with "begin; ... 
commit;". There is, though, the non-semantic aspect of round-trip reduction. It 
seems that psql doesn't expose doing many statements in a simple query. (At 
least, that's what I see in the server log when I put several statements on a 
single line (with psql's autocommit on).

4. If I use psql and set autocommit to off, I see that it knows 

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

[Bryn Llewellyn]

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 "SET" in ECPG 
(Embedded SQL in C) or "self.session.set_session(autocommit=...)" in Python. 
And that the mode is a property of the current session.

But 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 intended 
SQL statement)? Or is it done server-side?

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

[Bryn Llewellyn]

> 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 cases 
>> like this, I can't write a useful "order by r" because the values of "r" 
>> interleave, for example, rule-offs between sections of the report, various 
>> sub-headings, and actual query results. The required order is exactly the 
>> order in which my code produces the rows.
> 
> Seems safe enough to rely upon if the query is indeed: SELECT * FROM fn(); 
> The system has to consume the output of the function call in its serial order 
> and has no reason to then reorder things prior to producing the final result 
> set. Though I'd probably still consider adding a "report line number" column 
> to the output for end-user usability or if they want to sort the report and 
> then return to the physical order.
> 
> I am curious whether a user-defined set-returning function is allowed to 
> specify "WITH ORDINALITY" like the built-in UNNEST function does to produce 
> the output row numbers external to the function body and signature.

Thanks, David. Thanks, too, to pavel.steh...@gmail.com for your separate reply 
that also says that I can rely on seeing the order in which I produce the rows 
in the function's implementation. And yes, I realize that Postgres table 
functions are not pipelined in the way that they can be, if you choose this, in 
Oracle Database.

Given that the order is pre-calculated, it seems that "with ordinality" can add 
line numbering "after the fact" reliably and with minimum clutter when it's 
needed. I tried these two variants:

create function f1()
  returns setof text
  language sql
as $body$
  values ('skiing'), ('cycling'), ('running');
$body$;
and:

create function f2()
  returns table(r text)
  language plpgsql
as $body$
begin
  r := 'skiing'; return next;
  r := 'cycling'; return next;
  r := 'running'; return next;
end;
$body$;

select t.line_no, t.report_text
from f1() with ordinality as t(report_text, line_no);

Each supports this same query

select t.line_no, t.report_text
from fN() with ordinality as t(report_text, line_no);

and gets this same result:

 line_no | report_text 
-+-
   1 | skiing
   2 | cycling
   3 | running

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

[Bryn Llewellyn]

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 that they cannot rely 
on what seems to be a reliable order unless they clutter the SQL (and add the 
cost of sorting) by adding an "order by" clause.

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 cases like this, 
I can't write a useful "order by r" because the values of "r" interleave, for 
example, rule-offs between sections of the report, various sub-headings, and 
actual query results. The required order is exactly the order in which my code 
produces the rows.

Here's a trivial, artificial, example:

create function report_1()
  returns table(r text)
  language plpgsql
as $body$
declare
  rule constant text not null := lpad('—', 40, '—');
begin
  r := rule;  return next;
  r := 'MY REPORT';   return next;
  r := rule;  return next;

  r := '';   return next;
  r := 'Section 1';   return next;
  r := '-';   return next;
  for r in (
 select v::text
 from t1
 order by v
   ) loop
/**/  return next;
  end loop;

  r := '';return next;
  r := 'Section 2';   return next;
  r := '-';   return next;
  for r in (
 select v::text
 from t2
 order by v desc
   ) loop
/**/  return next;
  end loop;
  r := rule;  return next;
end;
$body$;

And this is the output, given some suitable content in t1 and t2, from "select 
r from report_1()" with no "order by":

 
 MY REPORT
 
 
 Section 1
 -
 10
 12
 14
 16
 
 Section 2
 -
 27
 24
 21
 

I've written no end of reports this way. And I've never, ever, seen the rows 
come out in an order that differs from the order in which they're written. (Of 
course, I know that this proves nothing.) Here's a variant that lets me say 
"select r from report_1() order by k":

create function report_2()
  returns table(k int, r text)
  language plpgsql
as $body$
declare
  rule constant text not null := lpad('—', 40, '—');
begin
  k = 1; r := rule;   return next;
  k = k + 1; r := 'MY REPORT';return next;
  k = k + 1; r := rule;   return next;

  k = k + 1; r := ''; return next;
  k = k + 1; r := 'Section 1';return next;
  k = k + 1; r := '-';return next;
  for r in (
 select v::text
 from t1
 order by v
   ) loop
k = k + 1;return next;
  end loop;

  k = k + 1; r := ''; return next;
  k = k + 1; r := 'Section 2';return next;
  k = k + 1; r := '-';return next;
  for r in (
 select v::text
 from t2
 order by v desc
   ) loop
k = k + 1;return next;
  end loop;
  k = k + 1; r := rule;   return next;
end;
$body$;

It adds an uncomfortable amount of clutter.

* Is it essential for correctness? *

It's annoying that the use of "return next" prevents the pattern that each 
"print line" follows from being encapsulated into a procedure. But it is what 
it is, yes?

Re: Intervals and ISO 8601 duration

[Bryn Llewellyn]

> 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 identicality issue:
> 
> WITH inters AS (
> SELECT
> '1 day 2 hours'::interval AS i1,
> '26 hours'::interval AS i2
> )
> SELECT
> *,
> to_char(i1,'HH24:MM:SS') AS i1_char,
> to_char(i2,'HH24:MM:SS') AS i2_char,
> i1=i2 AS "Equal?"
> FROM inters;
> 
>i1   |i2| i1_char  | i2_char  | Equal?
> +--+--+--+
>  1 day 02:00:00 | 26:00:00 | 02:00:00 | 26:00:00 | t

I struggled to understand this whole murky area when I was writing the “Date 
and time data types and functionality” section for the YugabyteDB doc. 
(YugabyteDB uses the Postgres SQL processing code “as is” on top of its own 
distributed storage layer. All the examples in my doc work identically in 
vanilla PG.)

The implied question here is this: is the interval “1 day 2 hours” the same as 
the interval “26 hours”? It might seem that the answer is “yes”—as it surely 
must be. But, sorry to say, that the answer is actually “no”. Confused? You 
will be. Most people are until they’ve wrapped their head in a towel and 
puzzled it through for a few days. This shows you what I mean:

set timezone = 'America/Los_Angeles';
with c as (
  select '2023-03-11 20:00 America/Los_Angeles'::timestamptz as 
original_appointment)
select
  original_appointment::text as "original appointment",
  (original_appointment + '1 day 2 hours'::interval)::text as "postponed by 
'1_day 2 hours'",
  (original_appointment + '26 hours'::interval)::text as "postponed by 
'24_hours'"
from c;

This is the result:

  original appointment  | postponed by '1_day 2 hours' | postponed by 
'24_hours' 
+--+-
 2023-03-11 20:00:00-08 | 2023-03-12 22:00:00-07   | 2023-03-12 23:00:00-07

Two different answers! The “trick” here is that the time of the original 
appointment and the postponed times straddle the 2023 “spring forward” moment 
(at least as it happens in the America/Los_Angeles timezone). And the 
resolution of what at first might seem to be a bug come when you realized that 
you must make a distinction between clock time and calendar time.

This query sheds a bit more light on the matter:

with c(i1, i2) as (
  select '1 day 2 hours'::interval, '26 hours'::interval)
select
  interval_mm_dd_ss(i1)::text as i1, 
  interval_mm_dd_ss(i2)::text as i2,
  (i1 = i2)::text as "i1 = i2",
  (i1==i2)::text as "i1 == i2"
from c;

I defined the “interval_mm_dd_ss()” function and the “==” operator. (I called 
it the “strict equality operator for interval values”.)

I believe that your question implies that you want my “interval_mm_dd_ss()” 
function. I can’t be sure what you want. But I dare to speculate that you might 
find it helpful to read (at least) the references that I’ve listed below. Start 
with the informal treatment in my blog post.

Tom, Adrian, and David might remember my endless questions in this general 
space in March 2021. This, from Tom, answers the present question:

https://www.postgresql.org/message-id/DAABCBE6-6DFF-4C28-9CED-0AA8053FE12C%40yugabyte.com
 

p.s. Some other posts came in while I was writing this. My advice on 
“justify_interval()” is to avoid it.


PostgreSQL Timestamps and Timezones: How to Navigate the Interval Minefield
https://www.yugabyte.com/blog/postgresql-timestamps-timezones-interval-minefield/

Two ways of conceiving of time: calendar-time and clock-time
https://docs.yugabyte.com/preview/api/ysql/datatypes/type_datetime/conceptual-background/#two-ways-of-conceiving-of-time-calendar-time-and-clock-time

type interval_mm_dd_ss_t as (mm, dd, ss) 
https://docs.yugabyte.com/preview/api/ysql/datatypes/type_datetime/date-time-data-types-semantics/type-interval/interval-utilities/#type-interval-mm-dd-ss-t-as-mm-dd-ss

The user-defined "strict equals" interval-interval "==“ operator
https://docs.yugabyte.com/preview/api/ysql/datatypes/type_datetime/date-time-data-types-semantics/type-interval/interval-utilities/#the-user-defined-strict-equals-interval-interval-operator

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

[Bryn Llewellyn]

>>> 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 based on their authorisation levels. This 
>>> delineation of user access has to also be supported by the database, if 
>>> possible. Also, for audit purposes the data is append only. And the design 
>>> is based on just two tables(vertices and edges) to emulate a 
>>> document-oriented(jsonb) graph structure.
>>> 
>>> Postgres is the database I am leaning towards for this project. But as I am 
>>> not a DBA and also a solo developer, I am trying to understand how I can 
>>> spend less time managing the DB and more time developing the application. I 
>>> would like to have a distributed and fault-tolerant DB setup with multiple 
>>> read and write nodes with little to no configuration on my part, if 
>>> possible. I am looking for a self-hosted open source solution.
>>> 
>>> Is this possible with PG? What is the best way to achieve this for a 
>>> non-DBA solo developer like me?
>> 
>> robjsarg...@gmail.com wrote:
>> 
>> None of the experts chimed in so I ante up my $0.02. It won't be possible 
>> unless you become a serious DBA _and_ solo (full stack) developer.  Or you 
>> pay for db support.
> 
> saul.perd...@gmail.com wrote:
> 
> What Rob said... plus, I would urge you to give some more thought to "for 
> audit purposes the data is append only". If your application is ever 
> successful, non-insignificant storage costs are something you'll need to deal 
> with sooner or later.
> 
> Anyway, what you ask is certainly achievable, but not without sustained 
> effort. IMO your options are: spend the time to learn on your own with much 
> reading + trial and error; pay somebody to set it up for you; or, cross the 
> high-availability bridge after you've got something of substance developed, 
> app-wise, on a single local DB.

There’s always Internet search. Mention as many key phrases as you see fit. For 
example:

> Fully managed cloud service for highly available, fault tolerant, 
> Postgres-compatible distributed SQL database

Look at my email address. That outfit is among the hits. So full disclosure is 
done. But, as they say, “other services are available”. So I don’t think that 
my suggested search string is inappropriate for a list like this.

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

[Bryn Llewellyn]

> 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 on pretty arcane knowledge that, as far as I can see, isn't 
>> documented.
> 
> The last paragraph of the privileges documentation says this explicitly:
> 
> If the “Access privileges” column is empty for a given object, it means the 
> object has default privileges (that is, its privileges entry in the relevant 
> system catalog is null). Default privileges always include all privileges for 
> the owner, and can include some privileges for PUBLIC depending on the object 
> type, as explained above.
> 
> https://www.postgresql.org/docs/current/ddl-priv.html
> 
> Or, you know, just use the provided functions that have been programmed with 
> knowledge of how the system works.
> 
> https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-INFO-ACCESS-TABLE
> 
> select has_database_privilege(0,current_database(),'CONNECT');

Ah yes, thanks. I’d missed that at the bottom of the page. I find the "aclitem" 
base type a bit confusing. I understand that, as a base type, its structure is 
hidden. However, its text typecast, exemplified by this:

calvin=r*w/hobbes

is conventional and rather informally doc'd. For example, what is a field? You 
have to guess.

There's no mention on the "Privileges" page of the "has_database_privilege()" 
function. Nor of "aclexplode()".

Even now, I haven't managed a linear start to finish read of the entire PG 
docs. And I found "has_database_privilege()" and "aclexplode()" by Internet 
search rather than x-refs within the PG doc.

The account of "has_database_privilege()" has this:

has_database_privilege ( [ user name or oid, ] database text or oid, privilege 
text ) → boolean

but that's the only mention of the function on the "System Information 
Functions and Operators" page. So nothing says what it means to use the (text, 
text) or (oid, text) overloads.

Moreover, nothing says that "0" denotes "public". (Nor does anything that I've 
found say that it's the same for "0" in the first field of what "aclexplode()" 
produces for each element of its "aclitem[]" argumemt. Internet search for 
"postgres oid of public" gets no useful hits.

But experiment shows that you can use this reserved name (in single quotes) 
with the same effect as "0".

I suppose that it all boils down to this:

…where
  has_database_privilege('public', datname, 'connect') or
  has_database_privilege('public', datname, 'create') or
  has_database_privilege('public', datname, 'temp');

versus this:

…where 0::oid = any(select (aclexplode(datacl)).grantee) or datacl is null;

Yes, I prefer the version that uses "has_database_privilege()" (even though 
it's longer) because it says more clearly what it means.

Thanks!

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

[Bryn Llewellyn]

> 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   
> 
> template1
> template0
> regression

Thanks, both, for the lightning-fast replies. Yes, I see it now. (I got myself 
confused about the requirements for using parentheses.) I should have slept on 
it before sending to the list.

There's still a little snag though. I created a brand-new cluster (with 
bootstrap superuser called "postgres"), started a session as "postgres", and 
did this:

create database d1;
revoke all on database d1 from postgres;
revoke all on database d1 from public;

create database d2;
revoke all on database d2 from postgres;

create database d3;

select
  datname::text   as name,
  case
when datacl is null then ''
else datacl::text
  end as datacl,
  (0::oid = any(select (aclexplode(datacl)).grantee))::text   as "public has a 
priv"
from pg_database
where datname in ('d1', 'd2', 'd3')
order by 1;

It produced this result:

 name | datacl | public has a priv 
--++---
 d1   | {} | false
 d2   | {=Tc/postgres} | true
 d3   |  | false

This seems to imply that this wording from "5.7. Privileges" 
(https://www.postgresql.org/docs/current/ddl-priv.html) is a little sketchy:

«
For other types of objects, the default privileges granted to PUBLIC are as 
follows: CONNECT and TEMPORARY (create temporary tables) privileges for 
databases…
»

The effect of  a NULL "datacl" is as if CONNECT and TEMPORARY have been granted 
to public. But even so, these privileges are not shown to have been actually 
granted.

In my test, I simply revoked "all" on "d2" from postgres. And this produced a 
not null "datacl" that did then show the documented default regime.

The following test:

create role r with login password 'p';
\c d1 r
\c d2 r
\c d3 r

Showed that "public has a priv" (as I coded it) doesn't tell the whole story 
because "\c d3 r" (as well as "\c d2 r") succeeds. Of course, "\c d1 r" fails.

I do see that, in a strict "legal sense", the doc that I quoted is not (quite) 
wrong. But to implement the test that I want robustly, I need to extend the 
logic thus:

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 on pretty arcane knowledge that, as far as I can see, isn't 
documented.

Anyway, my immediate requirement is solved. Thanks again!

Test if a database has any privilege granted to public

[Bryn Llewellyn]

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 
implemented the function. And preliminary tests haven't shown that it doesn't 
work.

I created two databases like this, using a session that I authorized as a 
non-bootstrap superuser called "yugabyte":

create database d1;
create database d2;

grant  all on database d1 to yugabyte;
revoke all on database d2 from yugabyte;

grant  all on database d1 to public;
revoke all on database d2 from public;

And I tested the function like this:

select
  datname,
  mgr.db_has_priv_granted_to_public(datname)::text as "bad?"
from pg_database
where datname in ('d1'::name, 'd2'::name)
order by 1;

It produced this result:

 datname | bad?  
-+---
 d1  | true
 d2  | false

Here's the function's implementation:

create type mgr.acl_t as (grantor oid, grantee oid, privilege_type text, 
is_grantable boolean);

create function mgr.db_has_priv_granted_to_public(db in name)
  returns boolean
  set search_path = pg_catalog, pg_temp
  language sql
as $body$
  with
c1 as
  (
select (aclexplode(datacl)::text)::mgr.acl_t as v1 from pg_database 
where datname = db
  ),
c2 as
  (
select (c1.v1).grantee as grantee from c1
  )
  select exists(select 1 from c2 where grantee = 0::oid);
$body$;

The design of the user-defined type was inspired by "\df aclexplode". And the 
typecast to "text" and thence to my "acl_t" works around the error "cannot cast 
type record to acl_t". I want only to access the "grantee" field of the 
"aclitem" value.

My code feels very obscure and verbose. Having said this, searching the PG doc, 
and doing general Internet searches didn't bring any inspiration. Rather, all 
that I found was this:

https://pgxn.org/dist/pg_acl/
The default ACL type in Postgres (aclitem) produces rather cryptic output that 
is hard to understand.

And indeed, "pg_database.datacl::text" produces output like this:

{=CTc/yugabyte,yugabyte=CTc/yugabyte}

Sure enough, the PG doc does say "An empty grantee field in an aclitem stands 
for PUBLIC." But it would be a pain to parse that text and deduce the presence 
of the empty field that I can see with my human eyes.

However, I don't want to mess around with a third party extension to meet a 
goal that is so simply stated.

Am I missing something? Is there a better way to implement my function?

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

[Bryn Llewellyn]

> 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 
>> the fact that, once the initial configuration of a cluster has been done 
>> immediately after its creation (which configuration is done while still in 
>> self-imposed single-user mode), there are then very few, and infrequent, 
>> tasks that require the power of the superuser role.
>> »
>> 
>> Nobody supports it!
> 
> ...why the "Nobody supports it!" statement for a recommendation that only 
> appeared at the same time? I for one have a poor record of mind reading 
> and/or predicting the future:)

Here’s what I wrote in the post that started this thread, archived at this URL:

https://www.postgresql.org/message-id/290ef7b8-d150-4ae1-8ffe-a38912cd1...@yugabyte.com

> The implication is clear: you should allow a cluster to have just a single 
> superuser, the inevitable bootstrap superuser, and you should think very 
> carefully indeed before ever starting a session as this role because of the 
> risks that doing so brings. Rather, you should realize that there are hardly 
> any tasks that cannot be carried out by an appropriately configured role with 
> "nosuperuser”.


The essential content of each (what I wrote in my opening post and what stands 
between « ... » above) is the same: allow maximum one superuser. Each is a 
strawman. And, as such, carries its own implicit invitation for challenge or 
support. The outcome was all challenge and no support. I don’t know why 
observing that this was the outcome has, itself, become controversial.

In fact, David Johnston did unequivocally challenge my strawman a couple of 
turns back, thus:

> no one is supposed to login to the database cluster using the postgres role. 
> Period. Upon initialization whomever is responsible for creating the cluster 
> gets their personal user credentials installed into the cluster as superuser 
> and from that point on never uses postgres.  


That’s actionable advice. I mentioned that I had implemented that scheme and 
then, later, abandoned it. I can easily re-implement it.

Because PG allows a cluster to have as many superusers as you please, and 
because any one of these can create or drop another, any convention in this 
space needs some extra mechanisms to enforce it..

I believe that the fact that a superuser's ability to start a session can be 
limited by what the "hba_file" says is critical here—together with the fact 
that the ability to edit this file is governed by the regime of O/S users and 
file privileges. Maybe this is the key to the effectively tamper-proof 
implementation of the scheme that David recommends. (Having said this, there's 
always the "set role" backdoor.)

There's also the caveat that a "drop" attempt by a superuser for a single 
object owned by the bootstrap superuser (say, the 
"pg_catalog.pg_terminate_backend()" function) in some database causes an error 
with the message "cannot drop function... because it is required by the 
database system". (At least, this is what my tests have shown with a smallish 
sample of drop targets.) This seems to be a Very Good Thing. But the fact that 
this is the behavior makes me wonder what harm can be done by a session that 
authorizes as the bootstrap superuser that cannot be done by a session that 
authorizes as a regular superuser. I'll try to find out.

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

[Bryn Llewellyn]

> 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 that, once the initial configuration of a cluster has been done 
>> immediately after its creation (which configuration is done while still in 
>> self-imposed single-user mode), there are then very few, and infrequent, 
>> tasks that require the power of the superuser role.
>> »
>> 
>> Nobody supports it!
> 
> I went back through the thread and don't anywhere when you made the above 
> statement, correct me if I am wrong. In that case there was nothing to 
> support or not support until now. What people where responding to the title 
> of the thread:
> 
>> "Seeking practice recommendation: is there ever a use case to have two or 
>> more superusers?"
> 
> That is a different ask.

I didn't mean to say that I'd already written the sentence that starts "It is 
good practice to limit the number of superuser roles that exist in a 
cluster...". Sorry if I gave that impression. It was just a strawman version of 
what I practice recommendation might look like that used the same general 
wording style as the one about "a role that has the CREATEDB and CREATEROLE".

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

[Bryn Llewellyn]

> 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 
 permission to reach my own conclusion.
>>> 
>>> Not sure why you think you need permission to take whatever action you 
>>> desire on a database whose only usage stipulation is that you maintain a 
>>> copy of the license.
>> 
>> Adrian, I think Bryn's speaking metaphorically there.
> 
> It is hard to tell with him. He makes much of his Oracle background and I 
> think misses an overlord that lays down the rules.

I didn’t mean to speak metaphorically. But I made a bad word choice when I used 
“permission”. A couple of turns back, David Johnston wrote this:

> there is no good blanket recommendation to give to someone else as to how 
> their [security] policy should be written.  Security, especially of this 
> sort, needs to be architected.

And some time ago, in a different thread, he wrote this:

> You only need superuser once to configure the system in such a way, through 
> role and grants and possibly default permissions, that from then on most 
> everything an application user would want to do can be done by the role(s) 
> you have created.

That second quote reads like a recommendation—which puts it at odds with the 
first quote. (But doubtless I’m reading it wrongly.)

Then there’s this (from the doc):

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


That, too, reads like a recommendation that intends to inform a security 
policy. But, I suppose, one could argue that saying something “is good 
practice” is very different from making a recommendation.

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 that, once the initial configuration of a cluster has been done 
immediately after its creation (which configuration is done while still in 
self-imposed single-user mode), there are then very few, and infrequent, tasks 
that require the power of the superuser role.
»

Nobody supports it!

I’m puzzled why the good practice statement about a role with the CREATEDB and 
CREATEROLE attributes earns a place in the doc while nobody at all is prepared 
to make a practice statement about how many superusers is good. I’d like very 
much to understand the critical parts that I’m missing of the essential mental 
model in this general space.

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

[Bryn Llewellyn]

> 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 major or deliterious impact).
> 
> Which is why you want to automate deployments. When the easiest way to fix 
> something in production is to just push the change into the repo, this is 
> what people will do. When doing it the "proper" way is much more complicated 
> than just fudging it, people will do the latter.

Thanks to all who offered their views on my question. It seems that different 
people will reach different conclusions. I’ll take this as permission to reach 
my own conclusion.

Software systems in general often offer some freedoms that most people think 
should never be exercise. For example, PG lets you define a domain that has a 
not null constraint on one, or more, of its data attributes. But the doc

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

tells you not to do this. (Look for « Best practice therefore is to design a 
domain's constraints so that a null value is allowed... ».) I was rather slow 
to grasp the risks that ignoring this advice brings.. But I get it now.)

At the other end of the spectrum, there’s generally accepted thinking on how to 
choose between a procedure or a function for a particular new use case. But 
there are still plenty of such cases where opinions will vary and where each 
preference can be defended with reasonable arguments.

I haven’t seen anything in the PG doc that warns against creating additional 
superusers—so I suppose that this fact tells me something. Nevertheless, I 
remain convinced about what I’d recommend here:

The default choice must be to allow only one superuser: the inevitable 
bootstrap superuser.

But I’ll concede that I should see this like many rules of recommended 
practice. Sometimes they get broken. This seems most often to happen  as an 
expedient because the recommended practice requires too much effort. The 
general second-order advice for such scenarios is to write down the rationale 
and to offer it up for peer review. And if there are no peers who care, then 
all bets are anyway off...

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

[Bryn Llewellyn]

> 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 "No!".
> 
> Is there ever a reason? Yes. Does that reason apply to you? I don't know. 
> Maybe, maybe not.
> 
>> I wondered, at first, if it might be a good practice to create a second 
>> superuser, say "super" with "login", to alter the bootstrap superuser with 
>> "nologin", and then to use "super" on an “ordinary" daily basis for tasks 
>> that might need this. The thought was that this practice might protect the 
>> artifacts that the bootstrap superuser owns from damage. But this thought 
>> dissolved into thin air, before it was fully formed, on the realization that 
>> the unstoppable "super” could anyway do arbitrary damage to the bootstrap 
>> superuser's artifacts.
> 
> You could create additional superusers and restrict those to certain 
> databases and/or IP ranges. That probably won't stop an attacker (I can think 
> of at least one way to get around that and it's probably even easier than I 
> think) but it might prevent accidental damage.
> 
>> The implication is clear: you should allow a cluster to have just a single 
>> superuser, the inevitable bootstrap superuser, and you should think very 
>> carefully indeed before ever starting a session as this role because of the 
>> risks that doing so brings. Rather, you should realize that there are hardly 
>> any tasks that cannot be carried out by an appropriately configured role 
>> with "nosuperuser".
> 
> One important task that can AFAIK only be performed by superusers is the 
> creation of functions in untrusted languages like plpython3u and plperlu. If 
> your application uses functions in those languages you need a superuser to 
> install or upgrade it.

Thanks, Peter. I experimented with the notion of restricting a superuser to 
certain databases. My set-up has this in the “hba_file”:

# TYPE  DATABASE  USERMETHOD  [auth-options]
#     --  --  
  local all   postgrespeer

So that gave me an easy way to test the notion. (This is a sandbox cluster in a 
VM. So I can do arbitrary destructive tests and recover in a heartbeat.) Before 
the test, this command at the O/S prompt (as the O/S user “postgres”):

psql -d d0 -U postgres

succeeded and took me where it specified. I changed the “hba_file” thus:

# TYPE  DATABASE  USERMETHOD  [auth-options]
#     --  --  
  local postgres  postgrespeer

It had the effect that you promised. This command at the O/S prompt (as the O/S 
user “postgres”):

psql -d postgres -U postgres

succeeded. But this attempt:

psql -d d0 -U postgres

now failed with this error:

...no pg_hba.conf entry for host "[local]", user "postgres", database "d0"...

as promised by the doc. But this seems to achieve very little. Having 
authorized using the first command, I then did "drop database d0". I had no 
reason to think that it might fail. And it didn't. I see that it's careless to 
say that a superuser is *unstoppable*. But it seems that, as long as a 
superuser can start as session at all, then it can certainly do untold harm.

The experiment, then, doesn't seem to change the thinking. In other words, only 
a highly trusted person should know the password(s) needed to start a superuser 
session. This might well be done is a "four eyes" partnership, as such tasks 
often are, to increase the level of trust and responsibility that the 
organization relies upon. Given this, I cannot see how it would help increase 
the trust given to such people to have more than one superuser to connect as.

I don't see that the point about maintenance changes for functions written in 
untrusted languages changes anything. You already have the inevitable bootstrap 
superuser and, if the organization chooses, robust "four eyes" practices to 
govern how people use sessions that authorize as this role. You don't need a 
second superuser just because this kind of task is to be done a long time after 
the initial cluster creation and configuration.

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

[Bryn Llewellyn]

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*

An earlier thread:

https://www.postgresql.org/message-id/8a2c8b1b-470b-42f9-9694-c1eddac06...@yugabyte.com

reached the interim conclusion that the term "bootstrap superuser" is the 
generally accepted term of art for the superuser whose name you specify to 
"initdb" with its -U option when you create a new cluster. I said "interim" 
because the current PG doc has no glossary entry for this notion. (Sometimes, 
the explicit use of "initdb" is hidden behind a wrapper like 
"pg_createcluster". You use this after installing PG with "apt install 
postgresql" on Ubuntu. This detail is of no consequence for my question.)

The bootstrap superuser is uniquely special in that, in each database, it owns, 
for example, the "pg_catalog" and "information_schema" schemas and the objects 
in them. In other words, this role owns the SQL component of the implementation 
of the PG RDBMS and is essential to allow it to function.

However, you can create as many additional superusers as you please—and each is 
as unstoppable as all the others. I wondered, at first, if it might be a good 
practice to create a second superuser, say "super" with "login", to alter the 
bootstrap superuser with "nologin", and then to use "super" on an "ordinary" 
daily basis for tasks that might need this. The thought was that this practice 
might protect the artifacts that the bootstrap superuser owns from damage. But 
this thought dissolved into thin air, before it was fully formed, on the 
realization that the unstoppable "super" could anyway do arbitrary damage to 
the bootstrap superuser's artifacts.

Moreover, it seems that there are, anyway, no (or exceedingly few) tasks that 
ever need the power of a superuser. At the very least, there seems to be no 
defensible notion of "daily superuser tasks".

I noticed this in a recent post to this list by Christophe Petus:

https://www.postgresql.org/message-id/23c450ab-9333-40cb-8ed1-f15d15bed...@thebuild.com

> The typical configuration is to not permit the PostgreSQL superuser to log in 
> remotely. The database can be managed by a different, non-superuser role, 
> including schema migrations.


The implication is clear: you should allow a cluster to have just a single 
superuser, the inevitable bootstrap superuser, and you should think very 
carefully indeed before ever starting a session as this role because of the 
risks that doing so brings. Rather, you should realize that there are hardly 
any tasks that cannot be carried out by an appropriately configured role with 
"nosuperuser".

David Johnston strengthened this notion with his turn in the same thread:

https://www.postgresql.org/message-id/CAKFQuwYvkohCz2o%3Dr7tEzpif0_DfaNzup5H-wJaR-1BJGOJz%3Dg%40mail.gmail.com

> You only need superuser once to configure the system in such a way, through 
> role and grants and possibly default permissions, that from then on most 
> everything an application user would want to do can be done by the role(s) 
> you have created.


This thinking is reflected further in the "22.2. Role Attributes" section in 
the PG doc:

https://www.postgresql.org/docs/current/role-attributes.html

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


*Yet more detail — only if you're interested*

I have a nicely working prototype that honors these principles. (I've mentioned 
it before.) It implements a "multitenancy by convention" scheme that avoids 
collisions of global names by following a naming convention that's enforced 
with "security definer" procedures for role provisioning.

Just as David described, I set up the scheme as a postlude to "initdb" by using 
a session authorized as the bootstrap superuser. This creates my "clstr$mgr" 
role with "createdb" and "createrole" and installs the role-provisioning 
procedures in a dedicated "mgr" schema in the customized "template1" database. 
(Both the "mgr" schema and the objects in it are owned by "clstr$mgr".) Once 
this set-up is done, no session needs ever agin to authorize as the bootstrap 
superuser—unless bug fixes or enhancements are needed to the implementation of 
the scheme itself. (But most of this patching could, anyway, be done by a 
session that authorizes as "clstr$mgr".) This patching would anyway require 
stopping all ordinary user sessions and doing the task in a self-imposed 
single-user mode. So it's most certainly natural to disallow authorizing a 
bootstrap superuser session remotely—just as Christophe said.

It's 

Re: "set role" semantics

[Bryn Llewellyn]

> 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 it if I had it. But, because I don’t, I 
will have to write a comment essay to explain what tests might show and why 
these outcomes that might seem worrisome at first sight can be seen, after an 
exercise of reasoning, to be harmless. I’m not a fan of that kind of essay 
writing. But I’ll do it if I have to.

>> Yes I have actually done this. But rigorous testing remains to be done... 
>> The point (conforming to the principle of least privilege) is that sessions 
>> that connect as "client" must not be allowed to do arbitrary SQL. Rather, 
>> they should be able to do only what has been explicitly "white-listed" in by 
>> the encapsulation provided by the API-defining subprograms.
> 
> All right that I get.

Good. I’m relieved that you haven’t (yet) spotted a flaw in my scheme.

Re: "set role" semantics

[Bryn Llewellyn]

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 role with which you connect to a database 
should be identical, always, to what "current_role" returns. I speculated only 
that an enhanced privilege scheme that limited the target of "set role" to 
those that have "connect" on the current database might be nice. I can't see 
that this would interfere with the "security" notion for a subprogram. After 
all, it's already possible for role "r1" to invoke a "security definer" 
subprogram owned by role "r2" when "r1" cannot "set role" to "r2". (This is 
probably the overwhelmingly common case.)

I believe that I do understand the business of these two "security" kinds for 
user-defined functions and procedures well. (And, yes, I know that a "set role" 
attempt in a "security definer" context causes a run-time error.) But thanks 
for mentioning the topic. There's a certain family resemblance between a 
"security definer" subprogram and "set role" in that each brings the outcome 
that the value that "current_role" returns might differ from the value that 
"session_user" returns. And you can certainly arrange it so that a "security 
definer" subprogram is owned by a role that does not have "connect" on the 
database where the subprogram exists. There is, though, a difference between 
the two paradigms in that the subprogram follows a stacked behavior so that 
when the subprogram that's first called exits, the "current_role" value is back 
where it was when the call was made. In contrast "set role" makes a durable 
change that you can see at the "psql" prompt (mentioning this as an example of 
any client). And you can use "set role" to roam around, on demand, among any 
number of roles in the set that allows you do do this in any order. This feels 
different—at least to me.

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.

>> 
>> You mentioned access to the catalog tables. This, too, belongs to the 
>> discussion of the principle of least privilege. This access is not hard 
>> wired. Rather, it's just a manifestation of the default regime. I've 
>> prototyped a regime where the privileges that you need to access these 
>> tables (and other things too) are revoked from public and (for convenience) 
>> are granted to a single dedicated role. This means that it's easy to make it 
>> such that the role(s) that clients use to connect can't query the 
>> catalog—or, if you prefer, can access exactly and only those catalog items 
>> that they need to. I'm pleased with how it worked out. And I'll pursue this 
>> regime further.
>> 
> 
> Have you actually done that and tried to run SQL statements? They are called 
> system catalogs because they are used by the system to get the information 
> necessary to do things. Throwing restrictions on their access would be akin 
> to pouring sand in a gearbox, lots of strange behavior and then nothing.

Yes I have actually done this. But rigorous testing remains to be done. I've 
implemented the scheme only within a disciplined bigger picture. I've mentioned 
the thinking that I'll sketch now, before, in other contexts. It's not 
original. Many real-world applications follow it. I like to refer to it as the 
"hard shell" paradigm. Here, the ownership of the various artifacts that 
implement an application's database backend is spread among as many roles as 
you please. For example, tables and their associated artifacts (like indexes, 
sequences, and so on) would have a different owner from the user-defined 
subprograms that implement the business functions that access the tables. 
Significantly, client-side access to this whole shooting match would be via one 
(or a few) dedicated "client" roles. Such a role has only "connect" on the 
database that houses the application's backend. And it owns no schema and no 
objects in other schemas. Rather, it's just the target for the "execute" 
privilege of those few of all the user-defined subprograms that jointly define 
the database's API. The point (conforming to the principle of least privilege) 
is that sessions that connect as "client" must not be allowed to do arbitrary 
SQL. Rather, they should be able to do only what has been explicitly 
"white-listed" in by the encapsulation provided by the API-defining subprograms.

The "lazy" approach for the roles that own the application's implementation and 
that rely on (some of) the artifacts that live in "pg_catalog" is simply to 
revoke "all" from "public" for every one of these catalog items and, in the 
same breath, to grant "all" (or what is needed) on each to a dedicated role 
(say, "d0$developer" in database "d0"). Then "d0"developer" is 

Re: "set role" semantics

[Bryn Llewellyn]

> 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 that, as far as I've taken this test, "d2$mgr" is entirely impotent 
>> when the "current_database()" is "d1". Is this, maybe, just as good as it 
>> gets. I suppose I can live with what seems to me to be very odd as long as 
>> no harm can come of it.
> 
> Yes. In short, you can prevent a person from connecting to specific databases 
> by ensuring the credentials they hold only resolve successfully on those 
> specific databases.  Both via connect privilege and pg_hba.conf can this be 
> done. But that person, while connected to any databases, can assume any roles 
> the user name of the credentials they used are a member of.  Whether that is 
> actually useful or not depends upon grants.  But in no case can you prevent 
> them from, say, examining the database catalog tables, or executing at least 
> some limited SQL.

Thanks. If nobody thinks that ending up as I showed is possible brings any kind 
of risk, then I’m happy to accept that. More generally, I’m a huge fan of the 
principle of least privilege, and (as far as it concerns what I asked about in 
this thread), its following aspect:

If you consider these two alternatives:

Alt. #1: Some ability that you do not need (and therefor don’t want) is simply 
not available.

Alt. #2: That unwanted ability is available, but reasoning shows that it’s 
harmless.

then Alt. #1 is preferable.

But I do see that I can’t get Alt #1 here.

In my actual use case, every role apart from "postgres", and its non-superuser 
deputy with "create role" and "createdb", will be confined at "connect time" to 
exactly one database. And those of these that will be able to "set role" will 
be able to do this only to other roles that are also confined (at "connect" 
time) to the same database. Moreover, I cannot worry about what sessions that 
authorize as "postgres" or its deputy can do because the former is unstoppable" 
and the latter is dangerously powerful in the wrong human hands. There's always 
a need for trusted humans who, if they betray that trust, can do untold harm. 
In this case, they must understand the design of the "multitenancy by 
convention" scheme and must be committed to honoring it. So, sure enough, 
reasoning tells me that my plan is sound.

Nevertheless, it does seem to be unfortunate to take the mechanics of "connect" 
as salient rather than the resulting state of the session that either "connect" 
or "set role" can bring about. There could be (in a future PG version) a 
privilege that governed "set role" in the present scenario. But I'm sure that 
there never will be.


You mentioned access to the catalog tables. This, too, belongs to the 
discussion of the principle of least privilege. This access is not hard wired. 
Rather, it's just a manifestation of the default regime. I've prototyped a 
regime where the privileges that you need to access these tables (and other 
things too) are revoked from public and (for convenience) are granted to a 
single dedicated role. This means that it's easy to make it such that the 
role(s) that clients use to connect can't query the catalog—or, if you prefer, 
can access exactly and only those catalog items that they need to. I'm pleased 
with how it worked out. And I'll pursue this regime further.

Re: "set role" semantics

[Bryn Llewellyn]

> adrian.kla...@aklaver.com wrote:
> 
>> david.g.johns...@gmail.com wrote:
>> 
>>> b...@yugabyte.com wrote:
>>> 
>>> Notice that I didn't grant "connect" on either of the databases, "d1" or 
>>> "d2", to any 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 been explained before to you (Bryn Llewellyn).
> 
> A quick search:
> 
> https://www.postgresql.org/message-id/2176817.1644613...@sss.pgh.pa.us
> 
> https://www.postgresql.org/message-id/cakfquwayij%3daqrqxjhfuj3qejq3e-pfibjj9cohx_l_46be...@mail.gmail.com
> 
> https://www.postgresql.org/message-id/cakfquwzvq-lergmtn0e3_7mqhjwtujuzf0gsnkg32mh_qf2...@mail.gmail.com

Here's an extract from the script that I copied in my first email:

create database d1;
revoke all on database d1 from public;

create database d2;
revoke all on database d2 from public;

Didn't I do exactly what you both said that I failed to do?

*Summary*

My experiments (especially below) show that "set role" has special semantics 
that differ from starting a session from cold:

"set role" allows a role that lacks "connect" on some database to end up so 
that the "current_database()" shows that forbidden database.

My question still stands: where can I read the account of this? I'm also 
interested to know _why_ it was decided not to test for the "connect" privilege 
when "set role" is used.

*Detail*

I suppose that the script that I first showed you conflated too many separable 
notions. (My aim was to you show what my overall aim was). Here's a drastically 
cut down version. It still demonstrates the behavior that I asked about.

create role joe
  nosuperuser
  nocreaterole
  nocreatedb
  noreplication
  nobypassrls
  connection limit -1
  login password 'p';

create database d1;
revoke all on database d1 from public;

\c d1 postgres

set role joe;
select current_database()||' > '||session_user||' > '||current_user;

I'm still able to end up with "Joe" as the "current_user" and "d1" (to which 
Joe cannot connect) as the "current_database()".

I then did the sanity test that I should have shown you at the outset. (Sorry 
that I didn't do that.) I started a session from cold, running "psql" on a 
client machine where the server machine is called "u" (for Ubuntu) in my 
"/etc/hosts", thus:

psql -h u -p 5432 -d d1 -U joe

The connect attempt was rejected with the error that I expected: "User does not 
have CONNECT privilege".

I wondered if the fact that the "session_user" was "postgres" in my tests was 
significant. So I did a new test. (As ever, I started with a freshly created 
cluster to be sure that no earlier tests had left a trace.)

create role mary
  nosuperuser
  noinherit
  nocreaterole
  nocreatedb
  noreplication
  nobypassrls
  connection limit -1
  login password 'p';

create role joe
  nosuperuser
  noinherit
  nocreaterole
  nocreatedb
  noreplication
  nobypassrls
  connection limit -1
  login password 'p';

create database d1;
revoke all on database d1 from public;
grant connect on database d1 to mary;
grant joe to mary;

Then I did this on the client machine:

psql -h u -p 5432 -d d1 -U mary
set role joe;

Here, too, I ended up with "Joe" as the "current_user" and "d1" (to which Joe 
cannot connect) as the "current_database()".

"set role" semantics

[Bryn Llewellyn]

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 system admin O/S user 
for my VM):

--

source pg-init.sh
sudo -u postgres psql

This is the SQL script:

create role clstr$mgr with
create role clstr$mgr with
  nosuperuser
  createrole
  createdb
  noreplication
  nobypassrls
  connection limit 0
  login password null;

set role clstr$mgr;

create role d1$mgr
  nosuperuser
  nocreaterole
  nocreatedb
  noreplication
  nobypassrls
  connection limit 0
  login password null;

create role d2$mgr
  nosuperuser
  nocreaterole
  nocreatedb
  noreplication
  nobypassrls
  connection limit 0
  login password null;

create database d1;
revoke all on database d1 from public;

create database d2;
revoke all on database d2 from public;

\c d1 postgres

set role clstr$mgr;
grant create on database d1 to d1$mgr;
create schema s;
grant usage  on schema s to d1$mgr;
grant create on schema s to d1$mgr;

set role d1$mgr;
select current_database()||' > '||session_user||' > '||current_user;
create table s.t(k int);
insert into s.t(k) values(17);
select * from s.t;

set role d2$mgr;
select current_database()||' > '||session_user||' > '||current_user;

-- permission denied...
select * from s.t;

--

Notice that I didn't grant "connect" on either of the databases, "d1" or "d2", 
to any of the roles, "clstr$mgr, "d1$mgr", or "d2$mgr".

I couldn't find the doc that tells me what to expect. Where is it? I was a bit 
surprised that I could end up with the "current_database()" as "d1" or "d2" and 
the "current_user" as "clstr$mgr" when this role doesn't have "connect" on 
either of the databases. But I guessed that permission to do this was implied 
by the "createdb" attribute (as a special case of the general unstoppability of 
a superuser). However, I was very surprised that I could end up with the 
"current_database()" as "d1" or "d2" and the "current_user" as "d2$mgr" or 
because it is so far minimally privileged (and in particular doesn't have 
"connect" on "d1" or "d2").

I'd been hoping that "set role d2$mgr" would fail when "d2$mgr" doesn't have 
"connect" on the target database, "d1". My plan, then, had been to set up 
"d1$mgr" as the manager for "d1"  by granting it "connect on "d1" but not on 
"d2". Then I'd've used a similar scheme for "d2$mgr".

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 that, as far as I've taken this test, "d2$mgr" is entirely impotent 
when the "current_database()" is "d1". Is this, maybe, just as good as it gets. 
I suppose I can live with what seems to me to be very odd as long as no harm 
can come of it.

--
# pg-init.sh

sudo pg_dropcluster --stop 11 main
sudo rm -Rf /etc/ybmt-generated/pg-logs/*

sudo pg_createcluster 11 main \
  -e UTF8 --locale=C --lc-collate=C --lc-ctype=en_US.UTF-8 \
  -d /var/lib/postgresql/11/main \
  > /dev/null

sudo cp /etc/ybmt-code/pg-and-yb-config-files/*.conf \
/etc/postgresql/11/main

sudo chown postgres /etc/postgresql/11/main/*.conf
sudo chgrp postgres /etc/postgresql/11/main/*.conf
sudo chmod 644  /etc/postgresql/11/main/*.conf
sudo chmod 640  /etc/postgresql/11/main/pg_hba.conf
sudo chmod 640  /etc/postgresql/11/main/pg_ident.conf

sudo pg_ctlcluster start 11/main

sudo -u postgres psql -c " alter role postgres with superuser connection limit 
-1 login password 'x'; alter database postgres with allow_connections = true 
connection_limit = -1; "

sudo -u postgres psql -c " select name, setting from pg_settings where category 
= 'File Locations'; "
。

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"

[Bryn Llewellyn]

> 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 and in the Debian/Ubuntu packaging the better 
> option for that is pg_ctlcluster. I generally use the systemd scripts to 
> start/stop Postgres instances, though when I do pg_lsclusters I tend to fall 
> into using pg_ctlcluster as the cluster info is right there.


Can't parse this. Sorry.

>> Notice that I'm still using the off-limits "initdb" here. (I wired it up 
>> with a link on "/usr/bin".) Is there any reason to change that and to try to 
>> work out how to use what David (in an email soon after Adrian's) suggested?
> 
> Yes and since that is basically coloring outside the lines, then that leads 
> to ... blowing up ... Per the saying, "In a ham and eggs breakfast the 
> chicken is involved but the pig is committed", right now you are involved in 
> the Debian/Ubuntu process but not committed. Until you commit you will not 
> get the results you want.

Fair enough. I started again from the state where my "config_file", my 
"hba_file", and my "ident_file" are all customized as I want them to be but 
where I hadn't yet tried to trash my cluster and re-create it. Then I tried 
with "pg_dropcluster --stop" and "pg_createcluster". I discovered immediately 
that this approach (in contrast to the "initdb" approach) blows away all the 
*.conf files and recreates them in canonical form—just as the "man" page says. 
This is a bit of a nuisance. But it's negotiable. I installed my files as I 
want them in a safe place, outside of the entire PG world, and then used this 
script:

sudo pg_dropcluster --stop 11 main

sudo pg_createcluster 11 main \
  -e UTF8 --locale=C --lc-collate=C --lc-ctype=en_US.UTF-8 \
  -d /var/lib/postgresql/11/main \
  > /dev/null

sudo cp /etc/ybmt-code/pg-and-yb-config-files/*.conf \
/etc/postgresql/11/main

sudo chown postgres /etc/postgresql/11/main/*.conf
sudo chgrp postgres /etc/postgresql/11/main/*.conf
sudo chmod 644  /etc/postgresql/11/main/*.conf
sudo chmod 640  /etc/postgresql/11/main/pg_hba.conf
sudo chmod 640  /etc/postgresql/11/main/pg_ident.conf

sudo pg_ctlcluster start 11/main

sudo -u postgres psql -c " select name, setting from pg_settings where category 
= 'File Locations'; "

sudo -u postgres psql -f 
/etc/ybmt-code/cluster-mgmt/01-initialize-brand-new-YB-or-PG-clstr/00.sql

It worked without error and had the intended effect. My old approach with the 
uncommitted chicken used to take ~3 sec. This new approach takes ~5 sec. The 
difference is completely unimportant.

For various reasons, I need the non-standard "--lc-collate=C" choice. I could 
doubtless leave all the other options unspecified. But that's the style 
discussion we had the other day—and I prefer, here, to self-doc my choices.

I discovered that using, say, the "initdb" approach, then the "kosher" 
approach, and then the "initdb" approach brought all sorts of errors. That's 
the beauty of using a VM and file backups (or snapshots). I suppose this is to 
be expected.

Can I declare victory, now, with the approach that I showed above?

I'm impatient to get back to my real project.

p.s. Is my pessimism justified—that there simply exists no plain English user 
guide for this whole Debian/Ubuntu apparatus—correct. Or is it, rather, that my 
search skills are just too feeble?

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"

[Bryn Llewellyn]

> 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 if you use third-party packaging 
> to install PostgreSQL (which most people should) that the documentation for 
> the packaging should describe this layer where PostgreSQL and the operating 
> system intersect.  You even quoted it: "follow the instructions for the 
> specific platform.", though reading that now I think something along the 
> lines of:
> 
>  "Additionally, while reading the next chapter, Server Setup and Operation, 
> is recommended if you are using a binary package the setup and operational 
> environment it creates is likely to be somewhat different than what is 
> described in this documentation.  Please read the documentation for the 
> packages you install to learn how it behaves and what additional 
> platform-specific features it provides."
> 
> Actually, not sure on the best approach here, since the Server Setup chapter 
> already says:
> 
> https://www.postgresql.org/docs/current/runtime.html
> 
> "The directions in this chapter assume that you are working with plain 
> PostgreSQL without any additional infrastructure, for example a copy that you 
> built from source according to the directions in the preceding chapters. If 
> you are working with a pre-packaged or vendor-supplied version of PostgreSQL, 
> it is likely that the packager has made special provisions for installing and 
> starting the database server according to your system's conventions. Consult 
> the package-level documentation for details."
> 
> However, that appears below-the-fold after a decent sized table of contents.
> 
> Changing anything now feels like an over-reaction to a single incident, but I 
> sympathize with the general confusion all this causes, and the fact it is 
> only in the recent past that we've made this first attempt to rectify the 
> situation by adding these comments.  A second-pass based upon this encounter 
> seems at least reasonable.  Whether I or others end up deciding it is worth 
> proposing a patch remains to be seen.

Thanks for your explanations, David. I believe that my point about how all this 
seems to me is well taken. I might concede that the Debian/Ubuntu packaging 
provides adequate reference doc by implementing its "man" pages. But I haven't 
found anything like a user guide that explains *why* ordinarily documented PG 
features have been hidden from sight (but not removed) and how (if the 
Debian/Ubuntu alternatives are just wrappers for the native PG) one might do 
that wrapping by hand. Doing this would demonstrate what benefits the wrapping 
brings.

Anyway, I now have a working PG system and useful notes. When, presently, I 
make a second VM for PG 15 (I prefer separate VMs over having both versions in 
the same VM) it should all go quickly and smoothly.

I have no reason to describe to anybody else how to install and configure 
PG—and I certainly won't do this.

My interest in being able to re-establish the pristine cluster starting state 
reliably and quickly is to support my own productivity. I'll presently have SQL 
scripts that establish the "multitenancy by self-imposed discipline" scheme 
that I've referred to from any arbitrary state of population of a cluster. I 
don't intend my scheme to co-exist with other schemes. And I don't expect there 
to be any real use cases for starting with an arbitrarily populated cluster and 
taking it to a state that conforms with my scheme. Rather, all this is about 
demonstrating how to establish the scheme on the assumption (but not 
requirement) that one starts with a brand-new cluster that will be dedicated to 
the approach that I've sketched.

I'm looking forward to returning to that project and putting all that we've 
been discussing here behind me.

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"

[Bryn Llewellyn]

> 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 things as "root". 
>> That's why I enabled "sudo" for "postgres" (just as the code examples on 
>> that page imply).
> 
> You don't need to do that. Just use sudo as what ever user you log in as. For 
> example:
> 
> aklaver@arkansas:~$ sudo pg_ctlcluster stop 14/main
> [sudo] password for aklaver:
> 
> Again very simple explanation, the OS postgres user is just created to run 
> the server. It does not even have a home directory.

I should make it clear that my VM has four "ordinary" users. Two are present on 
first use when VM creation finishes: the system administrator (called 
"parallels") and, of course, "root". The "parallels" user is enabled for 
"sudo". Installing PG brings "postgres". (I created mine before hand, and it 
was unperturbed by the PG installation. This is my private laptop. And it suits 
me to give it a home directory and to customize its ".bashrc". But that's so 
that I can look around with the minimum of fuss.) Finally, there's my 
"clstr_mgr" O/S user that acts as the authorization vehicle for my "clstr$mgr" 
cluster-role. Each of "postgres" and "clstr_mgr" is in its own singleton 
group—and in no other groups. And neither is set up for "sudo".)

The doc explains how to edit (at least) these: the "config_file", the 
"hba_file", and the "ident_file". You edit them to achieve certain supported 
outcomes. I mentioned that, during my "for real" installation (from a 
re-established freshly created Ubuntu VM) I noted how the config files were set 
up:

-rw-r--r-- 1 postgres postgres   143 Nov  1 15:48 
/etc/postgresql/11/main/pg_ctl.conf
-rw-r- 1 postgres postgres  4686 Nov  1 15:48 
/etc/postgresql/11/main/pg_hba.conf
-rw-r- 1 postgres postgres  1636 Nov  1 15:48 
/etc/postgresql/11/main/pg_ident.conf
-rw-r--r-- 1 postgres postgres 24321 Nov  1 15:48 
/etc/postgresql/11/main/postgresql.conf
-rw-r--r-- 1 postgres postgres   317 Nov  1 15:48 
/etc/postgresql/11/main/start.conf

So only "postgres" can edit the files that must be so edited.

Apparently, an unwritten rule says that one must never end up so that "whoami" 
shows "postgres". I see that I can, then, always do, for example, this (from 
"parallels"):

sudo -u postgres vi pg_hba.conf

And, given that one major theme in our recent mutual, extended, exchanges is 
that I want to use "local", "peer" authentication for the cluster-role 
"postgres" via the O/S user with the same name, I see that I can always run all 
the SQL scripts that I want, using this authentication, like this:

sudo -u postgres psql -f my_script.sql

With this in mind, I re-wrote my "clean start" script thus:

#!/bin/bash

# do this as ANY user that can do "sudo" (and this includes "root" itself)

sudo pg_ctlcluster stop 11/main
sudo rm -Rf /var/lib/postgresql/11/main

sudo -u postgres initdb \
  -U postgres --encoding UTF8 --locale=C --lc-collate=C --lc-ctype=en_US.UTF-8 \
  -A md5 --pwfile=my_password_file \
  -D /var/lib/postgresql/11/main

sudo pg_ctlcluster start 11/main

sudo -u postgres psql -f my_script.sql

It certainly works. And when it's finished, this:

sudo -u postgres psql -c " select name, setting from pg_settings where category 
= 'File Locations'; "

shows that my files are where they ought to be. Then, further tests show that 
"local", "peer" authorization works as intended for my "clstr$mgr" role and 
that I can connect from client machines. So all is good. 

Why, though, is "pg_ctlcluster stop/start... " preferred over "systemctl 
stop/start postgresql?". Both are quick enough. And I can't see any difference 
in effect. Moreover, the latter is what 
https://ubuntu.com/server/docs/databases-postgresql 
 recommends.

Notice that I'm still using the off-limits "initdb" here. (I wired it up with a 
link on "/usr/bin".) Is there any reason to change that and to try to work out 
how to use what David (in an email soon after Adrian's) suggested?

sudo pg_dropcluster --stop 11 main
sudo pg_createcluster 11 main
sudo pg_ctlcluster start 11/main

I tried it. But it "did not work". I don't have the energy to describe the 
errors that were reported and the nasty outcomes that I got when I tried to use 
the new cluster. There's no useful doc for that approach and I've already 
established that Internet search gets me nowhere. So I'm inclined not to use it.

Rather, I want, now, simply to declare victory with the script that I showed 
and return to ordinary productive work.

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"

[Bryn Llewellyn]

> 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 system.
> 
> As for the rest, all I see is that you are using an opinionated package 
> manager to install software whose opinions you don't agree with. Maybe there 
> is some buggy behavior with respect to shared o/s db administration among 
> users in a common group... you haven't demonstrated that one way or the other 
> here. I think it is pointless to have the o/s admin and postgres bootstrap 
> user be anything but postgres and this whole thing is counter-productive. But 
> if you are going down to first principles maybe you should install from 
> source and build your own "package" from that.

> david.g.johns...@gmail.com LATER wrote:
> 
> I think the intent of the design is for the custom Debian wrapper scripts to 
> be able to read the configuration files for the named version "11" and 
> configuration "main" to find out where certain things like the socket file 
> are being written to. The argument being the configuration files don't 
> actually contain secret data so reading shouldn't be an issue and can be 
> useful. Obviously the same does not apply to data files. On that basis it 
> would indeed make more sense to grant read to "all" rather than try and add 
> users to "postgres" to make the reading of the configuration files work.
> 
> Also, per the initdb documentation:
> 
> For security reasons the new cluster created by initdb
> will only be accessible by the cluster user by default.  The
> --allow-group-access option allows any user in the same
> group as the cluster owner to read files in the cluster.  This is useful
> for performing backups as a non-privileged user.

A strange mutual misunderstanding has arisen here. I suppose that it must be my 
fault. I have no interest whatsoever in "going down to first principles". And I 
most certainly never said that I want to "have the o/s admin and postgres 
bootstrap user be anything but postgres". On the contrary: I want just that. 
Saying this more abstractly, I want to install PG (admittedly the old version 
11) in a freshly created Ubuntu 20.04 LTS VM. And I want to follow the reigning 
notions of proper practice. As far as possible, I'd like to find that I simply 
get such an outcome without explicit intervention—or at least by accepting all 
the defaults.

Searching the actual PG doc took me here:

Chapter 17. Installation from Source Code
https://www.postgresql.org/docs/15/installation.html

That's the last thing I want to do. So then I read this:

Chapter 16. Installation from Binaries
https://www.postgresql.org/docs/15/install-binaries.html

(It was ranked lower by the doc's native search.) It says nothing of substance. 
But it does say this:

«
visit the download section on the PostgreSQL website at
https://www.postgresql.org/download/ 
and follow the instructions for the specific platform.
»

I did exactly that. And I selected "Linux" and under that "Ubuntu". Notice that 
I did NOT select "Debian", though it was on offer, because that's not what I 
have. If Ubuntu and Debian were effectively the same, then there wouldn't be 
two distinct choices. My choice took me here:

Linux downloads (Ubuntu)
https://www.postgresql.org/download/linux/ubuntu/

It mentions that my (22.04, LTS) is supported. Then I did these simple steps:

sudo -s
apt install postgresql-common
/usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

apt update
apt upgrade
apt install postgresql-11

I did have to look around a bit to find that recipe. But it completed quickly, 
without error, and without prompting me to make a single choice.

Now that I know what I do (and I confess that I did not know enough at the 
start) I could complete the whole thing in less than 30 minutes. (The time 
would be more or less according to what notes I decided to take along the way 
and what copy-and-paste-ready config file snippets and the like I had to hand.) 
I'm including, in this timing, the necessary post install steps to allow 
connections from other machines and to enable "local", "peer" authorization for 
my "superuser's assistant" that I implement with the cluster-role that I name 
"clstr$mgr".

I did the whole thing from scratch after trashing my provisional attempt. (This 
is easily afforded when you use a VM. Am I the only person who does this: 
practice, make mistakes, learn, trash, and then do it for real?)

I discovered this time around that the config files "arrive" like this:

-rw-r--r-- 1 postgres postgres   143 Nov  1 15:48 
/etc/postgresql/11/main/pg_ctl.conf
-rw-r- 1 postgres postgres  4686 Nov  1 15:48 
/etc/postgresql/11/main/pg_hba.conf
-rw-r- 1 postgres postgres  1636 Nov  1 15:48 

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"

[Bryn Llewellyn]

> 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 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 system.
> 
> As for the rest, all I see is that you are using an opinionated package 
> manager to install software whose opinions you don't agree with.  Maybe there 
> is some buggy behavior with respect to shared o/s db administration among 
> users in a common group...you haven't demonstrated that one way or the other 
> here.  I think it is pointless to have the o/s admin and postgres bootstrap 
> user be anything but postgres and this whole thing is counter-productive.  
> But if you are going down to first principles maybe you should install from 
> source and build your own "package" from that.

I meant only to ask a question—and not to pre-judge anything. I should have 
thought more carefully about its wording. (I'm thinking specifically of a PG 
installation on Linux—and only that.)

About "opinionated package manager", I created my installation by following the 
steps described here:

https://wiki.postgresql.org/wiki/Apt

My aim is simply to conform to recommended practice. When I've said (like I 
believe that I have) that I didn't get a chance, during the flow, to specify 
, my intention was only to note this fact and to imply that what 
I got without any intervention coincided with the recommended practice. This 
suits me.

I'm going to need to use a term to denote the O/S user that very often ends up 
with the name "postgres". I don't know what the official term is. I'll simply 
say "postgres" here. I haven't yet found an overview of the set of files that 
jointly implement a freshly installed PG system. But, of course, I've noticed 
where critical directories are. For example:

/usr/lib/postgresql/11/bin

Lots of familiar names, like "initdb" are found there. They all have 
owner/group "root". And they're all executable by "all". The point has been 
made that its not who owns them that matters but, rather, what the effective 
user ID is at execution time. I can therefore ignore these for the purpose of 
my present question.

In contrast, on (my) "data_directory" (I have just one) here:

/var/lib/postgresql/11/main

I see that every file has owner/group "postgres/postgres". The "owner" has all 
privileges. And each of "group" and "all" have no privileges. This doesn't seem 
at all surprising. But it does indicate that whatever privileges some critical 
file has (anywhere), the set has been determined by design.

There are several "*.conf" files on the same directory as the "config_file ". 
For me, it's this:

/etc/postgresql/11/main

These, too, all have owner/group "postgres/postgres" — and "postgres" has all 
meaningful privileges (presumably "execute" means nothing for a text config 
file). But "group” has only "read" on them all and "all" has no privileges 
except (I believe) for “postgresql.conf"—which is readable by all. 

I mentioned that I'll presently re-do my PG installation from scratch. Then 
I'll record the permissions that all the config files are set up with and know 
for sure. I looked in this section:

20.2. File Locations
https://www.postgresql.org/docs/current/runtime-config-file-locations.html 


But "permission" isn't found on the page.

Anyway, it's only natural to assume that privileges on all of the 
"postgres/postgres" files have been determined in accordance with a deliberate 
design. The docs tell me to edit certain of these files to achieve certain 
intended effects. I've done this—by authorizing as the "postgres" O/S user. So 
there's an inevitable risk (at least for me) that I might have changed some 
permissions accidentally.

I've observed that, for my present use case (enable "local", "peer" 
authentication for an O/S user other than "postgres), the authorization attempt 
fails unless that user can read one critical config file. I just did a careful 
test with the brand-new O/S user "bob". Here's what "id" shows:

id=1003(bob) gid=1003(bob) groups=1003(bob)
uid=1001(postgres) gid=1001(postgres) 
groups=1001(postgres),27(sudo),114(ssl-cert)


I know that I've been told off for allowing "sudo" for "postgres". I'm only 
experimenting on my laptop. But I want to be able to stop the server, delete 
the datafiles, create a new cluster, and then start that using a single script. 
I can't use "pg_ctl stop/start" because it expects to find its config files on 
the data directory. (That's a different story. And I'm not ready to ask about 
that yet.) So I use "sudo systemctl stop/start postgresql" because 

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"

[Bryn Llewellyn]

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 Googling got me to this on the pgsql-general list (from the Peter, in 
fact):

https://www.postgresql.org/message-id/20190909171519.GA7858%40hjp.at 


on that very topic.

It seems that the error message is simply misleading and that it should read 
"Cannot read the config_file, hba_file, or ident_file" — as they are named in 
this query's output":

select name, setting
from pg_settings
where category = 'File Locations';

Sure enough, neither my hba_file nor my ident_file were readable by "all" (but 
they were readable by "group"). However, the config_file was readable by "all". 
I've no idea what the history of those permissions is. Maybe I changed 
something along the way. I s'pose that I'd better regard my present 
installation as a dress rehearsal and simply redo it starting by restoring my 
"bare" Linux VM from file backup.

Anyway, just to prove the point, I chmod'd my hba_file and my ident_file to 
make them readable by all. And the silly error message went away.

However, that feels wrong to me. It would seem proper to put any user who you 
want to set up for "local", "peer" authentication into the "postgres" group.

What do you (all) think?

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

[Bryn Llewellyn]

> 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, at signs and dashes, and not start 
>> with
>> a dash (as defined by IEEE Std 1003.1-2001). For compatibility with Samba
>> machine accounts $ is also supported at the end of the username
>> 
>> I tried your longer version verbatim:
>> 
>> useradd -m -s /bin/bash 'mac$crooge'
>> 
>> and that quietly succeeded. I'd left out "-m" and "-s" because, for an 
>> ordinary username, I get the home directory that I want and the (bash) shell 
>> that I want without explicitly asking for these.
>> 
>> It's bizarre that, merely by being explicit about these two fact, I'm now 
>> allowed to have a name with a dollar-sign—notwithstanding what the text of 
>> the earlier error message claimed. I wondered if that it wasn't an error 
>> message at all—and was just a warning. But "cat /etc/passwd" showed me that 
>> "dog$house" had not been created while "mac$crooge" HAD been.
> 
> Also note the "useradd" != "adduser" - you are running two different 
> commands.  One of them is stock Linux while the other is provided by Ubuntu 
> (probably Debian, actually, too lazy to research specifics).

Yes, indeed. I couldn't muster the strength to mention that piece of silliness. 
This explanation:

https://askubuntu.com/questions/345974/what-is-the-difference-between-adduser-and-useradd

is on a relatively trustworthy site. And its account sound  plausible. (Maybe I 
should say the its ountacc sounds sibleplau.)

My reading of it is that  "adduser" is to be preferred. It certainly seems to 
be what you normally see in various random examples on the Internet.

Anyway, my conclusion remains the same. I'll stick with "clstr_mgr" for my O/S 
user.

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

[Bryn Llewellyn]

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'
> trintignant:~ 22:46 :-) 1016# su - 'mac$crooge'
> mac@trintignant:~$ id
> uid=1002(mac$crooge) gid=1003(mac$crooge) groups=1003(mac$crooge)
> mac@trintignant:~$
> 
> I'm not saying that doing this is a good idea ...

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, at signs and dashes, and not start with
a dash (as defined by IEEE Std 1003.1-2001). For compatibility with Samba
machine accounts $ is also supported at the end of the username

I tried your longer version verbatim:

useradd -m -s /bin/bash 'mac$crooge'

and that quietly succeeded. I'd left out "-m" and "-s" because, for an ordinary 
username, I get the home directory that I want and the (bash) shell that I want 
without explicitly asking for these.

It's bizarre that, merely by being explicit about these two fact, I'm now 
allowed to have a name with a dollar-sign—notwithstanding what the text of the 
earlier error message claimed. I wondered if that it wasn't an error message at 
all—and was just a warning. But "cat /etc/passwd" showed me that "dog$house" 
had not been created while "mac$crooge" HAD been.

So I've leaned something about yet another Linux weirdness.

However, now that I know what I know from what contributors to this thread have 
told me, I'll stick with plain "clstr_mgr" for the O/S user and use the 
"clstr$mgr" spelling just for the cluster role. It's a mild nuisance having to 
enquote this when it's the argument of psql's "-U" option, and in the config 
files. But I can live with that.

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

[Bryn Llewellyn]

> 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" authentication as so it does NOT require a 
 password. That would be enough for me. But, naturally, and now that it's 
 working. I prefer the Peter-inspired bare "psql".
>>> 
>>> Personally, I use longer forms like above as a form of explicit is better 
>>> then implicit. There are no end of posts to this list where the issue was 
>>> someone or something had changed a 'hidden' value in a env variable or conf 
>>> file could not connect or connected to wrong cluster and/or database.
>> 
>> This thinking extends, of course, to:
>> 
>> psql -d postgres -U ‘postgres'
>> 
>> having logged in as the O/S user "postgres". (And here, I can simply "set 
>> role" to "clstr$mgr" when I need to without exiting one session, logging in 
>> as a different O/S user, and then starting a new session.) But when I'm 
>> working interactively, I might well allow myself to type the bare minimum, 
>> on the fly, that gets the result.
> 
> This implies that the only auth method you will be using is peer, is that 
> correct? This also means that the only connections to the cluster will be 
> done as local, is that correct?

I must stress that this is just an idea that I’m thinking about. I’m not 
committed to anything. At the very least, I’ll need to implement the complete 
convention-based multitenancy scheme that I sketched and try out some use cases.

The idea that informs this is that, maybe, sessions authorized as “postgres” or 
“clstr$mgr” would be needed only immediately after creating a new cluster to 
bootstrap the regime into place and to create, say, 100 empty databases.

Maybe, from time to time, it would be appropriate to patch the artifacts that 
implement the scheme. But that should be doable (with the usual discipline for 
making only compatible changes).

On a daily basis, the people who know the password for the “dNN$mgr” tenant 
database’s manager could meet all their role-provisioning needs by using the 
pre-installed “security definer” procedures. Even to the extend that they could 
easily restore it to the pristine state and start again. Or they could simply 
send an email to say they were done with it. And then the “clstr$mgr” guy would 
change the password and return it to the pool. (So another very rare task for 
that team.)

It might be too strict to force the “clstr$mgr” guys (and the “postgres” guys 
too) to “ssh” the to cluster’s host to do these tasks. But the idea that it’s 
simply impossible to start a session as one of these roles except by doing that 
appeals to my sense of what “hardening means. Another choice is to be stricter 
about “postgres” than about “clstr$mgs”—just as the doc talks about.

So, yes, if I still like it when it’s all working, then each of the “postgres” 
and “clstr$mgr” roles would have a NULL password the the config files that 
we’ve been discussing would allow them to use ONLY “local”, “peer” 
authentication.

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

[Bryn Llewellyn]

> 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 
>> working. I prefer the Peter-inspired bare "psql".
> 
> Personally, I use longer forms like above as a form of explicit is better 
> then implicit. There are no end of posts to this list where the issue was 
> someone or something had changed a 'hidden' value in a env variable or conf 
> file could not connect or connected to wrong cluster and/or database.

Yes. I think the same as you about being explicit (in programs and scripts). 
That's why the "create role" statement that I showed mentioned every settable 
attribute. It's relatively rare that my requirement is "use the reigning 
defaults, whatever they might be now and whatever they might be changed to 
later". (Having said this, that for me rare scenario is proper in certain 
cases.)

So when I write a script to connect as "clstr$mgr", I'll use the explicit form 
that calls for "local", "peer" authentication and that uses the "-d" and "-U" 
flags. And I'll add a comment to say that, because the script is run only on 
the cluster's host machine after logging in as the O/S user "clstr_mgr", the 
(only) required password challenge has already been met. I plan to stage all of 
my "PG multitenancy by imposed convention" code in a dedicated Yugabyte, Inc 
GitHub repo. This will allow the code comment that I mentioned to x-ref the 
README.md that explains how I set up "pg_hba.conf" and "pg_ident.conf" to 
define the mapping between the O/S principal and its partner within-cluster 
principal.

This thinking extends, of course, to:

psql -d postgres -U 'postgres'

having logged in as the O/S user "postgres". (And here, I can simply "set role" 
to "clstr$mgr" when I need to without exiting one session, logging in as a 
different O/S user, and then starting a new session.)

But when I'm working interactively, I might well allow myself to type the bare 
minimum, on the fly, that gets the result.

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

[Bryn Llewellyn]

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 writing too much, and so I kept my account short. Maybe too 
much so. Anyway, I've written it up more fully at the end. Feel free to ignore 
that account.

Very briefly, I find the notion appealing that you can authorize a client 
session as "postgres" (using this actual role name to denote the cluster's 
bootstrap superuser) by authorizing an O/S session on the machine that hosts 
the cluster's data and the software that manages it without needing a (second) 
password because being able to log in as the right O/S user is considered 
enough of a check. I'll call this O/S user "postgres", too, recognizing the 
common convention and to save myself some typing. This allows the possibility 
to set the password for the "postgres" cluster-role to NULL so that you MUST 
use the O/S prompt to start a session as this role. In other words, make it 
such that "local", "peer" authentication is the ONLY way to start a session as 
the "postgres" role". (This would echo a very popular, highly recommended, 
practice  with Oracle Database and its corresponding SYS database user.)

"Local", "peer" authentication is actually essential when you install PG on 
Ubuntu because the "apt install postgresql-11" flow (at least) offers no 
opportunity for user input and finishes up with an already-started cluster that 
has password authentication turned on (using the "md5" method). But the 
password is a secret. So the only way to make progress its to start with this:

psql -c " alter role postgres with password 'x' ";

"Local", "peer" authentication is also a useful backdoor (even when a NOT NULL 
role password is defined) for the case that a human being forgets the password 
that allows starting a session as the "postgres" role from a client machine.

Further, if the "postgres" cluster-role's password is deliberately set to NULL, 
then there's no second password to keep safe—but you can, nevertheless, start a 
session as the "postgres" cluster-role from the O/S prompt where the cluster is 
hosted by authorizing as the "postgres" O/S user. I'll think more about this. 
But it seems that it might be a useful "hardening" notion in the general 
business of security practice to adopt this regime intentionally.

As an extension of this thinking, I've resolved to adopt the practice 
recommendation from the doc always to use a dedicated, slightly junior, role 
for provisioning databases and roles. I want to call this role "clstr$mgr". 
And, yes, I do want that dollar sign in place. I explain why below. The 
practice goes hand-in-hand with keeping the password that allows starting a 
session as the "postgres" role a very closely guarded secret. This means that 
the people who know the password that allows starting a session as the 
"clstr$mgr" role will NOT know the password that allows starting a session as 
the "postgres" role.

Of course, because what's sauce for the goose is sauce for the gander, I want 
to show that it's possible to implement the same practice for "clstr$mgr" 
sessions as for "postgres" sessions. In other words, to make it possible to 
start a session as the "clstr$mgr" role ONLY by using "local", "peer" 
authentication—but, obviously, via its own dedicated O/S user.

However, Linux (at least) simply disallows O/S users that have a dollar sign in 
the name. That's where the idea of using a mapping from the O/S user 
"clstr_mgr" to the cluster role "clstr$mgr" comes from. (So if Linux had a 
different philosophy for user names, like it already has for file names, then I 
wouldn't be talking about mapping.)

THE SOLUTION

I now have an end-to-end solution where I can, for example, "ssh" to the 
cluster's host machine as the O/S user "clstr_mgr" and simply type "psql" at 
the O/S prompt, as soon as I'm in, to take me to a session where this:

select current_database()||' > '||session_user as "Where/who ami I";

shows this:

   Where/who ami I
--
 postgres > clstr$mgr

I actually have no requirement to elide the database name or the role name. The 
only thing I *require* is not to need a second password. But Peter showed me 
how—so why not follow his plan? It's a nice, albeit small, usability benefit. 
Here's how I got there.

1. Create the database role
---

create role clstr$mgr with
  nosuperuser
  createrole
  createdb
  noreplication
  nobypassrls
  connection limit -1
  login password null;

2. Create the partner O/S user
--

I use "sudo" from any starting place that allows this. I (with another hat on) 
have to be allowed to do this, also, for, e.g, "systemctl start postgresql" and 
its cousins.

sudo adduser clstr_mgr

Then (as per Peter) I put this in 

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

[Bryn Llewellyn]

> 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) groups=1001(postgres)
> 
> This has nothing to do with your problem, but don't do this. Normal  users 
> should not be in group "postgres". That gives them access to some files which 
> are not readable by the public. It might be useful for administrators, but 
> AFAICS your test users aren't supposed to be that.
> 
>> uid=1001(postgres) gid=1001(postgres) groups=1001(postgres),27(sudo),114 
>> (ssl-cert)
> 
> And is there a reason for postgres to be in group sudo?

Thanks for pointing this out, Peter.

I was careless. I'm testing ideas using my laptop. And apart from the fragments 
of SQL, O/S scripts, and what these report, that I've shown on this list, 
everything is private. (Nobody else can access my laptop without stealing it 
and breaking in.)

That's no excuse for showing sloppy practices. I'll aim to do better.

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

[Bryn Llewellyn]

> 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-conf.html 
> 
> 
> "The map-name is an arbitrary name that will be used to refer to this mapping 
> in pg_hba.conf."
> 
> This example below is for the ident auth method but the same syntax applies 
> to peer.
> 
> # TYPE  DATABASEUSERADDRESS METHOD
> hostall all 192.168.0.0/16  ident 
> map=omicron
> 
> pg_ident.conf and pg_hba.conf are two separate files and the only way 
> information gets from the former to the latter is if you explicitly include 
> the map name under METHOD for the the auth line.

Yes, Adrian, I see that I did slip up. Thanks, David, for pointing out this 
same error in your separate reply. I’m advertising my stupidity in this area 
rather effectively. My problem stems from the fact that the goal statement that 
my inner voice expresses seems so simple to state. This is what I want:

1. I want to do this at the O/S prompt on the machine where my PG cluster has 
been started: "su mary".

2. Then I want to start a session (I use "psql" here an an example) like this: 
"psql -d postgres".

3. Then, at the "psql" prompt, I want "select session_user" to show "bob".

It would have seemed to me, knowing just that the goal is achievable, that I 
could express this declaratively in one place—without needing to name the 
mapping between the system user's name and the cluster role's name, thus:

authentication type:local
authentication method:  peer
system user:mary
cluster role:   bob

I know that it isn't like this. But I have no intuition for why it could not be 
like this—and so it's easy for me to get muddled.

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) groups=1001(postgres)
uid=1001(postgres) gid=1001(postgres) 
groups=1001(postgres),27(sudo),114(ssl-cert)

And I set up the cluster-roles "bob" and "mary" so that "\du" shows this:

bob   || {}
mary  || {}
postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Then I started with this in "pg_hba.conf":

...
# TYPE  DATABASEUSERMETHOD  [auth-options]
#   --  --  --  
  local all bob peer
  local all marypeer
...

together with en empty "pg_ident.conf". So far, after either "su bob" or "su 
mary", i was able to confirm that the bare "psql -d postgres" worked fine and 
that then "select session_user" showed, respectively, "bob" or "mary", too.

Then I changed  "pg_hba.conf" to add a mapping for "mary" thus:

# TYPE  DATABASEUSERMETHOD  [auth-options]
#   --  --  --  
  local all bob peer
  local all marypeermap=bllewell

But I left "pg_ident.conf" deliberately empty. I expected, now, that "psql -d 
postgres" would still work fine for "bob" but that if would fail for "mary". 
With this deliberate error in place, I found that after "su bob", the bare 
"psql -d postgres" worked fine. But after "su mary", the same command caused 
this error:

Peer authentication failed for user "mary"

I assume that the phrase « user "mary" » means the O/S user "mary".

It seems to me that the message « no entry for the mapping "bllewell" in 
"pg_ident.conf" » would be more helpful. But maybe that would need psychic 
powers.

Next, I put an identity mapping in for "mary" in "pg_ident.conf" thus:

# MAPNAMESYSTEM-USERNAME   PG-USERNAME
# ------   ---
  bllewell   mary  mary

So, "bob" is the simple case. And "mary" is one step harder. Now, the 
unqualified "psql -d postgres" works again for "mary" (and it still works, of 
course, for "bob").

So far, so good. Now for the test, I mapped "mary" to "bob" in "pg_ident.conf" 
thus:

# MAPNAMESYSTEM-USERNAME   PG-USERNAME
# ------   ---
  bllewell   mary  bob

As I'd expect, O/S "bob" still works fine and ends up as cluster-role "bob". 
But now, the attempt by O/S "mary" to connect using "psql -d postgres" fails, 
as it had ealier, with what boils sown to "computer says No":

Peer authentication failed for user "mary"

I still don't have a mental model that can explain this. As I reason it, the 
name "mary" 

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

[Bryn Llewellyn]

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
 


I'm starting a new thread because my question, now, has nothing to do with the 
role whose interim name was deemed to be best spelled "bootstrap super user" 
for the time being.

This question is about "peer" authentication. I am able to make it work as long 
as my O/S user's name (what "pg_ident.conf" calls the "SYSTEM-USERNAME") is 
spelled identically to my partner cluster role's name (what "pg_ident.conf" 
calls the "PG-USERNAME"). But the doc for this file explains that you can 
define a mapping in "pg_ident.conf", give it any "MAPNAME" that you want, and 
map a "SYSTEM-USERNAME"to a differently spelled "PG-USERNAME". Or, as you put 
it, Adrian"

> The purpose of mapping would be to do something like map OS user foo to PG 
> user usr.


I want to get this to work because I want to use a role-name that has a 
dollar-sign in it (I don't care that this isn't in line with the Standard) and 
because the O/S uses dollar-sign in a reserved way and I don't want to go 
against the convention there by escaping things.

Here, I simply used o/s user "bob" and cluster role "alice".

And, yes, I did read the two doc sections "The pg_hba.conf File" and "User Name 
Maps" (for Version 11 'cos that's what I'm using). The latter shows this 
example:

# MAPNAME   SYSTEM-USERNAME PG-USERNAME
...
omicron robert  bob

And I simply decided to follow its spirit with "bob" mapping to "alice", thus:

# MAPNAME   SYSTEM-USERNAME PG-USERNAME
...
bllewellbob alice

Here's my "pg_hba.conf":

...
local   all postgrespeer # See the 
essay at the start.
local   all alice   peer
local   all bob peer
local   all all peer
...

For reasons that will become clear in a moment, the file has entries for both 
"bob" and "alice".

Here's how I created the O/S user:

adduser bob # Password «x»
usermod -g postgres bob

And here's how I created the cluster role:

create role alice with
  nosuperuser
  createrole
  createdb
  noreplication
  nobypassrls
  connection limit -1
  login password 'x';

(You can see that my plan is to follow the advice from  the section "Role 
Attributes".) Again, for reasons that will become clear in a moment, I also 
created the role "bob" using an otherwise identically spelled "create role" 
statement.

Then I bounced the cluster thus (as my "postgres" O/S user):

sudo systemctl stop  postgresql
sudo systemctl start postgresql
pg_ctl reload -D /etc/postgresql/11/main/

(I know that I could've used "systemctl restart ".) Like I said elsewhere, the 
"reload" seems to be superfluous. But it costs nothing to do it.

Then I did "su bob" and first did this sanity test:

psql -h localhost -p 5432 -d postgres -U alice

That worked fine—and "select current_role" showed "alice".

Then I did the spelling for "peer", to authorize explicitly as "bob":

psql -d postgres -U bob

That worked too so that "select current_role" now showed "bob". Finally, I 
omitted "bob" here in the belief that this would make my mapping kick in and 
authorize using the cluster role "alice":

psql -d postgres

It got me in without error. (And, as hoped for, there was no password 
challenge.) But "select current_role" showed that the mapping had been ignored 
and that I was connected again as "bob".

What am I doing wrong?

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

[Bryn Llewellyn]

> 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 
> by now --- I count half a dozen uses in our SGML docs and another dozen or so 
> in the code --- and it's certainly more specific than "database superuser". 
> We should probably create a glossary entry for it and then change all the 
> uses of "database superuser" as appropriate.
> 
> However ... it looks to me like some of those uses just mean to distinguish 
> between Postgres-specific superuser-dom as opposed to whatever the term might 
> mean out in the operating system. But I'm not sure that anybody really uses 
> that term for an OS-level
> concept on any popular OS, so it feels a bit pedantic as well as confusing.  
> Should we leave those usages alone, or reduce them to just "superuser"?

Thanks, Tom. I'd certainly appreciate an entry in "Appendix M. Glossary" for 
the term of art that I've been struggling to name. Until I hear what the 
experts decide, I'll use "catalog-owning role". Then I'll switch to the newly 
blessed term.

I'm afraid that I didn't get your point in your last paragraph. The terms 
"within-cluster role" and "O/S user" seem to capture the distinction when the 
context doesn't make it clear. I aim never to use the term "user" for the 
within-cluster phenomenon. After all, an existing within-cluster role can flip 
between "with nologin" and "with login" at the drop of a hat of the guy "with 
createrole".

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

[Bryn Llewellyn]

> 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 O/S user that owns lots of (but not all*) the software files 
>> that define the PostgreSQL RDBMS, together with the various files that 
>> represent what users create
>> 
>> are identical.
> 
> Nope, the name of the bootstrap user is the one supplied to initdb via the 
> --username argument.  Period. It need not match any name on the host 
> operating system and it will still be the bootstrap superuser's role name.
> 
> Yes, the description for --username probably should be modified to read:
> 
> "Selects the user name of the cluster's bootstrap superuser." Or just 
> consider a "cluster superuser" the term d'art...since most people would just 
> refer to any old role having superuser authorization as being plain ole 
> "superuser".  The fact that is says "database superuser" is the same holdover 
> effect as the fact that "init db" means "init database" even though it 
> actually initializes a cluster.

Thanks again, David. And once again, all is clear now.

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

[Bryn Llewellyn]

> 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-cluster catalog-owning role has a certain name, say 
>> "pg_system" (or "bob").
>> 
>> 2.  The O/S user that owns (most of) the O/S presence of the cluster and the 
>> software that accesses it has the identical name "pg_system" (or "bob").
>> 
>> 3. The current O/S user when you make the attempt to connect is "pg_system" 
>> (or "bob").
>> »
>> 
>> I want to know if my hypothesis is correct.
> 
> It is not.  "By default" probably, but not "only". Peer authentication means:
> 
> If the local O/S user (bob) running "psql" requests to login to the database 
> using that same* role name (bob) and the role exists in the cluster, accept 
> the authentication attempt.
> 
> * You can implement aliases by using an identity mapping.
> 
> Nothing more, nothing less.
>  
>> And, more importantly, I want to know where I can read a nicely written 
>> linear account of what *is* correct tha defines and then uses the official 
>> terms of art.
> 
> It doesn't exist, deal with it. Most people just call the "bootstrap" role 
> "postgres" when not talking about a specific installed cluster that happens 
> to use something different. In any case, no matter what terminology is used 
> everyone seems to figure out what is being referred to from context at least 
> and largely don't make a big deal about it.  Unless you actually want to 
> write the documentation just pick something you like and go with it.

Yes, all is clear now. Thanks again. And thanks for the dispensation to choose 
my term of art. I'll use "catalog-owning role" from now on.

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

[Bryn Llewellyn]

> 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 
> probably a better term for the O/S side of things.  Though, frankly, aside 
> from trying to distinguish things when talking about logging in, the 
> necessity to even care about the O/S user is fairly minimal.
> 
> [about your "usr" example] just create a database named "usr" and you won't 
> get the "database usr not found" error message anymore and the login will 
> succeed.

Thank you very much David. The scales have now finally fallen from my eyes. I 
know now that in order to be able to start a client session from the O/S of the 
machine where the PG software and cluster live, without needing to supply a 
password even when "pg_hba.conf" asks for password authentication, it's 
sufficient to do this (using my "usr" example):

(0) Simply leave the regime in place where the catalog-owning role is called 
"postgres" and the cluster's data files and other config files are owned by 
postgres.

(1) create a new database role thus (where "password null" is just so that I 
can prove a point here):

create role usr with login password null;

(2) Add this line under the existing final comment in the shipped copy of 
"pg_ident.conf" thus:

# MAPNAME   SYSTEM-USERNAME PG-USERNAME
usr usr usr  # Added by Bryn

(It seems that I could set the first field of this line to "dog"—but I won't 
test that.

(3) Add this line between the existing two in the shipped copy of "pg_hba.conf" 
thus:

local   all postgrespeer # See the 
essay at the start.
local   all usr peer # Added by 
Bryn
local   all all peer

(My copy of this file specifies "md5" and not "trust".)

I'd've thought that "all" would mean any O/S user existing, or 
yet-to-be-created. But the comment in the shipped "pg_hba.conf" says this:

# If you change this first entry you will need to make sure that the
# database superuser can access the database using some other method.
...
# Database administrative login by Unix domain socket
local   all postgrespeer

(So two terms for the one notion just a couple of lines apart!) I'll do the 
empirical test presently. Anyway, with these conditions met, I can "su usr" and 
then start a session like this:

psql -d postgres

Yes, your point about what artifacts exist the moment after "initdb" finishes 
is taken. So I finished my test by (after authorizing as "postgres") creating a 
database "usr" and granting "connect" on it to "usr".) Then I could create a 
new session from the O/S prompt when "whoami" shows "user" with the bare 
"psql"—just as I could the moment after the PG install finished from the O/S 
prompt when "whoami" shows "postgres".

I did think that I'd tried all this at the outset. But clearly I must've missed 
one of those steps or done a typo.

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

[Bryn Llewellyn]

> 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 the name of the 
>> within-cluster role to connect as—and without supplying a password. And it 
>> can do this only from as O/S session where the effective O/S user is the 
>> bootstrap superuser.
> 
> I don't believe this is correct.  psql is using libpq.  A hint to that is 
> given in the section under the \c command:
> 
> When the command neither specifies nor reuses a particular parameter, the 
> libpq default is used.
> 
> The libpq docs show more info on the connection parameters 
> (https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS)
> 
> If not specified, both the user and the dbname default to the name of your 
> operating system user.  So, this could work if your operating system user, 
> the database name, and the database username are all postgres, but they could 
> also all be bob.
> 
> As to the password requirement - this depends on the settings in pg_hba.conf. 
>  You could set all connections to trust and then no one would need a 
> password, but I wouldn't recommend that.

Thank you for this extra information, Jeremy. I'm afraid that what I wrote was 
insufficiently precise. I should have added these riders:

(1) My O/S env is in a brand new VM (it happens to be Parallels on my Big Sur 
Mac Book) that was created by a single button press that selected Ubuntu 20.04. 
(I followed that with what I hope is only benign customization for terminal 
colors and the like.)

(2) My PG regime is what I end up with in this VM immediately following the use 
of "apt install postgresql-11". (There's a good reason why I want that old 
version. I hope that its age isn't a distraction here.) Notably, the 
installation flow offers no opportunity to express choices.

(3) This gives me the PG software (largely owned by the O/S user "postgres", 
but with some programs owned by "root") and an already started cluster.

(4) I am able to start a plsql session, when my O/S user is "postgres" simply 
by typing the bare command "psql". Yes, I'm implicitly selecting various libpq 
default values—just as you described. But those defaults don't include a 
default for the password.

(5) When I start a session in this way, I see that I have this regime: a single 
role with the name "postgres" and the status "superuser"; and a single database 
non-template database also with the name "postgres", together with the usual 
"template1" and "template2". Further, this query (when connected to the 
"postgres" database):

select nspname
from pg_namespace n inner join pg_roles r on n.nspowner = r.oid
where r.rolname = 'postgres'
order by 1;

shows me this:

pg_toast
pg_temp_1
pg_toast_temp_1
pg_catalog
public
information_schema

When I asked how to refer to this clearly special cluster-role, David said:

> Don't think it's documented but I like "bootstrap user"


and Ian said:

> "bootstrap superuser" is also mentioned.


Meanwhile, in a separate thread, Adrian pointed me to the "initdb" doc (and 
command line help) where the term "database superuser" is used.

This means that I'm so far denied the possibility to use a single term that 
everybody agrees on. I may as well call it the "catalog owning role" here 
because at least that term is unambiguously descriptive.

I stated in my reply to Adrian that  I had formed this hypothesis (reworded 
slightly here).

When he environment is what I described at the start (which env. brings a 
"pg_hba.conf" file that requires password authentication by NOT specifying 
"trust"),

«
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-cluster catalog-owning role has a certain name, say "pg_system" 
(or "bob").

2.  The O/S user that owns (most of) the O/S presence of the cluster and the 
software that accesses it has the identical name "pg_system" (or "bob").

3. The current O/S user when you make the attempt to connect is "pg_system" (or 
"bob").
»

I want to know if my hypothesis is correct. And, more importantly, I want to 
know where I can read a nicely written linear account of what *is* correct tha 
defines and then uses the official terms of art.

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

[Bryn Llewellyn]

> 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" 
>> uniquely what it is—at least on Ubuntu.
> 
> How much time would it have taken to go to the docs:
> 
> «
> https://www.postgresql.org/docs/current/app-initdb.html
> 
> Selects the user name of the database superuser. This defaults to the name of 
> the effective user running initdb. It is really not important what the 
> superuser's name is, but one might choose to keep the customary name 
> postgres, even if the operating system user's name is different.
> »

I HAD read that. The phrase occurs three times on that page. But the account 
doesn't define the term. Rather, it's used as if everybody knows what it means. 
Yet there's no x-ref to where the definition is. I did refer, albeit 
implicitly, to this doc by citing the text that "initdb --help" gives. The doc 
and the help say the same thing. You, Adrian, often accuse me of being too 
wordy. So I  catered to you by not using words to spell out what I just spelled 
out here.

A PG cluster has lots of databases. Lots of things have names whose uniqueness 
scope is (maximally) a single database. Just a couple of things, and roles in 
particular, need names that are unique in the cluster as a whole. Therefore, 
the term "database superuser" is tautologically wrong. It should, at least, be 
"cluster superuser". But then, like I said, you can have as many superusers as 
you please in a single cluster. So the idea that one is singled out as *THE* 
[cluster] superuser didn't make sense to me. Moreover, the "initdb" doc says 
that it doesn't really matter if what it calls the "database superuser" has the 
same name as the O/S ussr that owns (most of) the PG installation and cluster 
content. But David pointed out here:

https://www.postgresql.org/message-id/CAKFQuwYHLCMpLgPbBC7idoomoKwrgrjGxOnd%2BD1CXAQtf3DHcg%40mail.gmail.com

that you lose a lot if these two sides of the same coin don't have the same 
name. (So the "initdb" doc would be improved by an x-ref to the discussion of 
the consequences of the name choices here.)

This implies that there's still a missing term of art that denotes the nicely 
matched *pair* of within-cluster role and O/S user.

I said all this in my reply to David and Ian Barwick. I did send it about half 
an hour before you wrote this. But I see now that I'd managed to omit 
"pgsql-general@lists.postgresql.org 
" from the addressee list. (Yes, 
another of my notorious typos.) I resent it moments ago. It's here:

https://www.postgresql.org/message-id/5C47A318-F265-4A64-B306-C4019F499DB8%40yugabyte.com

>> ...listed as the owner of the pg_catalog schema, the objects in it, other 
>> related schemas...
> 
> 
> You need to define 'other related schemas'.

select nspname
from pg_namespace n inner join pg_roles r on n.nspowner = r.oid
where r.rolname = 'postgres';

produces this:

pg_toast
pg_catalog
information_schema
...

Same point as before. You and others in the cohort of "the pgsql-general list 
lawmakers" have made me nervous about spelling things out 'cos doing so uses 
words and code—and often I've been told off for being too wordy. This is a pity 
because accuracy and precision inevitably compete with brevity.

> ...For it to  work you have to be operating as the OS user postgres. I'm 
> guessing that is why your attempt as usr failed, you where not running as the 
> OS user usr.

No, I've been super-aware of the current identity of the O/S user in all tests, 
I've typed "whoami" more times in the last few days than before in my whole 
life to date.

>> I tried to set up "peer" authentication for a brand new O/S user that I 
>> called "usr" to match a brand new cluster role that I also called "usr". I 
>> added a new line in "pg_hba.conf" thus:
>> local   all usr peer
> 
> 
> Read:
> https://www.postgresql.org/docs/current/auth-peer.html 
> 

I had. And I'd followed the link to Section 21.2:
https://www.postgresql.org/docs/current/auth-username-maps.html

> «
> "The pg_ident.conf file is read on start-up and when the main server process 
> receives a SIGHUP signal. If you edit the file on an active system, you will 
> need to signal the postmaster (using pg_ctl reload, calling the SQL function 
> pg_reload_conf(), or using kill -HUP) to make it re-read the file.
> »

To be sure, I did the whole thing again now. (And, yes, my O/S user is 
"postgres", at the start of this account.) Here's the relevant part of the 
output from "cat /etc/passwd": 

postgres:x:1001:1001:,,,:/home/postgres:/bin/bash
usr:x:1002:1001:,,,:/home/usr:/bin/bash

I did "sudo systemctl stop postgresql". Then I made sure 

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

[Bryn Llewellyn]

(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  wrote:
> 
>> david.g.johns...@gmail.com :
>> 
>>> 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.
>> 
>> Don't think it's documented but I like "bootstrap user" which I've seen 
>> bandied about here a bit. It isn't that special but if the bootstrap user 
>> name and o/s user name are not the same name then you've broken an almost 
>> universal convention that exists to make stuff like logging in with peer 
>> authentication work better.
> 
> "bootstrap superuser" is also mentioned a few times in the [upcoming] docs, 
> see e.g.
> 
> https://www.postgresql.org/docs/devel/sql-grant.html 
> 
> 
> This recent commit: e530be2c5ce77475d56ccf8f4e0c4872b666ad5f [1] might also 
> be of interest to anyone considering the "special-ness" of this role.
> 
> [1] 
> https://git.postgresql.org/gitweb/?p%3Dpostgresql.git;a%3Dcommit;h%3De530be2c5ce77475d56ccf8f4e0c4872b666ad5f

Thanks. David and Ian. I'll take this:

> If GRANTED BY is specified, the grant is recorded as having been done by the 
> specified role. A user can only attribute a grant to another role if they 
> possess the privileges of that role. The role recorded as the grantor must 
> have ADMIN OPTION on the target role, unless it is the bootstrap superuser. 
> When a grant is recorded as having a grantor other than the bootstrap 
> superuser, it depends on the grantor continuing to possess ADMIN OPTION on 
> the role; so, if ADMIN OPTION is revoked, dependent grants must be revoked as 
> well.

from an upcoming version of the "grant" statement doc, to be a sufficient 
establishment of the canonical status of the term of art that I sought. I'll 
adopt the term "bootstrap superuser" (and not plain "bootstrap user") and I'll 
assume that everybody on this list (at least anybody who might answer my 
questions) shares the same, and immediate, understanding of the term—which 
implies this:

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 O/S user that owns lots of (but not all*) the software files 
that define the PostgreSQL RDBMS, together with the various files that 
represent what users create

are identical.


[*] I see that, in my Ubuntu installation, critical programs like "postgres" 
itself, "initdb", "pg_ctl", "pg_dump" and so on are owned by "root".

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) means that some 
sentences will require extra verbiage to identify which half of the pair the 
sentence treats. I'm open to suggestions. But I'll start with these these I'm 
corrected: the "bootstrap (regular) OS-user" and the "bootstrap within cluster 
superuser role". Sadly, the fact that "super" is baked into the term of art 
makes it difficult to name the O/S half of the phenomenon.

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 the name of the within-cluster role to 
connect as—and without supplying a password. And it can do this only from as 
O/S session where the effective O/S user is the bootstrap superuser.

It seems, too, that one would be stupid to call the bootstrap superuser 
anything other than "postgres". Notice that this implies that the typical macOS 
regime (where my bootstrap super user is called "Bllewell" and has to be 
double-quoted in SQL, and yours is called "sagrawal") is, indeed, stupidly 
unconventional.

All this implies a little test. Here, I'll save typing by saying that my 
bootstrap superuser is called "postgres". I created a second database in a 
freshly created cluster called "x". And then, from the O/S, I tried this:

psql -d x

That worked fine. But, having said this, it would seem that it would be so very 
unconventional (given that you've already agreed to call your bootstrap 
superuser "postgres", not to make a database called "postgres" available too. 
(I tested that by dropping my "postgres" database. (This is the freedom that a 
VM with a nice snapshot together with the ability to start afresh with "initdb" 
brings.) Now, the bare "psql" causes the error "FATAL:  database "postgres" 
does not exist".

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

[Bryn Llewellyn]

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 observations told me:

It's easy to characterize this role by describing the way that you get it and 
the conspicuous unique emergent properties that it has.

* You specify its name using the flag that's shown thus in response to "initdb 
—help"

  -U, --username=NAME   database superuser name

* It's listed as the owner of the pg_catalog schema, the objects in it, other 
related schemas in every existing and yet-to-be-created database, and some 
global things too. Loosely, it's the owner of the SQL part of the 
implementation of PostgreSQL.

The conventional choice is "postgres". I just did a brand-new PG installation 
in a brand new Ubuntu VM and I simply ended up with this name when the 
installation finished. (There was no chance in the installation flow to choose 
the name.) However, an informal survey among contacts who have PG installations 
on macOS showed that this "special" role ends up with the name that you gave 
when you first configured your new macOS for the admin O/S user. It's usually a 
cryptic form of one's own name—as is my "Bllewell".

But the name "database superuser name" (in "initdb" speak) is useless as a term 
of art for naming the phenomenon because you can have an unlimited number of 
roles that are created "with superuser" in a PG cluster.

In another context, the comments in the shipped "pg_hba.conf" file (at least on 
Ubuntu) include these:

# Database administrative login by Unix domain socket

for (in my case) this line:

local   all postgrespeer

(I failed when I tried to add a new one of my own. See below. But I assume that 
it must be possible—also for a superuser.)

I noticed that in my case, the bare "psql" O/S command connects me to "-d 
postgres -U postgres" without a password challenge. And the setup had been done 
by the installation. Is "postgres" role uniquely able to connect in this way 
with no password challenge? And might "the administrative role" be the term of 
art that I'm seeking?

— — — — — — — — — — — — — — — — — — — — 

* B.t.w., I tried to set up "peer" authentication for a brand new O/S user that 
I called "usr" to match a brand new cluster role that I also called "usr". I 
added a new line in "pg_hba.conf" thus:

local   all usr peer

(But there already is such a line for the special name "all".)

And I added a new line in "pg_ident.conf" (before, there were none at all) thus:

# MAPNAME   SYSTEM-USERNAME PG-USERNAME

usr usr usr

But this attempt to connect:

psql -d postgres -U usr

failed with this error:

connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: 
FATAL:  Peer authentication failed for user "usr"

while this attempt:

psql -h localhost -p 5432 -d postgres -U usr

happily suceeded. I clearly missed some essential other steps. But the doc 
didn't x-ref me to these.

I also tried this:

initdb \
  -U usr --encoding UTF8 --locale=C --lc-collate=C --lc-ctype=en_US.UTF-8 \
  -A md5 --pwfile=/etc/ybmt-code/misc/.superuser-passwd-for-initdb \
  -D /var/lib/postgresql/11/main

It succeeded. And, after re-start, I could connect as "usr". But I still could 
not do this using the "peer" method. I saw that, now, "usr" owns the 
within-cluster PG implementation artifacts.

However, while "initdb" was working, it said this:

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

So "postgres" is clearly special in ways other than just as the name of the 
owner of the within-cluster implementation. And this was thrust upon me simply 
by using the recommended "apt install postgresql-11 method. I had no say at all 
in the choice of this name. (as it happens, I did have a Linux user called 
"postgres" before I started. But I seem to recall, from PG installations on 
Ubuntu that I did a few years ago, that the Linux user "postgres" was simply 
created for me when I didn't already have it.

Where can I read a nice, linear, soup-to-nuts acount of this whole business 
that introduces, and that consistently uses, the proper terms of art?

Re: COMMIT IN STORED PROCEDURE WHILE IN A LOOP

[Bryn Llewellyn]

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 handler [because] that creates a subtransaction for the duration of 
> the [block statement].

I asked this in response (again, paraphrased for brevity):

> Could the limitation be lifted...? [Or is the limitation] rooted in 
> profoundly deep features of the architecture?

Sorry that I caused some distraction. Anyway, Tom replied immediately. He said:

> BEGIN with an exception block is a subtransaction because it's defined to 
> roll back to the database state as of the start of the block if an exception 
> occurs. COMMIT in the middle fundamentally conflicts with that, I should 
> think.


Thanks, Tom. It's clear to me now that the present PG paradigm will never, ever 
change.

So my conclusion stands for this use case: I'm using "serializable" isolation 
(which luxury ORCL doesn't afford me); I know that I can get a "cannot 
serialize" error at "commit" time. Here, I cannot take appropriate action 
within my PL/pgSQL code and hide the whole story of what this is about from 
client code. Rather, I must explain the business to the authors of the next 
tier, and teach them when, and how, retry is appropriate.

Tom asked, too, if ORCL has a different paradigm... Briefly, yes—and radically 
so. But (all of you) do please feel free to skip over my sketch here if it 
doesn't interest you.
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — 
«
The real account of which this is a sketch is of no interest at all if you work 
only with PG and have never used ORCL. But if you need to switch, 
intellectually, from one to the other—and maybe need to do this all the time 
because your job duties span both systems—then it *is* interesting.

ORCL's PL/SQL is compiled (and optimized) at "create procedure" time. The new 
source code replaces the old in the catalog. And if it failed to compile, then 
you're left with an invalid unit that you cannot execute. Compilation errors 
are recorded in the catalog too. Further, static dependencies (proc upon proc, 
proc upon table, etc) are also recorded in the catalog. This is intimately 
connected with the hard distinction between static and dynamic SQL. The latter 
simply passes the text on "as is" into the byte code to be dealt with at 
run-time. Only statements like "select", "insert", "update", delete" and a few 
others can be static SQL. Table creation and the like must be dynamic SQL. This 
probably shocks those who move from PG to ORCL because you cannot, for example, 
create a table and then operate on it with static SQL in the same procedure.

In particular, for the present discussion, the PL/SQL block statement is a pure 
lexical device. (This is the case in PL/SQL's progenitor, ADA. And that's where 
all that stuff about DIANA, that the PL/SQL programmer eventually comes to hear 
about, comes from.) All memory that you had a block statement in the source is 
lost in the compiled so-called byte code that gets interpreted at run time. On 
the other hand, every call from PL/SQL to SQL is done in its own 
subtransaction—and if it fails, then that single statement is atomically rolled 
back. The effect of all the SQLs to date, at this moment, remains intact—but 
uncommitted. (Of course, you might have issued "commit"(s) programmatically. So 
I'm talking about SQLs that were done since the most recent "commit".) 

Significantly, the failure of a call from PL/SQL to SQL raises an exception—so 
(as well as the single-statement rollback) you now have an in-flight exception 
that flies up through successive scopes in search of a matching handler. If it 
remains unhandled at the last moment before the top-level PL/SQL "call" is due 
to finish, then a "rollback" is automatically issued. But if a handler *is* 
found, well... the exception is dead and you can carry on. Like everything else 
in programming, the code author must work out what "safe" is. (It could be to 
turn an insert that fails 'cos a unique key is violated into an update.) In 
ORCL, just as in PG, writing "when others than null" is held to be stupid. And 
code examples that do this are deemed to be not worthy of discussion.

Though the paradigms are different, each allows you properly to implement 
mission-critical applications. It's rather like English and Chinese. 
Astonishingly different. But each supports all that you need to let people 
communicate about mundane daily business, science, philosophy, epistemology, 
and so on.)
»

Re: COMMIT IN STORED PROCEDURE WHILE IN A LOOP

[Bryn Llewellyn]

> 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 the BEGIN / END.

This surprised me when I first started to use PG (after all those years with 
ORCL). There are some use cases where this limitation is a nuisance. For 
example, if you want to implement the famous retry loop (with exponential 
backoff) for an error that is provoked at commit time... well, you have to do 
it in client-side code.

Could the limitation be lifted by making tractable internal implementation 
changes? Or is it rooted in profoundly deep features of the 
architecture—meaning that it could never be lifted?

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

[Bryn Llewellyn]

> 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 number of different examples along these 
>> lines:
>> 
>> select quote_ident('redaktør'); → "redaktør"
>> create table redaktør(n int); → table successfully created
> 
> Yep, and that is precisely what would make for a good bug report. Pointing 
> out that "if necessary" does not indeed match up with the behavior. I suspect 
> it is likely to get changed - everything else being discussed just detracts 
> attention from it.

*BRIEFLY*

What does "make for a good bug report" mean, David? Is it:

(1.1) You, David, or somebody else who has been officially recognized as a PG 
Contributor (https://www.postgresql.org/community/contributors/) will file the 
bug, granting it credibility with their imprimatur?

or (1.2) I, Bryn, should file the bug.

About "I suspect it is likely to get changed", do you mean:

(2.1) Change the doc to match quote_ident's current, unpredictable, behavior? 
(By all means, substitute "hard to describe accurately, precisely, and yet 
still tersely" for "unpredictable".)

(2.2) Change quote_ident's implementation—and then write new doc to describe 
the new behavior precisely and accurately? And for this option, the next 
question is "What's the spec of the changed implementation?"

Notice that the issue is broader than just quote_ident, as this test shows:

prepare x(text) as select format('« %s », gives « %I ».', $1::text, $1::text);
execute x('dog');
execute x('Dog');
execute x('农民');

The same over-zealous double-quoting that quote_ident shows for 农民 is shown by 
format. Presumably they share the same underlying implementation (but, 
surprisingly, don't re-use the actual SQL parser code). Option 2.1 implies 
using the same wording for what provokes double-quoting for each function. I'd 
make a similar argument for option 2.2.

*MORE DETAIL*

About option (2.2), I mentioned that ORCL's equivalent to quote_ident 
implements a simpler rule: the text of the identifier that it returns is always 
surrounded with double quotes, whether or not doing this is necessary. The ORCL 
scheme relies on the fact that double-quoting when this isn't necessary is in 
no way harmful. Here’s pseudocode (presented as tested PL/pgSQL) for what a 
patched C implementation of quote_ident might do:

create function quote_ident_2(name in text)
  returns text
  language plpgsql
as $body$
declare
  i0 text not null := quote_ident(name);
  i1 text not null := regexp_replace(regexp_replace(i0, '^"', ''), '"$', 
'');
  ident  text not null := case(i1 = i0)
when true then '"'||i0||'"'
elsei0
  end case;
begin
  return ident;
end;
$body$;

Re David’s

> everything else being discussed just detracts attention from it.


I’m not convinced. The discussion has shown that some people are somewhat 
confused. For example, it was suggested that a name like this:

农民

ought to be double-quoted. A simple test shows that this isn’t the case. And it 
helps if everybody is clear about that.

There's also the question of use-cases. I've been forced to think a lot about 
SQL injection over the years. It's immediately obvious from reading any of the 
skimpiest blogs on the topic that the root cause is always faulty code that's 
been written by a confused developer. But it's very rare to see an account of 
the root cause whose wording uses carefully defined terms of art. (In fact, the 
notion of defining and using such terms of art has been resisted by 
contributors to this list.) If a future PG shipped with a built-in function 
like this:

function is_exotic(name in text) returns boolean

...with, of course, excellent documentation, then an outfit that decided to 
outlaw the use of exotic names (and this is almost always how the outcome 
emerges) could police adherence to the rule, database wide, (and cluster wide 
for global phenomena) with a few trivial tests against the relevant catalog 
relations, like this:

do $body$
begin
  assert not exists (select 1 from pg_class where is_exotic(relname));
end;
$body$;

A shipped "is_exotic" function would bring the secondary, but by no means 
insignificant, benefit, that the case for using “format” with "%I" or its 
"quote_ident" cousin could be grounded upon solidly defined, and named, 
notions. Like this example shows:

do $body$
declare
  stmt constant  text   not null := 'create table %s(n int);';
  n  text   not null := '';

  simple_names constant text[] not null := array['farmers', 'bønder', '农民', 
'农民、儿童', '农民，儿童'];

  exotic_names constant text[] not null := array['T', 'farmers and children', 
'"x', 'y"', '农民 & 儿童'];
begin
  foreach n in array simple_names loop
assert 

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

[Bryn Llewellyn]

> 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 
>> quoted?
> 
> From my perspective this thread seems to miss the essential purposes behind 
> quote_ident(). It is part of processing external/user input… Expecting an 
> arbitrary string to be equal to itself after it has been through string 
> processing code is risky unless that processing is part of the design, and 
> quote_ident() was never designed to be part of any such arrangement.
> 
> …If the designer decides to use non-ASCII characters in the identifier they 
> can… just double-quote those identifiers.
> 
> AFAIK we never really use quote_ident() except to process external input… 
> There is no surprise for me in the subject line. There is mild surprise the 
> question was asked.

Here's why I fell into the trap that I did.

(1) I used the Oracle RDBMS for a very long time. Now I’m working hard to 
forget everything that I learned there and learn the PG way instead. I’m 
frequently caught out.

(2) ORCL provides a function with the same aim as quote_indent. Its input is 
the text of a name. And its output is the text of a legal SQL identifier for 
that name. The output *always* starts and ends with a double quote. And why 
not? Sometimes the bare text of a name is perfectly acceptable as the text of a 
SQL identifier—and yet it’s still perfectly acceptable in that way when it's 
surrounded with double quotes.

(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 number of different examples along these lines:

select quote_ident('redaktør'); → "redaktør"
create table redaktør(n int); → table successfully created

The doc might well have said that quotes are often added when they're not 
needed. I know that the doc also says "to be used as an identifier in an SQL 
statement string". But the reference doc for a function should give a precise 
and accurate specification of the rule that derives the output value from the 
input value(s). (There might well be performance caveats.) As long as this is 
reliable, then the user is free to use the function for any purpose where the 
specified behavior fits the use case.

(4) I do have a genuine use case. I know that, usually, it's best to preface a 
question to a forum like this with a clear statement of what you want to 
achieve. Well, in this case I wanted to achieve a full correct understanding of 
what quote_ident does. I think that I have this now. I accept that no change 
will ever be made to the actual behavior of quote_ident (for example, to have 
it always surround the return text with double quotes). And I accept that 
nobody is motivated to change the docs to stop PG neophytes like me being 
mislead by taking the extant wording at face value.

B.t.w., my specific use case is best solved by spending just a little effort to 
write my own function to check my own, context-specific, spec of what defines a 
bad name. Of course, it uses PG's native regular expression functionality. I 
was simply distracted from that effort when I briefly wondered if the 
ready-made quote_ident might save me some effort. I very soon realized that it 
would not. But that the same time, I couldn't help noticing that its behavior 
was at odds with the doc. This ought to surprise anybody but the most cynical 
amongst us. So naturally I asked about this.

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

[Bryn Llewellyn]

> 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 point 
>> about resulst display, try "\d s.*" in "\t off" mode.
>> 
>> [Allow me to re-write my bext few words, for sport.]
>> 
>> But a SQL statement with the following text representation
>>  
>> drop table "s.silly name”
>> 
>> when presented to the psql CLI as a text command in its language by 
>> appending a semi-colon causes that program to respond with some text that 
>> tells me that there's no such table.
> 
> And, indeed, there isn't. Perhaps there's a table s."silly name”. It is 
> accidental if unfortunate that the  is quoted with ""'s in the 
> \d output...

I believe that you and I see things the same way, Karsten. Yes, it does seem at 
times that some things in PG are accidental—and sometimes prove to be 
unfortunate.

Naturally, my questions to this list concern what know that I don’t understand. 
(Who knows what I think that I understand—but don’t. And who knows what I don’t 
even suspect is there waiting for me to try to understand.) To err is human—and 
it’s human, too, to misunderstand something when the proper understanding seems 
to be counter-intuitive. In my case, I developed my intuitions in the context 
of a different RDBMS with notions and terms of art that differ very 
significantly from PG’s—even though the core SQL syntax and semantics are 
deceptively similar.

Maybe I should send posts to this list like this:

«
I just wrote and tested a PG implementation to do something I hadn’t done 
before. I was very impressed at how straightforward it was—and with how 
expressive of my intentions the text of my code seemed to be. Well done PG.
»

I do very often have that experience. But I’ve never seen a contribution along 
those lines in this forum—and I’ve formed the impression that it would be out 
of place.
 

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

[Bryn Llewellyn]

> 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 to say "the text of an identifier" when the 
context of discourse is established. But, yes, I agree, when I wrote this:

«
...the value of "quote_ident()" rests on the distinction between a name (what 
you provide with the function's actual argument) and an identifier (what it 
returns). 
»

that the context of discourse was indeed established. I should have made no 
such assumption and written this instead:

«
...the value of "quote_ident()" rests on the distinction between the text of a 
name (what you provide with the function's actual argument) and the text of an 
identifier (what it returns). 
»

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

[Bryn Llewellyn]

> 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 overdrive. SQL 
statements, and if-then-else programs, are platonic notions. Like in the famous 
example of the notion of Boston itself—as opposed to how it's denoted in 
different contexts. Some would refer to it by saying "Boston". Others would say 
"波士顿".

In our world, the phenomenon is illustrated by this (after authorizing as a 
superuser):

create role "my name" login;
create database db;
grant all on database db to "my name";

\c db "my name"

create schema s;
create table s."silly name"(n int);
select relname
from pg_class c inner join pg_roles r on c.relowner = r.oid
where r.rolname = 'my name';

This is the result (in "\t on" mode)

 silly name

So that's *three* different ways to denote the platonic notion that I had in my 
head, of a certain table in a certain schema in a certain database, before I 
typed anything

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 point 
about resulst display, try "\d s.*" in "\t off" mode. You'll see this:

  Table "s.silly name"
 Column |  Type   | Collation | Nullable | Default 
+-+---+--+-
 n  | integer |   |  | 

But this SQL text:

drop table "s.silly name";

tells me that there's no such table. It's all a matter of convention. In an 
alternative universe, maybe manifest string constants are rendered, with no 
delineation, in red text; and identifiers are rendered, again with no 
delineation, in green text. In another universe, all SQL and program 
composition is done by talking. Manifest string constants are rendered by 
shouting; and identifiers are rendered in a whisper. And why not...

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

[Bryn Llewellyn]

> 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 behavior is 
>> at least intuitive.
> 
> quote_ident is a good bit more conservative than the core lexer about what is 
> an "identifier character" --- it considers all non-ASCII characters as 
> requiring quoting, too.
> 
> For typical uses of quote_ident, I think this is good future-proofing: it 
> makes it very much less likely that something quote_ident decides not to 
> quote would be rejected by some future PG version (not to mention non-PG SQL 
> databases). So I'm not really in a hurry to change the code. Maybe we should 
> tweak the docs a bit.

Is the use of *any* non-Latin character in the name of a database object a 
corner case? I appreciate that some outfits require Latin object names because 
they don't want to make it hard to have a global development staff. But not all 
outfits follow such a rule.

It's a pity that the doc reference that I started with and the second one that 
David mentioned:

https://www.postgresql.org/docs/14/functions-string.html 

https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
 


don't x-reference each other. I had no reason to suspect that what I read in 
the dedicated account of quote_ident was something of a sketch—and that it 
(loosely speaking) has to be interpreted in the light of the second one. I 
appreciate that the PG doc aims for maximally terse, full-on DRY accounts. But 
neither of the two accounts gives a hint that quote_ident is less permissive 
than are PG's SQL and PL/pgSQL parsers themselves. This would have fixed it for 
me:

«
[quote_ident] Returns its input argument string suitably rendered to be used as 
an identifier in an SQL statement string. Rendering is not always needed and in 
some cases the return value is identical to the input value. The rules for when 
rendering is needed, and what form it takes, are explained in [x-ref]. Here are 
four examples:

  select quote_ident('eye'); → eye
  select quote_ident($$ab"cd'ef$$); → "ab""cd'ef"
  select quote_ident('øye'); → "øye"
  select quote_ident('我的桌子'); → "我的桌子"

The third and fourth examples show that quote_ident is much stricter than the 
rules explained in [x-ref] demand. They explain that for example, the SQL 
identifier for this role name:

  redaktør

is that bare name—with no rendering. Quote_ident's unnecessary strictness means 
that some tempting uses for the function are not viable. A test like this:

  if 'proposed_name' <> quote_ident('proposed_name') then ...

will give a false negative for large classes of proposed names.
»

About:

> good future-proofing: it makes it very much less likely that something 
> quote_ident decides not to quote would be rejected by some future PG version


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

After all, I'd guess that a great deal of SQL text and PL/pgSQL source text is 
typed by hand—and you don't usually see double quotes delineating every SQL 
identifier when this isn't needed. (And you almost never see double quotes 
delineating a SQL identifier where this rendering is necessary.)

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

[Bryn Llewellyn]

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 be case-folded). Embedded quotes 
are properly doubled.
»

B.t.w, the value of "quote_ident()" rests on the distinction between a name 
(what you provide with the function's actual argument) and an identifier (what 
it returns). Some of you flatly reject (borrowing a phrase from Tom) the 
distinction between these two terms of art. Oh well…

Try this:

create table dog$(n int); -- OK
create table $dog(n int); -- Bad
create table "$dog"(n int); -- OK

These outcomes are consistent with the rules that say when a proposed name 
needs to be double-quoted to form its identifier in a SQL statement (or 
PL/pgSQL source text).

So it's correct for this to return FALSE:

select '$dog' = quote_ident('$dog');

But it's incorrect w.r.t. "quotes are added only if necessary" for this to 
return FALSE:

select 'dog$' = quote_ident('dog$');

"format()" shows the same error when you use the %I placeholder. I suppose that 
"format()" and "quote_ident()" share the same underlying implementation.

select format('What happens with %I?', 'dog'); -- double quotes are not added
select format('What happens with %I?', 'dog$'); -- double quotes are added

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

[Bryn Llewellyn]

> 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 condition name is undefined_table 
> then you know what to do.

Mea culpa. I should have stressed that my code was meant to be a sketch rather 
than the real thing. So my use of "on commit delete rows" suits the "hard shell 
paradigm" that I described here:

https://www.postgresql.org/message-id/f0a23614-749d-4a89-84c5-119d4000f...@yugabyte.com

where the client code does:

  check out connection
  call a user-defined API subprogram
  release connection

and where I therefore want automatic check-out-duration session state.

In a different use case, I'd want session-duration session state. There. I'd 
use "on commit preserve rows".

About testing what "message_text" from "get stacked diagnostics msg" returns, 
yes… of course its sensitivity to the current choice of national language is a 
non-starter. I don't like to assume more than I have to. So I might say this:

  if msg !~ '"pg_temp.flag"' then

But, then again, I might decide that it's just too fussy.

I've seen this pattern in use:

  create temp table if not exists pg_temp.flag(val boolean not null) on commit 
delete rows;
  insert into pg_temp.flag(val) values(true);

But doing a DDL before every use of the session-state representation felt 
heavier than assuming that it's there and creating the table only if it isn't. 
But I haven't done any timing tests. Is the "create… if not exists" so 
lightweight when the to-be-created object does exist that I'm fussing over 
nothing?

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

[Bryn Llewellyn]

> 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[*], because doing so would encourage people to abuse such 
> variables as application state variables. I say "abuse" because the code 
> supporting such variables isn't really designed to support lots of them.

I hinted at a different approach in an earlier turn in this thread:

https://www.postgresql.org/message-id/35254b0b-6501-4cf6-a13f-76d03756c...@yugabyte.com

I sketched only how you might handle the case where the session state is just a 
single value—by using a one-row, one-column temporary table with "on commit 
delete rows". But the general approach is to use a two column temporary table 
for key-value pairs. This approach is what the PG doc sketches here:

«
43.13. Porting from Oracle PL/SQL
https://www.postgresql.org/docs/current/plpgsql-porting.html
Since there are no packages, there are no package-level variables either. This 
is somewhat annoying. You can keep per-session state in temporary tables 
instead.
»

(That article of faith, "there are no packages and there never, ever will be", 
saddens me.)

Because PG has no event trigger that fires on session creation (why is this?), 
I've settled on this optimistic pattern:

begin
  insert into pg_temp.flag(val) values(true);
exception when undefined_table then
  get stacked diagnostics msg = message_text;
  if msg != 'relation "pg_temp.flag" does not exist' then
raise;
  else
create temp table pg_temp.flag(val boolean not null) on commit delete rows;
insert into pg_temp.flag(val) values(true);
  end if;
end;

The code would need to be more elaborate (and use "upsert") for key-value 
pairs. But that's easy to do.

Do the experts on this list disapprove of this pattern and prefer (for a future 
regime) something like the Pavel Stehule scheme that Tom mentioned?

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

[*] I didn't know that there was a PG doc policy sometimes not to call out a 
bad practice but, rather, to hide away (in an obscure backwater in the docs) 
the account of a feature that’s considered to be better avoided except in 
special cases. This effectively hides it from Google search (and similar) too 
because of the circular notion that few people find it, and fewer still publish 
pages that include the link,… and so on.

I suppose that calling the thing an "option" while the doc for the "set" SQL 
statement uses the term of art "run-time parameter" is another “bad practice 
admonition by obscurity” notion. (I've referred to the thing as a "user-defined 
run-time parameter" in informal emails to colleagues. But that is a lot of 
syllables.)

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

[Bryn Llewellyn]

> 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 
>> primitives that help the implementation of this next, application-specific, 
>> roles-and-privileges regime.
> 
> Can you provide a link to documentation (hopefully high-level and concise) of 
> such a system? I'm having a hard time imagining one which I wouldn't either 
> classify as "database roles by another name" or "just data".

I fear that we might be about to start another round of mutual 
misunderstanding. I’m not confident that I understand the question.

I believe that you want x-refs to accounts of native RDBMS features that let 
you implement application-level specific notions of security on top of the 
basic roles and privileges features and that are oriented to the case where a 
single RDBMS role, "client", must expose differentiated functionality to 
different human end-users—where these principals are identified by, and 
authorized by, system(s) outside of the RDBMS in question. In such a world, the 
RDBMS gets a suitable ID for the human (by all means suitably encrypted) and 
can use this to advantage by consulting its own representation of the current 
human's identity.

If my guess about your question is wrong, then I'm wasting my time. But I'll 
carry on anyway.

This is the canonical example:

—A manager must be able to see the salaries of all employees in the reporting 
hierarchy under her/him—but not be able to see salaries outside of that subtree.

Imagine the usual "employees" table with the pig's ear "manager_id" FK. But add 
an extra column for each employees external-to-the-database unique ID (unless 
this is already used as the table's PK).

PG has a native feature for this: row level security (hereinafter RLS). You can 
Google for blog posts about this use case. Here's one by a colleague of mine, 
Franck:

https://dev.to/yugabyte/hyper-scale-multi-tenant-for-saas-an-example-with-pgbench-33a3
 


It uses the multi-tenant use case rather than my "managers-and-salaries" one. 
But the principles are what matter. Never mind the detail. The critical 
table(s) are striped with "tenant_id". And this is passed from the application 
tier to the PG tier with:

set rls.tenant_id=42;

Using the "hard shell" paradigm, "rls.tenant_id" could be a formal argument to 
an API subprogram. And its within-database representation could be a one 
column, one row temporary table with "on commit delete rows".

Franck's PG RLS  policies are created like this:

create policy... on ... for... 
using(tenant_id=current_setting('rls.tenant_id')::int);

The argument of "using()" is just an arbitrary SQL expression that evaluates to 
a boolean.

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

So in the "managers-and-salaries" use case, it could be "employee_id in ()".

I'm afraid that I don't know what you mean by « classify as "database roles by 
another name" or "just data" ». For me, the RLS scheme is simply the native PG 
feature that you use to get the result that you want. But the context of these 
examples is very definitely a connection pooling regime that uses the single 
role "client" on behalf of (very) many different human users.

Finally, in case your question sought an account of some real-world scheme in 
this general use case space, a bit of Googling for  « Salesforce multi-tenancy 
» got me this:

Platform Multitenant Architecture
https://architect.salesforce.com/fundamentals/platform-multitenant-architecture 


It's public knowledge that they use Oracle Database (or at least did—I haven't 
thought about them recently). But the general principles have their equivalents 
in PG too.

Salesforce engineers have also talked at Oracle User group conferences about 
how they use PL/SQL. Their main multi-tenant implementation (at least at the 
time of these talks) followed the "hard shell" paradigm.

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

[Bryn Llewellyn]

> 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 given the credentials to 
>> start a database session is trusted—even a person who can connect as a 
>> superuser and do untold harm. So focus on a person who has the credentials 
>> to connect as "client" in my example. But imagine a design that exposes 
>> functionality to "client" sessions exclusively through a carefully designed 
>> and implemented API that's expressed exclusively with user-defined functions 
>> and procedures.
> 
> Sure. That is called an application server. What we are is a SQL server, and 
> that means that the API is SQL commands, and the authorization model is what 
> the SQL spec says it is. [So]... any session authenticated as user X has the 
> same privileges as any other session authenticated as user X, so there is not 
> a lot of point in user X mounting defenses against user X. So I think the 
> concerns you're expressing here would be better addressed at the next level 
> up the stack.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
Julien replied to the same message from me that Tom did thus:

> rjuju...@gmail.com wrote 
> 
> You mean like if the application takes care of checking that the logged-in 
> user is allowed to insert data based on whatever application defined rules / 
> user profile, while the SQL role can simply insert data and/or call the 
> carefully written functions? Yes the data will be consistent, but if your 
> role just transferred money from an account to another that's not really the 
> problem.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
Peter Holzer replied to an earlier message fro be in this branching thread thus:

> hjp-pg...@hjp.at wrote:
> 
> I think you did [misunderstand Tom]. What he was saying was that a database 
> design which uses only one single role which both owns all the objects and 
> executes all code on behalf of the user can not be secure. At the very least 
> you need two roles: One which owns the objects and one which can only use the 
> objects in a way allowed by the business logic and is not allowed to change 
> that logic (This is the case you demonstrated.)

Yes, indeed it is. That was my aim.

> In many cases this should be even more fine-grained, and at the extreme end 
> every user could actually have several roles, each with only the minimal 
> privileges required.
> 
> (I have often observed that we tend to throw away and build permission 
> systems at every layer of the stack: The OS has a system of users and 
> permissions. But the database server runs as a single user (postgres) which 
> has access to all the data files. So it has to implement its own system of 
> roles and permissions. Then an application developer comes along and writes 
> an app which uses a single database role which has access to all the data.

It often is done like this. But it doesn't have to be done this way. This is 
where a user-defined within-RDBMS functions and procedures, and so-called "run 
authority" in the wider discourse, come to the rescue.

> So it again has to implement its own system of users and permissions...)
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

*SUMMARY*

I'd better simply attempt to stop further discussion in this thread by saying 
that no mutual understanding has been reached. I'm sad about this outcome. But 
it would seem to be unfair to invite anybody to spend any more time on these 
exchanges.

*MORE... ONLY IF YOU CAN BEAR TO READ IT*

First, lest a really awful misunderstanding lingers on, I must stress that, 
about this from Tom:

> any session authenticated as user X has the same privileges as any other 
> session authenticated as user X


well, yes. That statement of outcome is a tautology. I don't think that I wrote 
anything to say that I thought otherwise. About the notion that Ms. Jones, 
authorized as X can kill Ms. Smith's session authorized as X, and vice versa... 
this seems now to be just a red herring. Either the designer wants this or they 
 don't. And if they don't, they can prevent it by a simple revocation of one 
object privilege. It's been argued that some other evil doing can't be 
prevented. Well, fair enough. But this doesn't seem to justify not preventing 
any bad thing that *can* be prevented. 

There was an era, now long gone, when an outfit had its own production RDBMS 
(or a small few of these) in a locked and guarded room in the very same 
building within which all human end users of such a system worked. In those 
days, each human user was mapped one-to-one to its own within-RDBMS role. And 
the application under the end-users fingertips connected directly to the RDBMS 
by 

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

[Bryn Llewellyn]

> 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 
>> purpose, from dropping tables and otherwise arbitrarily corrupting stuff. I 
>> expect that I completely misunderstood his point. But, anyway, that's what I 
>> responded to.
>> 
>> Now it seems that you, Julien, are not convinced that the code that I showed 
>> prevents a session that authorizes as "client" from dropping the table, 
>> owned by "u1", where the data is. Nor are you convinced that a "client" 
>> session is prevented from inserting mixed or upper case data, updating 
>> existing data, or deleting existing data. Rather (as your Bobby Tables 
>> reference indicates) you think that a cunning SQL injection attack can 
>> manage to do these bad things.
>> 
>> Well... the challenge is yours now: prove your point with some working code.
> 
> I'm convinced that that authorization system works as expected, what I'm not 
> convinced of is that the authorization system can prevent an untrusted user 
> with a direct SQL access from actually hurting you. So yes in your case maybe 
> the "client" role cannot drop the showed table, but it can still insert 
> nonsensical data, from a client point of view, or lead to outage or other 
> problems without any difficulty, and there's nothing in the authorization 
> system that can prevent that.
> 
> I'm also not convinced that your demo is proving anything, as "inserting any 
> only value made of non-uppercase characters in a single table" isn't really 
> representative of any basic application, especially without knowing what that 
> data will be used for.
> 
> The only case this example could make sense would be a log application, and 
> then a direct SQL access you can insert nonsensical or malicious data, 
> depending on what the application will do with those data (which could lead 
> to crash in the client application, or make it do thing it shouldn't do).

My example wasn't meant in any way to be realistic. I'm sorry if I didn't make 
that clear from the outset. It was meant only to illustrate the principles. For 
example, the "lower case only" rule was meant to be an example of *any* data 
rule. Just like the write-once-read-many auto-generated surrogate primary key 
rule. Can you show me how those data rules, unrealistic as you might think them 
to be, can be violated?

> 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 given the credentials to 
start a database session is trusted—even a person who can connect as a 
superuser and do untold harm. So focus on a person who has the credentials to 
connect as "client" in my example. But imagine a design that exposes 
functionality to "client" sessions exclusively through a carefully designed and 
implemented API that's expressed exclusively with user-defined functions and 
procedures. And choose something to model that meets your criteria for realism. 
Then show me, using a self-contained code example, how a session that 
authorized as "client" can cause the hurt that concerns you. Notice that "hurt" 
must be taken to mean having the persistently stored data no longer satisfying  
as specified business rule. And not anything to do with denial of service based 
on unconstrained resource consumption.

If, when I review it, I can see how to change the code to remove the 
vulnerability, then you'll have learned something. On the other hand, if you 
can show me a vulnerability that cannot be fixed, then I'll have learned 
something! I'm selfishly more interested in that second outcome because my 
overall mental model will have been improved.

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

[Bryn Llewellyn]

> 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 database operations that the 
>> database design specified. Am I missing something? In other words, can 
>> anybody show me a vulnerability?
> 
> [Julien] What exactly prevents the client role from inserting e.g.
> 
> - 'robert''); drop table students; --'
 
 [Peter] It can do this but it won't do any harm since the client role 
 doesn't have permission to drop the table.
> 
> [Julien] For the record. it's a reference to [XKCD's "Little Bobby Tables" 
> cartoon]. Both of you are saying it's harmless because you're assuming that 
> only the client role may read the data and act on it, but the whole point of 
> SQL injection is to try to do actions that the role you have access to can't 
> already do. And that's just a few out of dozens of examples of how having a 
> role connected to the database can do harm.
> 
> [Julien]
> - millions of 'cat' rows
> - millions of 1GB-large rows
 
 [Peter] That depends on "the database operations that the database design 
 specified", but if the client role is supposed to be able to insert data, 
 you can't really prevent it from inserting non-sensical or enormous data. 
 You can encapsulate the insert functionality in a function or procedure 
 and do some sanity checks there. But automatically distinguishing between 
 legitimate use and abuse is generally not simple.
> 
> [Julien] Which is exactly what was the idea behind Tom's "if you don't trust 
> another session that is running as your userID, you have already lost".
> 
> [Julien] or just keep sending massive invalid query texts to fill the 
> logs, or just trying to connect until there's no available connection 
> slots anymore, and then keep spamming the server thousands of time per 
> second to try to open new connections, or ...?
 
 [Peter] There are often several layers of defense. The database frequently 
 won't be accessible from the open internet (or even the company network) 
 directly. Only a middle tier of application servers running vetted client 
 code will connect directly. Even those servers may not be accessible 
 directly to end users. There may be a layer of proxy servers above them. 
 Each of these layers may implement additional checks, rate limits and 
 monitoring.
> 
> [Julien] If no one has direct SQL access to the database, then there's no 
> problem with a role being able to pg_terminate_backend() session for the same 
> role, and this thread shouldn't exist to begin with.
> 
>> [Bryn] I'm afraid that I didn't see this from you until I'd already replied 
>> to Julien's turn in this thread. Sorry that I caused thread divergence. 
>> Thanks, Peter, for addressing the contribution(s) that other tiers in the 
>> stack make (and uniquely are able to make) in order to deliver the intended 
>> application functionality to the end user.
> 
> [Julien] Your whole argument in your other email was:
> 
>> [Bryn] Anyway... this kind of denial of service discussion is way outside 
>> the scope of what I addressed.
> 
> [Julien] which looks like in total contradiction with your original email:
> 
>> Am I missing something? In other words, can anybody show me a vulnerability?
> 
> [Julien] Again, don't give SQL access to untrusted users and you will avoid a 
> lot of problems, including someone abusing pg_terminate_backend().

I fear that you and I, Julien, are talking past each other. That's probably my 
fault. Any maybe there's no hope of rescue now.

My brand new thread, started here:

https://www.postgresql.org/message-id/3d119733-6784-4e84-98e4-5124e69d4...@yugabyte.com

has nothing whatsoever to do with pg_terminate_backend(). The thread that I 
started here:

https://www.postgresql.org/message-id/10f360bb-3149-45e6-bffe-10b9ae31f...@yugabyte.com

to ask about pg_terminate_backend() reached a nice "case closed" for me because 
I filled an embarrassing hole in my understanding. I see now that, in a 
database of interest, I can revoke execute on the "killer" proc and grant it to 
any role that needs it. Doing this is nothing other than following the 
principle of least privilege. It's liberating to know that "Thing X" that you 
don't need can be made impossible. And so much nicer than applying the 
"what-about-ism" approach: "Thing X" brings some risks. But so what? "Thing Y", 
and its legion cousins, bring risks too—so don't worry about preventing "Thing 
X".

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 purpose, from 

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

[Bryn Llewellyn]

> 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 words, can anybody show me a 
>>> vulnerability?
>> 
>> What exactly prevents the client role from inserting e.g.
>> 
>> - 'robert''); drop table students; --'
> 
> It can do this but it won't do any harm since the client role doesn't have 
> permission to drop the table.
> 
>> - millions of 'cat' rows
>> - millions of 1GB-large rows
> 
> That depends on "the database operations that the database design specified", 
> but if the client role is supposed to be able to insert data, you can't 
> really prevent it from inserting non-sensical or enormous data. You can 
> encapsulate the insert functionality in a function or procedure and do some 
> sanity checks there. But automatically distinguishing between legitimate use 
> and abuse is generally not simple.
> 
>> or just keep sending massive invalid query texts to fill the logs, or just 
>> trying to connect until there's no available connection slots anymore, and 
>> then keep spamming the server thousands of time per second to try to open 
>> new connections, or ...?
> 
> There are often several layers of defense. The database frequently won't be 
> accessible from the open internet (or even the company network) directly. 
> Only a middle tier of application servers running vetted client code will 
> connect directly. Even those servers may not be accessible directly to end 
> users. There may be a layer of proxy servers above them. Each of these layers 
> may implement additional checks, rate limits and monitoring.

I'm afraid that I didn't see this from you until I'd already replied to 
Julien's turn in this thread. Sorry that I caused thread divergence. Thanks, 
Peter, for addressing the contribution(s) that other tiers in the stack make 
(and uniquely are able to make) in order to deliver the intended application 
functionality to the end user.

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

[Bryn Llewellyn]

> 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 
>> vulnerability?
> 
> What exactly prevents the client role from inserting e.g.
> 
> - 'robert''); drop table students; --'
> - millions of 'cat' rows
> - millions of 1GB-large rows
> 
> or just keep sending massive invalid query texts to fill the logs, or just 
> trying to connect until there's no available connection slots anymore, and 
> then keep spamming the server thousands of time per second to try to open new 
> connections, or ...?

My little code example was meant to show the basic principle: that the 
within-database artifacts that implement an application's backend can all be 
hidden from client code. The technique is simple and has been in use forever. 
That's why RDBMSs like PG have a privilege scheme. Object ownership and 
"current_role" are a central notions in any such scheme.

My demo depended upon an ordinary human regime of password secrecy. The key 
point that my demo made was that "client" owns nothing, lacks the "create" 
privilege on the database in question, and (for good measure) lacks "create" on 
all schemas in the database. Therefore, a session that authorizes as "client" 
is limited in what it can do.

I'm not sure what you mean to say with this fragment:

 'robert''); drop table students; --'

It rather looks like something that you see in an essay about SQL injection. 
But the entire SQL injection discussion is out of scope in my toy demo because 
the requirements statement simply allows a session that's authorized as 
"client" to issue any SQL statement. I don't know why you picked the "students" 
table when there isn't one. I just ran my demo code to completion, re-connected 
as "client", and did this:

drop table students;

It produces the "42P01: error: table "students" does not exist. Of course, the 
message isn't lying. So this is a better test:

drop view s.v;

This produces the "42501" error: must be owner of view v. This isn't a lie 
either. The hacker has now learned that, at least, such a view does exist. 
Arguably, the different between the two errors is a bad thing. And famously, in 
Oracle Database, you get a more generic "computer says no" in both cases. But 
PG is the way it is here and won't change in my lifetime. So, playing the 
hacker role, I tried this:

select definition from pg_views where schemaname = 's';

It caused the "42501" error: permission denied for view pg_views. And why 
shouldn't it? I didn't mention that I'd revoked "select" on every "pg_catalog" 
relation (and every "information_schema" relation) from public and then granted 
"select" explicitly on each to "u1" but not to "client".

This is the text-book principle of least privilege: you start with nothing and 
add what you need. For historical reasons, very few systems honor this 
principle by default. But it's an excellent feature of PG that you can overrule 
the default in the way that I described. The present toy demo works fine (all 
the tests behave the same) after my hardening intervention.

About inserting millions of rows, well... that's a word-game. The spec for my 
toy demo never mentioned that inserting millions of rows should be prevented.

There's only so far that you can go if you decide to articulate the hard-shell 
API as "use any SQL statement that you care to in order to access the intended 
app functionality". This is why the usual paradigm is to grant only "execute" 
on a designed set of subprograms that each implements a specified *business* 
function. People have been banging on about this approach since the late 
eighties (and probably since before then). Of course, the approach depends on a 
designed use of a privilege scheme. PG supports all this nicely. It's easy to 
implement an upper limit (in if-then-else code) on the number of rows that a 
procedure that implements "insert" allows. I s'pose that you'll say that the 
bad guy could call the procedure time and again. But techniques are available 
there too. (They're roughly analogous to what stops you making withdrawals from 
a bank account when the credit limit is reached.) Blocking a single "huge" row 
is trivial. Probably, a constraint that uses a SQL expression would suffice. 
But you can always implement the user-defined function for the hugeness test if 
you need to.

This leaves us with some kind of denial of service attack that uses a flavor of 
busy work or similar, like you mention. I don't think that there's any way that 
PG can prevent a connected role doing this:

do $body$
begin
  loop

  end loop;
end;
$body$;

or, say, a "select" with a recursive CTE with no stopping condition. There's 
always "set statement_timeout"—but that's in the hands of the session that 
authorizes as "client". I know of another