Feedback-ID: i0fe9450f:Fastmail
Date: Fri, 28 Apr 2023 08:05:22 +0900
From: Michael Paquier <michael@paquier.xyz>
To: Richard Guo <guofenglinux@gmail.com>
Cc: hysong0101@163.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17909: CREATE SCHEMA AUTHORIZATION sch CREATE TABLE foo (
 id INT ) will coredump
Message-ID: <ZEr/shjg+8TClUm/@paquier.xyz>
References: <17909-f65c12dfc5f0451d@postgresql.org>
 <ZEolfwBHm6+znydH@paquier.xyz>
 <CAMbWs4-f5w+YCzqd90YAS-ogPxoahzfJ7C1QUUo8X+KE=0UC4w@mail.gmail.com>
 <ZEpA1SgNzEqhgCvA@paquier.xyz>
 <CAMbWs49CfOfrMp_AGPiBo7g7BusGf2gy1kGSatm-BYO5aeojvQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="OwadXfNbpl6ejhd1"
Content-Disposition: inline
In-Reply-To: 
 <CAMbWs49CfOfrMp_AGPiBo7g7BusGf2gy1kGSatm-BYO5aeojvQ@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/ZEr/shjg%2B8TClUm/%40paquier.xyz>
Precedence: bulk


--OwadXfNbpl6ejhd1
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Thu, Apr 27, 2023 at 06:28:28PM +0800, Richard Guo wrote:
> I noticed that in CreateSchemaCommand there is logic that fills schema
> name with the role name if it is not specified.  Do you think we can
> save the new-filled schema name into CreateSchemaStmt.schemaname there?

It's actually much trickier than that, on second look, as a RoleSpec
is embedded in this portion of a query because we need to support
CURRENT_ROLE, CURRENT_SESSION and SESSION_USER.  For example, this
query assigns neither role name nor schema name we could rely on at
transformation for the objects:
create schema authorization current_role create table aa (a int);

Anyway, semantically, something could go very wrong if we decide to
enforce a schema name for the objects based on the RoleSpec at the
time of transformation, because we may finish by executing the CREATE
SCHEMA command under an entirely different context than what we could
assign.  So, I think that we should do the following in the
transformation path if an object is schema-qualified:
- Fail immediately if there is no schema and no role name at hand,
just give up.  This needs a new error message, say:
"CREATE specifies a schema (%object_schema) without providing a schema
name."
- If there is a role name, aka RoleSpec points to a ROLESPEC_CSTRING,
use it as a comparison with the objects schema-qualified.  Note that
there is no case for public, because we would fail on
get_rolespec_oid() when the schema is created.

There is also a very fancy case, if "foo" matches to the role that
would be assigned by GetUserIdAndSecContext() when executing the
schema command:
create schema authorization session_role create table foo.aa (a int);

One could say that this should work, and my proposal would cause an
error to make the query more predictible at an earlier step.  IMO, I
think that this is just saner.  And this case crashes today like the
others.

Any thoughts or objections about doing that?
--
Michael

--OwadXfNbpl6ejhd1
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmRK/7IACgkQnvQgOdby
QH1fKA//eTbEvCpAiznNaXbU2Mv53Y03aeR28uKEbJ349OWEWwuY2WRKewL2HEJ4
cEw/HtEc/qwfKYvZX7iRbsC1f2/ASqtZsIjaZTAqNZDJ1GktfyXeZqLvoSiaMQIM
YUQEU0PfwAE8xUjSUyJ1pOKdCL+jmVuKj/IrtqVkShQqkY9Xx+TFohjy2XZSscMH
eifoDUe7Ez2Jz1FT2YUJlXsObUybqGsCHlDMfSHeBRLSzBZifYiEfza7GatWUojY
VICDpt3tFIr+zVIOWAKlEb2h935NSZKFTS6J5CVqQurQlOACWITPzIHf7MwM2tHP
MzGLPlVxfHQo7u3rREqmycWYgBgB7A3p82kRZeNk0LCDHW08xuLsx0TGGFaKW8Ms
PWhy90yMVDtXZerNr6+1g+YRvRfAaGlnVvKAQN9Hx8HWDPMms2bzx77Kh+WHBQUK
AA4lsUjO4LOPpFf1upKwuOLUW2iMN1PqADtIAmguCeKq+CVZbf+foQ/IdUjBVRjJ
0bJVKFNnxqDr6yF5F5mT5hIpaL30+H0ST4wnSWAORVakfzVu0FEb2wn2HnOacvMt
D30Ky8qI2e5Ra1ZqFEESZEAxn9mMnmD0KeSj9Ru0QwTp5mqUf9GQhofWFvkiCj2w
dzTO+0GrwzNm+vrbat0JhwvVFOfyTgI6dq2zwkhpD7dwr84kkFU=
=Dofh
-----END PGP SIGNATURE-----

--OwadXfNbpl6ejhd1--