Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ooDbJ-0003Ub-I8 for pgsql-general@arkaria.postgresql.org; Fri, 28 Oct 2022 00:51:57 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ooDbI-0007HT-85 for pgsql-general@arkaria.postgresql.org; Fri, 28 Oct 2022 00:51:56 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ooDbH-0007H4-73 for pgsql-general@lists.postgresql.org; Fri, 28 Oct 2022 00:51:55 +0000 Received: from out5-smtp.messagingengine.com ([66.111.4.29]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ooDbE-0007H1-8f for pgsql-general@lists.postgresql.org; Fri, 28 Oct 2022 00:51:54 +0000 Received: from compute5.internal (compute5.nyi.internal [10.202.2.45]) by mailout.nyi.internal (Postfix) with ESMTP id 12CB75C0090; Thu, 27 Oct 2022 20:51:51 -0400 (EDT) Received: from mailfrontend1 ([10.202.2.162]) by compute5.internal (MEProxy); Thu, 27 Oct 2022 20:51:51 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:cc:content-transfer-encoding:content-type:date:date:from:from :in-reply-to:in-reply-to:message-id:mime-version:references :reply-to:sender:subject:subject:to:to; s=fm2; t=1666918311; x= 1667004711; bh=qzrCHg2lEPQKoqnKhLD4pGCVjql6F3Vos9ZLzTiYP1w=; b=c umziaqglvqL1PQYD0GKv1KYnE2xLfCKlAqZkvnAy1X6JBFPALXekQGk80LvBbhgP 7Y89ogu7/6r7oYMJ0GFLj9qX8oSSMsULlKdH0PByx2zQvmoNJcwcqVMO7Gvkow8r vI+aOpBwApOh12RHwV7+MtrlKUY9pwt/HbXKGQDqyrZvaTS8kfhqpNYpZ4KhM7JE c+pTJEqqHhlycoBPss//k3YlXCJLf4sKGZs28a11J3YMfDRpm+I10U3zJCQ2LxPN O6N823h6sNuoj9v4K1W9eHPsLpLWNW4Z4po5Kgf8vPX+PjsysMT4D01IL9S7VyMw XPdqPSqG1RvgqosBI1iMw== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-transfer-encoding :content-type:date:date:feedback-id:feedback-id:from:from :in-reply-to:in-reply-to:message-id:mime-version:references :reply-to:sender:subject:subject:to:to:x-me-proxy:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm3; t=1666918311; x= 1667004711; bh=qzrCHg2lEPQKoqnKhLD4pGCVjql6F3Vos9ZLzTiYP1w=; b=c iNzSaGtzw2x9bdMvha5elF3HT/BUqPHONtSQ6DM6/ELoDHCY7HccM28ZydPgGpmg tG8BDdGPH0uX2N5GI6eQwD/KGx8o0O6WyWZmN9qDr/KfPqXrTmXxwr7GDkV5ohtI VxDfKingRZOINXESfRSKr/bvqx/WJt1k2JFuLTMVQ1iCW604TXHPTPiNPBOVmyf8 ObSDkahwf0GNQ+KsMWPC58dtsaTSmaYVYCusSQ3bmp25U8TkZBcxwLrvGPdzbZD0 xbzHvP2ThSFRSUoOAUZwSHb7snDtSqCBprlF4Rkgy6hMlwqE9lkjkCo9X/x09/Mf t2hSimoe0lA7STpVIfCpA== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvgedrtdehgdefkecutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfghnecu uegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmdenuc fjughrpefkffggfgfuvfevfhfhjggtgfesthekredttdefjeenucfhrhhomheptegurhhi rghnucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtoh hmqeenucggtffrrghtthgvrhhnpefhgeevkeefleffvdfgvdefffdtueejheehueeifffh feeihfdvhffgtefgvdeugfenucffohhmrghinhepphhoshhtghhrvghsqhhlrdhorhhgne cuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehmrghilhhfrhhomheprggurhhi rghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtghomh X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Thu, 27 Oct 2022 20:51:50 -0400 (EDT) Message-ID: Date: Thu, 27 Oct 2022 17:51:49 -0700 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.4.0 Subject: =?UTF-8?Q?Re=3a_Seeking_the_correct_term_of_art_for_the_=28unique?= =?UTF-8?Q?=29_role_that_is_usually_called_=22postgres=22=e2=80=94and_the_me?= =?UTF-8?Q?ntal_model_that_underlies_it_all?= To: Bryn Llewellyn , "David G. Johnston" Cc: Ian Lawrence Barwick , pgsql-general list References: <8A2C8B1B-470B-42F9-9694-C1EDDAC06266@yugabyte.com> <5C47A318-F265-4A64-B306-C4019F499DB8@yugabyte.com> Content-Language: en-US From: Adrian Klaver In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 10/27/22 17:20, Bryn Llewellyn wrote: >> david.g.johnston@gmail.com wrote: >> >>> bryn@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. The above is not contributing to the below(pg_hba.conf) and would be redundant any way as it just says OS user usr = Pg user usr and peer means that anyway. The purpose of mapping would be to do something like map OS user foo to PG user usr. References: https://www.postgresql.org/docs/current/auth-peer.html "map Allows for mapping between system and database user names. See Section 21.2 for details. " Section 21.2 "The map-name is an arbitrary name that will be used to refer to this mapping in pg_hba.conf." This example below id for the ident auth method but the same syntax applies to peer. https://www.postgresql.org/docs/current/auth-pg-hba-conf.html # TYPE DATABASE USER ADDRESS METHOD host all all 192.168.0.0/16 ident map=omicron > > (3) Add this line between the existing two in the shipped copy of > "pg_hba.conf" thus: > > local   all             postgres                                peer # > See the essay at the start. > local   all             usr                                     peer # > Added by Bryn > local   all             all                                     peer As noted above your pg_ident.conf will not do anything for the above. It will work though if you are logged in as OS user usr as it will connect as PG user usr. > > (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             postgres                                peer > > (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. > -- Adrian Klaver adrian.klaver@aklaver.com