public schema grants to PUBLIC role

[Dominique Devienne]

Hi. I've recently realized via a post (or article?) from Laurenz that the
PUBLIC role has CREATE privilege on the 'public' schema by default (see
query below). I guess it can't be avoided?

OK, then I'll REVOKE that privilege when creating a new DB.
Like I already revoked the default CONNECT to PUBLIC on the DB.

But I'm wondering about unexpected side-effets.
In particular, we need extensions, which are loaded in public by default.
Will USAGE of public be enough for LOGIN users having access to the DB to
use extensions?

More broadly, we want to secure the DB so that all DB access and schema
access are explicit.
Anything else to be aware of please, beside the two mentioned above?

Thanks, --DD

```
=> select grantor::regrole::text, case grantee when 0 then 'PUBLIC' else
grantee::regrole::text end, privilege_type as priv, is_grantable as adm
from pg_namespace, lateral aclexplode(nspacl) where nspname = 'public';
 grantor  | grantee  |  priv  | adm
--+--++-
 postgres | postgres | USAGE  | f
 postgres | postgres | CREATE | f
 postgres | PUBLIC   | USAGE  | f
 postgres | PUBLIC   | CREATE | f
(4 rows)
```

Re: public schema grants to PUBLIC role

[Christoph Moench-Tegeder]

## Dominique Devienne (ddevie...@gmail.com):

> Hi. I've recently realized via a post (or article?) from Laurenz that the
> PUBLIC role has CREATE privilege on the 'public' schema by default (see
> query below). I guess it can't be avoided?

You could just use PostgreSQL 15:
https://www.postgresql.org/docs/15/release-15.html#id-1.11.6.7.4

> In particular, we need extensions, which are loaded in public by default.
> Will USAGE of public be enough for LOGIN users having access to the DB to
> use extensions?

Plus any grants on the extension's object.

> More broadly, we want to secure the DB so that all DB access and schema
> access are explicit.
> Anything else to be aware of please, beside the two mentioned above?

Have a look at default privileges and group roles, that will make your
life much easier.
https://www.postgresql.org/docs/15/ddl-priv.html

Regards,
Christoph

-- 
Spare Space.

RE: Blog post series on commitfests and patches

[Tim Clarke]

That’s excellent Chris, thanks very much for doing that. Concise, informative 
and targeted. Is there a mailing list for updates?

--
Tim Clarke BSc (Hons), MBCS
IT Director
Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420
From: Chris Travers 
Sent: Thursday, March 9, 2023 3:24 AM
To: pgsql-generallists.postgresql.org 
Subject: Blog post series on commitfests and patches

Hi all;

I have been writing a few blog posts trying to shed some light on the 
development process of PostgreSQL , what's coming and what I hope we see more 
of.

I would be very much interested in feedback as to whether people (particularly 
non-Postgres contributors) find this useful or not.

The latest entry is at:
https://www.timescale.com/blog/a-postgresql-developers-perspective-five-interesting-patches-from-januarys-commitfest/?utm_source=timescaledb&utm_medium=linkedin&utm_campaign=mar-2023-advocacy&utm_content=tsdb-blog


--
Best Wishes,
Chris Travers

[https://i0.wp.com/www.manifest.co.uk/wp-content/uploads/2022/12/Minerva-Analytics-Logo-PORTRAIT.png]

[https://i0.wp.com/www.manifest.co.uk/wp-content/uploads/2022/12/esg_finalist.png]



Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: 
+49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848
Web: https://www.manifest.co.uk/

Watch our latest Minerva Briefings on 
BrightTALK



Minerva Analytics Ltd - A Solactive Company
9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom



Copyright: This e-mail may contain confidential or legally privileged 
information. If you are not the named addressee you must not use or disclose 
such information, instead please report it to 
ad...@minerva.info
Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: 
Registered in England Number 11260966 & The Manifest Voting Agency Ltd: 
Registered in England Number 2920820 Registered Office at above address. Please 
Click Here https://www.manifest.co.uk/legal/ for further information.

crosstab

[Rosebrock, Uwe (Environment, Hobart)]

Hi List
I like to use crosstab to pivot a long list of rows into columns, however the 
column labels are created dynamically with the resulting column type known.

Is there a way pass a list of names and types to crosstab (‘query’) as (<‘type 
list as strings in array’> ) ?
In other words,
Can I pass an array to crosstab row labels

Cheers
Uwe

Re: public schema grants to PUBLIC role

[Laurenz Albe]

On Thu, 2023-03-09 at 10:34 +0100, Dominique Devienne wrote:
> Hi. I've recently realized via a post (or article?) from Laurenz that the 
> PUBLIC
> role has CREATE privilege on the 'public' schema by default (see query below).
> I guess it can't be avoided?

It can be avoided if you connect to "template1" and

  REVOKE CREATE ON SCHEMA public FROM PUBLIC;

there *before* you create a new database.

Or, as Christoph said, if you use v15 or better.

> OK, then I'll REVOKE that privilege when creating a new DB.
> Like I already revoked the default CONNECT to PUBLIC on the DB.

Excellent.

> But I'm wondering about unexpected side-effets.
> In particular, we need extensions, which are loaded in public by default.
> Will USAGE of public be enough for LOGIN users having access to the DB to use 
> extensions?

Yes, that is enough.

> More broadly, we want to secure the DB so that all DB access and schema 
> access are explicit.
> Anything else to be aware of please, beside the two mentioned above?

Avoid SECURITY DEFINER functions with no "search_path" set:
https://www.cybertec-postgresql.com/en/abusing-security-definer-functions/

Yours,
Laurenz Albe

Re: crosstab

[David G. Johnston]

On Wednesday, March 8, 2023, Rosebrock, Uwe (Environment, Hobart) <
uwe.rosebr...@csiro.au> wrote:

> Hi List
>
> I like to use crosstab to pivot a long list of rows into columns, however
> the column labels are created dynamically with the resulting column type
> known.
>
>
>
> Is there a way pass a list of names and types to crosstab (‘query’) as
> (<‘type list as strings in array’> ) ?
>
> In other words,
>
> Can I pass an array to crosstab row labels
>
>
No, you cannot change query structure at execution time in SQL.

David J.

find coumn name and his type with greatest size in bytes

[Алексей Ефимов]

Hello all!
can you help, i need to write a query that displays column name with his
data type that have maximum bytes length in row for every table in database:

for example, for table employees with data:
emp_id,  emp_name, emp_dept, emp_surname
1  alexey 2trew

i need to get such output:

table_name, emp_id,  column_name,  bytes, datatype
employees  1   emp_name   6 text

Re: public schema grants to PUBLIC role

[Dominique Devienne]

On Thu, Mar 9, 2023 at 2:13 PM Laurenz Albe 
wrote:

Hi,

On Thu, 2023-03-09 at 10:34 +0100, Dominique Devienne wrote:
> > Hi. I've recently realized via a post (or article?) from Laurenz that
> the PUBLIC
> > role has CREATE privilege on the 'public' schema by default (see query
> below).
> > I guess it can't be avoided?
>
> It can be avoided if you connect to "template1" and
>
>   REVOKE CREATE ON SCHEMA public FROM PUBLIC;
>
> there *before* you create a new database.
>

Right. Didn't think of that. Thanks.


> Or, as Christoph said, if you use v15 or better.
>

Because Managed Azure is still stuck at 14.2, that's currently not an
option.
We need both on-prem and managed Azure.


> > More broadly, we want to secure the DB so that all DB access and schema
> access are explicit.
> > Anything else to be aware of please, beside the two mentioned above?
>
> Avoid SECURITY DEFINER functions with no "search_path" set:
> https://www.cybertec-postgresql.com/en/abusing-security-definer-functions/


Thanks for the reminder. We already set "search_path" on our functions,
because the client code does not always set the search_path at all,
which was resulting in errors. No DEFINER functions either, yet.

I also plan to look at the new function syntax, that eagerly resolve
references
at DDL time, rather than runtime, to avoid the search_path dependency at
runtime completely.
Although I'm worried about the introspection rewriting already discussed
recently... --DD

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 := the_row.c1::

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

[David G. Johnston]

On Thu, Mar 9, 2023 at 12:46 PM Bryn Llewellyn  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...

David J.

Onfly, function generated ID for Select Query

[Durumdara]

Dear Members!

I'm searching for a simple solution, like this:

select GetIDForThisSelect(1, 1) as UID, * from anytablehasdoublestringkey
join othertablehasnotuniqueintegerkey ...
where 

Ok, I can make a sequence, but I don't want to use persistent data.
It is enough to get 1...N as UID for this select.

Like generate_series, but that is generating rows.

Maybe you have some trick to do this.

Thank you for any assistance!

Best regards
dd

SV: Onfly, function generated ID for Select Query

[Gustavsson Mikael]

Hi,


Is it something like row_number() you want?

https://www.postgresql.org/docs/current/functions-window.html

KR
Mikael

Från: Durumdara 
Skickat: den 10 mars 2023 8:12
Till: Postgres General
Ämne: Onfly, function generated ID for Select Query

Dear Members!

I'm searching for a simple solution, like this:

select GetIDForThisSelect(1, 1) as UID, * from anytablehasdoublestringkey
join othertablehasnotuniqueintegerkey ...
where 

Ok, I can make a sequence, but I don't want to use persistent data.
It is enough to get 1...N as UID for this select.

Like generate_series, but that is generating rows.

Maybe you have some trick to do this.

Thank you for any assistance!

Best regards
dd

Get more columns from a lookup type subselect

[Durumdara]

Dear Members!

I use the subselects many times in my Queries to get some info (Name, etc)
from a subtable.

Sometimes I have to use it to get the last element.

select t.*,
   (

select value from u join ... where ...

order by id desc limit 1

   ) as last_value,

It is ok, but how can I get more values from subselect without repeating
the subquery?

select t.*,
   (

select value from u join ... where ...

order by date desc limit 1

   ) as last_value,
   (

select type from u join ... where ...

order by date desc limit 1

   ) as last_type,

This is not too comfortable, and may make mistakes if the join is not
defined properly or the date has duplicates.

Ok, I can use WITH Query:

with
  pre as ( select * from t  )
  ,sub as (select pre.*, (select u.id from u where ... limit 1) as last_u_id
select  sub.*, u.value, u.type, u.nnn from sub
left join u on (u.id = sub.last_u_id)

But sometimes it makes the Query very long (because I have to read more
subselects).

Do you know a simple method for this, like:

select t.*,
   (

select value, type, anyfield from u join ... where ...

order by date desc limit 1

   ) as last_value, last_type, anyfield

?

Thank you for the help!

Best regards
Chris

Re: Onfly, function generated ID for Select Query

[Durumdara]

 Dear Mikael!

Wow... that is it!
Thank you!

SELECT product_id, product_name, group_id, ROW_NUMBER () OVER (ORDER BY
product_id) FROM products;

Does this mean that I have to duplicate the order by clause?

SELECT product_id, product_name, group_id, ROW_NUMBER ()
OVER (*ORDER BY product_id, product_name, group_id*)
FROM products
*ORDER BY product_id, product_name, group_id*

To get the same sequence in the ROW_ID-s, and the Query rows?

BR
dd

Gustavsson Mikael  ezt írta (időpont: 2023.
márc. 10., P, 8:33):

> Hi,
>
>
> Is it something like row_number() you want?
> https://www.postgresql.org/docs/current/functions-window.html
>
> KR
> Mikael
> --
> *Från:* Durumdara 
> *Skickat:* den 10 mars 2023 8:12
> *Till:* Postgres General
> *Ämne:* Onfly, function generated ID for Select Query
>
> Dear Members!
>
> I'm searching for a simple solution, like this:
>
> select GetIDForThisSelect(1, 1) as UID, * from anytablehasdoublestringkey
> join othertablehasnotuniqueintegerkey ...
> where 
>
> Ok, I can make a sequence, but I don't want to use persistent data.
> It is enough to get 1...N as UID for this select.
>
> Like generate_series, but that is generating rows.
>
> Maybe you have some trick to do this.
>
> Thank you for any assistance!
>
> Best regards
> dd
>

SV: Onfly, function generated ID for Select Query

[Gustavsson Mikael]

No, the result will be ordered by the window functions order clause so no 
additional ordering is nessesary.
You can try this by changing it to OVER(ORDER BY product_name)


KR

Mikael


Från: Durumdara 
Skickat: den 10 mars 2023 08:41:06
Till: Gustavsson Mikael
Kopia: Postgres General
Ämne: Re: Onfly, function generated ID for Select Query

 Dear Mikael!

Wow... that is it!
Thank you!

SELECT product_id, product_name, group_id, ROW_NUMBER () OVER (ORDER BY 
product_id) FROM products;

Does this mean that I have to duplicate the order by clause?

SELECT product_id, product_name, group_id, ROW_NUMBER ()
OVER (ORDER BY product_id, product_name, group_id)
FROM products
ORDER BY product_id, product_name, group_id

To get the same sequence in the ROW_ID-s, and the Query rows?

BR
dd

Gustavsson Mikael mailto:mikael.gustavs...@smhi.se>> 
ezt írta (időpont: 2023. márc. 10., P, 8:33):

Hi,


Is it something like row_number() you want?

https://www.postgresql.org/docs/current/functions-window.html

KR
Mikael

Från: Durumdara mailto:durumd...@gmail.com>>
Skickat: den 10 mars 2023 8:12
Till: Postgres General
Ämne: Onfly, function generated ID for Select Query

Dear Members!

I'm searching for a simple solution, like this:

select GetIDForThisSelect(1, 1) as UID, * from anytablehasdoublestringkey
join othertablehasnotuniqueintegerkey ...
where 

Ok, I can make a sequence, but I don't want to use persistent data.
It is enough to get 1...N as UID for this select.

Like generate_series, but that is generating rows.

Maybe you have some trick to do this.

Thank you for any assistance!

Best regards
dd