Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.400.131.1.6\))
Subject: Re: libc to libicu via pg_dump/pg_restore?
From: Paul Foerster <paul.foerster@gmail.com>
In-Reply-To: <0150a3d8-9276-4534-9847-8d3d7bba6e16@aklaver.com>
Date: Thu, 13 Feb 2025 18:31:40 +0100
Cc: pgsql-general list <pgsql-general@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <C7FEEB74-B8B2-4681-9961-432F68A2942D@gmail.com>
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>
 <0150a3d8-9276-4534-9847-8d3d7bba6e16@aklaver.com>
To: Adrian Klaver <adrian.klaver@aklaver.com>
Archived-At: <https://www.postgresql.org/message-id/C7FEEB74-B8B2-4681-9961-432F68A2942D%40gmail.com>
Precedence: bulk

Hi Adrian,

> On 13 Feb 2025, at 17:40, Adrian Klaver <adrian.klaver@aklaver.com> =
wrote:
>=20
> Per:
>=20
> https://www.postgresql.org/docs/current/ddl-priv.html
>=20
> "If the =E2=80=9CAccess privileges=E2=80=9D 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=3DarwdDxt/miriam) and =
then modify them per the specified request. Similarly, entries are shown =
in =E2=80=9CColumn privileges=E2=80=9D only for columns with nondefault =
privileges. (Note: for this purpose, =E2=80=9Cdefault privileges=E2=80=9D =
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.)"
>=20
> =46rom this:
>=20
> 1) It not unusual for the field to be blank.
>=20
> 2) \l only lists the privileges for the database object itself, not =
any of it's contained objects.
>=20
> In the original database are you executing explicit GRANTs on the =
database object?
>=20
> Do:
>=20
> pg_restore -s -f db_name.sql ${PGDATABASE}.out
>=20
> 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=