From owner-freebsd-questions@freebsd.org Mon Oct 21 06:23:15 2019 Return-Path: Delivered-To: freebsd-questions@mailman.nyi.freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2610:1c1:1:606c::19:1]) by mailman.nyi.freebsd.org (Postfix) with ESMTP id A0FCA1750B0 for ; Mon, 21 Oct 2019 06:23:15 +0000 (UTC) (envelope-from dnewman@networktest.com) Received: from mail8.networktest.com (mail8.networktest.com [192.73.244.137]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (Client did not present a certificate) by mx1.freebsd.org (Postfix) with ESMTPS id 46xRQp3b3tz3KNt for ; Mon, 21 Oct 2019 06:23:13 +0000 (UTC) (envelope-from dnewman@networktest.com) Received: from mail8.networktest.com (localhost [127.0.0.1]) by mail8.networktest.com (Postfix) with ESMTP id 8DB76550295 for ; Sun, 20 Oct 2019 23:22:53 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=networktest.com; h=content-transfer-encoding:content-language:content-type :content-type:mime-version:user-agent:date:date:message-id :organization:subject:subject:from:from:to; s=dkim; t= 1571638973; x=1572243774; bh=JOY2TdwhlErs+2OCDjHvaX/b2IEd7xXrmF9 RsIRANvs=; b=lFz4Q0NAqJjHPUsJgP98iEuD2NF3RtrV+O0LEJnmqX6TMjlsbXZ mkFPIoNZANQSg1/6+23C2WhPD+QTGTm9UGmWnUmlJs0KS9eJL5CWNYqg/AZJPAZv mWr1DfpWxgsXczgEyreC6KDZxii50iI7Frej8KTXg8XpqXgXb4+5W2rM= X-Virus-Scanned: amavisd-new at mail8.networktest.com Received: from mail8.networktest.com ([127.0.0.1]) by mail8.networktest.com (mail8.networktest.com [127.0.0.1]) (amavisd-new, port 10026) with ESMTP id S1k32yojj9Kq for ; Sun, 20 Oct 2019 23:22:53 -0700 (PDT) Received: from tejay.local (cpe-75-82-86-131.socal.res.rr.com [75.82.86.131]) by mail8.networktest.com (Postfix) with ESMTPSA id 4AA1A550294 for ; Sun, 20 Oct 2019 23:22:53 -0700 (PDT) To: freebsd-questions@freebsd.org From: David Newman Subject: changing the PostgreSQL default user Autocrypt: addr=dnewman@networktest.com; prefer-encrypt=mutual; keydata= mQINBFnmfMEBEACt5x64cZy4FPct3SaH44BMAYkghlJDxI11XpHUJNFAuJ8MD8WgF5F6Nc9C PCl7WzIHAwgWkM2ntAgcOeKvBiFTtzMfiIczfgIAkhiyANYAm3MIH4Li9X0NltYtROQ7FUn+ H4IWZ0SF/BZH1jfoLRNVvuLHdXVYrol/9sGlSI1KTpzpt3GIgeTxdOgTy0nO0HO7c0EBTnRv L3fyYs2E9XgmqsHlFqHBvF1OV0HjVJsqohQxb9iReZEL2UseTZYIYOhKizI3oJOnwPmf7zYg E4jzL422tHbbj4Qwh8l4kZDvy3J+S+KmqFD+IvOUbFBmHZKKB8oFZvLrjd7PLRs0ORSaseqA di0TEUAC3RpmybB1iuukvYhayeLV9afwXWFGNTqrAGCrGPR9umP7OeKGIm6SHrmGnqMlAAz6 qfVITcbhdne2JiqLaZ23B1ezTmJwPPbDGi7G6Yk3Z5pzuZBgq/L1ZyenUBmIcx95C1kVmUtl POJJ3RUNXqJLkjfZhGcmQczR2U0WoOuHVWCmztxa3yhkQETjKhmlIkBV6pj2Bc/mtynVBZg8 XclC6f4Q9bADDOiwO6uDbqI8Bpa1X8YY7iL8QJy2JHACT/j/+mji6Er8c+xU9qkxNmjc3E2a KpCg7gIqnWFj/Ag76AHkPkIo2SO2QINyRMgnFMQeS6j7ij7BOwARAQABtEtEYXZpZCBOZXdt YW4gKERhdmlkIE5ld21hbiBOZXR3b3JrIFRlc3Qgd29yayBrZXkpIDxkbmV3bWFuQG5ldHdv cmt0ZXN0LmNvbT6JAj8EEwEIACkFAlnmfMECGwMFCQeGH4AHCwkIBwMCAQYVCAIJCgsEFgID AQIeAQIXgAAKCRB8vD6mldfDUqfvD/4zARgEeKZqNrJWOK9YMkIah6C6dMohRH0Pz9AkMz9+ 9i9VhdZf2Pv0hq+IEGw+6ISQZL/xiNmq4l6rBioU+AAq98CZq8sh0VLSSeEqYDbo9anvSu2W j1MzhC0BKhlaFPgy08EYZlN2z4pbSvG/ARc0xfTiGlU2jA+iEiMNDbHveGEH1iwAXV3JGA9T bIpe/D5pVO2aemTsePf6+OiATE7M3crqKYZX7WMejqMbOuXzWbeCh/HoDnBBSFDIHR3lQfqR 6jwvb/NogoBioMx6I9vtXvwkqVgp4/jxgkK+I1X4BCdXvC1s3qnJQ9lQT3kFPCJBhQ3qcORv Lvx5svBtp9undPvvp+7SEOBIZDyAfAtSRvBHSv86Y8XnTSrseqk/XtcQbsHs7eVAYcg7F0dR gXFNFUMg67VgacXfyi2GrmkIvA3Zh8h39pZBiTiKfizMhn10gFPhHI6zndR5mYVO5sGUVMVt Mw4f0MDiKHmgFZcs/zcO89OSx3vRwAn5YfBdCagrX1VPYAgGxsfPhnUT8BjmqAFLbHHpb23n HUtfepMIeNaN76/PcxQJUInx/9MfceKL/XGJ8uThM3Ujdv8x6IS7mzEqSbMQTqVVi7loIfP3 qqP87k4Z6G7IMNXKk/Mg84kLTq7YD48pm9S1ME+ghd7wraX2tcbMycuZsrE4XfI8ibkCDQRZ 5nzBARAA9fYZMETrO0l7alXxUVxYxLYVWulKh3GBz9yAeSXSEIJ+q8f0WsDK5zPd6ndBzY2k pT2DJ/ND1NfZJlEo5o27eETQJkpIMV38ZINZz/gVThMIIpCHZeSMW3JveZYEB1xYLFLxeILI ECBAO/h0ASdRz0KYLJldWmuWBUd3tsxhOpK/lZ/s5wbFpstAzsAiw0sm2de4UpNpmGHc1x1j eJH4WBKIAUkZLDXUWEkN1aSVMGWH/2yH87x/J/Mjrj49T+6RwwUohSgOQV3q1XjGjqDq/tpN BPODp32z9GchIvmCU4cRF0uQKpUXtu0fpwnmc1W5M/1aVPQ33vgEFcS/brpWMf7xLIL9EGDp 4/4BukImG4wOx1L3GuKIw5XXNLA9uCriWXr7NSvI00AhXR7m/NbE9ZLIHKfTWssFf9hYnh3p x2n1E+Hy/okqDppfGynHUJjcl5UwP2v6lKO4VO8Gz6wdhl2sYU9pNT6/8Hfp9lnY0BTbFv5Y zZfD77NdCJoDwEd2FUgRzb4vqjwoswci8U73pcOCylbQbAEC5scy/Bd5BOoSIy0kGaQYqumA 3rGXzDn4B02xiYiraNIH28jZPmWzYGM/jncajRFYARZFh/K1OW8Wl3JILAlwZP4VO6ECDj3r qv9EwUv6gxsikXtCT0xjnHL/N5+UQBIQOrx7/TKb80MAEQEAAYkCJQQYAQgADwUCWeZ8wQIb DAUJB4YfgAAKCRB8vD6mldfDUvPlD/4rpb17nhmvSnAy+ShanY2p40M4kgo46Mwmvm3Dmug8 5P+ECRtmhTZ+kEU8Ici/u7FFvE1WHydeNCdE/tOaJ3qXMLBOd4SiB+sa21E0veqR28qUmymI Ti/gFhMpL2Cfa5oPQj5sq/fdEPJzpAoaHfxUMPIUZ7ebM96syazndEFJAFS9MO00/SJ/HcUx 8VMypzqoG+EyBs4c+PThNRyuUH5x8gh4uABILTYIwqUE4HESpRef9ETdJpBkWhsQqxo3Nvoa TLjZH0LNnMyZXOb/aUeQBm0TSGda+on+0BdI70QgI42mu6ovKup32eUhHh7C9scKv8NJYo1Z 4PqRQVIEPznCizZ90aBP1Ia0rJfcADzFNrOQru1WXmm+zXu/TYdESyoL5MRAlitEIs34oeJF X8wMgkHZBFVhvEm60ynH2HZQRLWGIVBnW7oJ2apTx10L2AZWZvLQ6P8pn4eZMPX3+f854d4Q D4yEhE9Ec7CTiqmo6A5+bHGi/yf8VzxMNorlyHCIFZZdfm5vctcg78iUfqsKyvAULpmjLKyA YkX0qv2EnCMUb9urwITWbQTySOVazfjOjRPUO1z5RbfGwCMwNdW8ahf94jtLaVbvVp+7F8dD Y81Qhcbc2MAOWzP1ghr+wsyAyvzVJuDS0kDh6XjpuZddSBEYAh+aI8Kcwqsr75INMg== Organization: Network Test Inc. Message-ID: <9fdcf15e-2f83-1cfd-1e04-cc08943485fa@networktest.com> Date: Sun, 20 Oct 2019 23:22:52 -0700 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.14; rv:68.0) Gecko/20100101 Thunderbird/68.1.2 MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Language: en-US Content-Transfer-Encoding: 7bit X-Rspamd-Queue-Id: 46xRQp3b3tz3KNt X-Spamd-Bar: --- Authentication-Results: mx1.freebsd.org; dkim=pass header.d=networktest.com header.s=dkim header.b=lFz4Q0NA; dmarc=none; spf=pass (mx1.freebsd.org: domain of dnewman@networktest.com designates 192.73.244.137 as permitted sender) smtp.mailfrom=dnewman@networktest.com X-Spamd-Result: default: False [-3.28 / 15.00]; ARC_NA(0.00)[]; RCVD_VIA_SMTP_AUTH(0.00)[]; R_DKIM_ALLOW(-0.20)[networktest.com:s=dkim]; NEURAL_HAM_LONG(-1.00)[-1.000,0]; FROM_HAS_DN(0.00)[]; R_SPF_ALLOW(-0.20)[+mx]; TO_MATCH_ENVRCPT_ALL(0.00)[]; MIME_GOOD(-0.10)[text/plain]; PREVIOUSLY_DELIVERED(0.00)[freebsd-questions@freebsd.org]; TO_DN_NONE(0.00)[]; RCPT_COUNT_ONE(0.00)[1]; HAS_ORG_HEADER(0.00)[]; RCVD_COUNT_THREE(0.00)[4]; RCVD_TLS_LAST(0.00)[]; DKIM_TRACE(0.00)[networktest.com:+]; DMARC_NA(0.00)[networktest.com]; NEURAL_HAM_MEDIUM(-1.00)[-0.999,0]; IP_SCORE(-0.78)[asn: 36236(-3.85), country: US(-0.05)]; RECEIVED_SPAMHAUS_PBL(0.00)[131.86.82.75.khpj7ygk5idzvmvt5x4ziurxhy.zen.dq.spamhaus.net : 127.0.0.10]; FROM_EQ_ENVFROM(0.00)[]; MIME_TRACE(0.00)[0:+]; ASN(0.00)[asn:36236, ipnet:192.73.244.0/24, country:US]; MID_RHS_MATCH_FROM(0.00)[] X-BeenThere: freebsd-questions@freebsd.org X-Mailman-Version: 2.1.29 Precedence: list List-Id: User questions List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 21 Oct 2019 06:23:15 -0000 Greetings. FreeBSD recently changed its default version of PostgreSQL from 9.5 to 11. However, attempts to run 'pg_upgrade' on the databases failed for me because my 9.5 install had a default user of 'pgsql' and version 11 goes with 'postgres' instead [1]. My hack was to edit /usr/local/etc/rc.d/postgresql, set postgresql_user to pgsql, (re)run initdb, and then do the pg_upgrade conversion. It works, but future upgrades might clobber the edit in the rc.d file. This article suggests one ALTER command will change the default PostgreSQL user: https://netnow.jira.com/wiki/spaces/PUBP/pages/119996467/Changing+the+default+user+and+password+for+postgreSQL Is that, plus chown'ing the data directory, sufficient to effect a name change? Thanks! dn [1] The pg_upgrade program has a -U switch to specify user, but it still fails because the pgsql user can't read stuff owned by the postgres user and vice-versa. Just running chown on either binary or data directory and its contents doesn't work.