Feedback-ID: i76984098:Fastmail
Message-ID: <0150a3d8-9276-4534-9847-8d3d7bba6e16@aklaver.com>
Date: Thu, 13 Feb 2025 08:40:48 -0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: libc to libicu via pg_dump/pg_restore?
To: Paul Foerster <paul.foerster@gmail.com>
Cc: pgsql-general list <pgsql-general@lists.postgresql.org>
References: <B0DB872D-B2A0-4323-9EA8-A7FB4567ACCB@gmail.com>
 <187f2c5d-f268-4596-bb63-c94790ae761d@aklaver.com>
 <D46171D1-80B9-4E81-BC26-D47319B90CE3@gmail.com>
 <99d51fe7-c6d2-4421-9456-07ed1f5703a2@aklaver.com>
 <5A9148A0-CF9C-4841-A9C0-D7E299AC4ADD@gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <5A9148A0-CF9C-4841-A9C0-D7E299AC4ADD@gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/0150a3d8-9276-4534-9847-8d3d7bba6e16%40aklaver.com>
Precedence: bulk

On 2/13/25 08:17, Paul Foerster wrote:
> Hi Adrian,
> 
> sorry for the late answer. I'm just too busy.
> 
>> On 7 Feb 2025, at 17:19, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>>
>>> With create database <options> being "template template0", this is what my script does. But I need the -cC options for pg_restore to get ACLs back. Leaving out either one of them will not get me the ACLs back.
>>
>> That does not make sense.
>>
>> Are there ACLs(privileges) in the database at all?
>>
>> What is the pg_dump command you are running?
> 
> I use this pg_dump command:
> 
> pg_dump -Fc -Z1 --quote-all-identifiers -b "${PGDATABASE}" -f ${dumpBase}/${clusterName}.${PGDATABASE}.dump.gz >${PGDATABASE}.out 2>${PGDATABASE}.err &
> 
> The command is embedded in a Bash script for loop that loops PGDATABASE over all database names inside the cluster and launches pg_dump as a background job. It then waits for all jobs to complete ("wait" command). dumpBase is just the destination directory.
> 
> If I don't use -cC, i.e. both, then the Access privileges will not be restored. Checking with \l just shows an empty field as usual for a newly created database. This happens at least with 17.2. I didn't check that with 17.3 yet.

Per:

https://www.postgresql.org/docs/current/ddl-priv.html

"If the “Access privileges” column is empty for a given object, it means 
the object has default privileges (that is, its privileges entry in the 
relevant system catalog is null). Default privileges always include all 
privileges for the owner, and can include some privileges for PUBLIC 
depending on the object type, as explained above. The first GRANT or 
REVOKE on an object will instantiate the default privileges (producing, 
for example, miriam=arwdDxt/miriam) and then modify them per the 
specified request. Similarly, entries are shown in “Column privileges” 
only for columns with nondefault privileges. (Note: for this purpose, 
“default privileges” always means the built-in default privileges for 
the object's type. An object whose privileges have been affected by an 
ALTER DEFAULT PRIVILEGES command will always be shown with an explicit 
privilege entry that includes the effects of the ALTER.)"

 From this:

1) It not unusual for the field to be blank.

2) \l only lists the privileges for the database object itself, not any 
of it's contained objects.

In the original database are you executing explicit GRANTs on the 
database object?

Do:

pg_restore -s -f db_name.sql ${PGDATABASE}.out

This will create a text version restore of the schema objects in the 
dump file. Then search the file for GRANT statements.


> 
> Cheers,
> Paul

-- 
Adrian Klaver
adrian.klaver@aklaver.com