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@xxxxxxxxxxx> 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@xxxxxxxxxxx
