Hi Adrian,
> On 13 Feb 2025, at 17:40, Adrian Klaver <adrian.kla...@aklaver.com> wrote:
>
> 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.
${PGDATABASE}.out and ${PGDATABASE}.err are just log files of the backgrounded
pg_dump command. The .out file is empty anyway and the .err file only contains
the messages about the database being not be able to be dropped (-c) because I
sit on it, which is explainable and correct.
What I mean is, in our environment there are four (application) roles having
certain privileges, one of them being the database owner while the others have
certain rights like reading or manipulating data, but no DDL. These four roles
all have their privileges shown with \l in the access privileges column.
Contrary to how I understand the documentation of pg_restore, they are restored
only if I use -cC and they are not restored if I only use -C.
Cheers,
Paul
