Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1j1mVC-0001jq-MB for pgsql-general@arkaria.postgresql.org; Wed, 12 Feb 2020 07:32:06 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1j1mVA-0003d5-IE for pgsql-general@arkaria.postgresql.org; Wed, 12 Feb 2020 07:32:04 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1j1mVA-0003aq-8S for pgsql-general@lists.postgresql.org; Wed, 12 Feb 2020 07:32:04 +0000 Received: from portal-smtp-out.hot.ee ([194.126.101.125]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1j1mV7-000713-Bk for pgsql-general@postgresql.org; Wed, 12 Feb 2020 07:32:03 +0000 Received: from localhost (localhost [127.0.0.1]) by vm-hot-relay10.estpak.ee (Postfix) with ESMTP id 68CC71ADE; Wed, 12 Feb 2020 09:32:00 +0200 (EET) X-Virus-Scanned: Debian amavisd-new at vm-hot-relay10.estpak.ee Received: from portal-smtp-out.hot.ee ([127.0.0.1]) by localhost (vm-hot-relay10.estpak.ee [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 1cu71NOGDsyt; Wed, 12 Feb 2020 09:31:57 +0200 (EET) Received: from mail.hot.ee (hot-relayhost1.estpak.ee [213.180.2.79]) by vm-hot-relay10.estpak.ee (Postfix) with ESMTP id BBF8F1549; Wed, 12 Feb 2020 09:31:57 +0200 (EET) Received: from dell2 (66-63-196-88.sta.estpak.ee [88.196.63.66]) (Authenticated sender: kobruleht2) by HOT-Relayhost1.estpak.ee (Postfix) with ESMTPSA id 48HWYS3RCLz2TR; Wed, 12 Feb 2020 09:31:55 +0200 (EET) Message-ID: <4D6B3F5D59EA48A0BF711C8E461D6388@dell2> From: "Andrus" To: "Justin" , References: <38D406CB287140A49F635258B634E072@dell2> In-Reply-To: Subject: Re: How to restore roles without changing postgres password Date: Wed, 12 Feb 2020 09:31:48 +0200 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_009B_01D5E187.3FBF3DB0" X-Priority: 3 X-MSMail-Priority: Normal Importance: Normal X-Mailer: Microsoft Windows Live Mail 16.4.3528.331 X-MimeOLE: Produced By Microsoft MimeOLE V16.4.3528.331 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk See on mitmeosaline MIME-vormingus sõnum. ------=_NextPart_000_009B_01D5E187.3FBF3DB0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi! Thank you. >pg_dumpall creates an SQL file which is just a simple text file >you can then edit sql removing postgres user from the file >This can be automated in a script that searches the generated sql file = for the postgres user replacing it with a blank/empty line or adds -- = to the bringing of >the line which comments it out. =20 This script creates cluster copy in every night. So this should be done = automatically. I have little experience with Linux.=20 Can you provide example, how it should it be done using sed or other = tool.=20 There is also second user named dbandmin whose password cannot changed = also. It would be best if CREATE ROLE and ALTER ROLE clauses for postgres = and dbadmin users are removed for file. Or if this is not reasonable, same passwords or different role names can = used in both clusters. Also I dont understand why GRANTED BY clauses appear in file. This looks = like noice.=20 GRANT documentation https://www.postgresql.org/docs/current/sql-grant.html does not contain GRANTED BY clause. It looks like pg_dumpall generates = undocumented clause. Andrus. ------=_NextPart_000_009B_01D5E187.3FBF3DB0 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi!
 
Thank you.
 
>pg_dumpall=20 creates an SQL file which is just a simple text file
>you can then edit sql removing postgres user from  the = file
>This can be automated in a script that searches the generated = sql file=20 for the postgres user  replacing it with a blank/empty line or adds = -- to=20 the bringing of >the line which comments it out. 
This script creates cluster copy in every night. So this should be = done=20 automatically.
I have little experience with Linux.
Can you provide example, how it should it be done using sed or = other tool.=20
There is also second user named dbandmin whose password  = cannot=20 changed also.
 
It would be best if  CREATE ROLE and ALTER ROLE  clauses = for=20 postgres and dbadmin users are removed for file.
 
Or if this is not reasonable, same passwords or different role = names can=20 used in both clusters.
 
Also I dont understand why GRANTED BY clauses appear in file. This = looks=20 like noice.
GRANT documentation
https://www.postgresql.org/docs/current/sql-grant.html<= /DIV>
 
does not contain GRANTED BY clause. It looks like pg_dumpall = generates=20 undocumented clause.
 
Andrus.
 
------=_NextPart_000_009B_01D5E187.3FBF3DB0--