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: <159fd473-e361-4b60-9d62-5cf617bc4be1@aklaver.com>
Date: Thu, 13 Feb 2025 20:57:00 +0100
Cc: pgsql-general list <pgsql-general@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <C3E9395C-5235-4E62-A678-BE04E6AF772F@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>
 <C7FEEB74-B8B2-4681-9961-432F68A2942D@gmail.com>
 <159fd473-e361-4b60-9d62-5cf617bc4be1@aklaver.com>
To: Adrian Klaver <adrian.klaver@aklaver.com>
Archived-At: <https://www.postgresql.org/message-id/C3E9395C-5235-4E62-A678-BE04E6AF772F%40gmail.com>
Precedence: bulk

Hi Adrian,

> On 13 Feb 2025, at 19:05, Adrian Klaver <adrian.klaver@aklaver.com> =
wrote:
>=20
> Then run pg_restore -s -f db_name.sql against whatever is the dump =
file produced by pg_dump -Fc -Z1 ...
>=20
> It will create a plain text version of the schema definitions, no data =
in the file db_name.sql. Then you can see if GRANTs are being done.

I think, we're not talking about the same thing. I'm talking about =
access privileges on the database, i.e. connect, create, etc. Without a =
connect privilege, no schema privileges are relevant in the first place.

> This only shows the information the actual database object not the =
objects contained within it.

Yes, this is what I am referring to, the access privileges on the =
database, not objects.

> You will need to show your work:
> 1) What does \l show in the cluster you are dumping from?
> 2) What are the roles and what privileges are they being granted?

I'm not at work anymore and won't be until Monday (long weekend =F0=9F=A4=A3=
). So I don't have the exact case handy. However, I tried on my home =
database clusters (15.10 and 17.3). Seems, at least here at home, only =
using -C works. I don't know (yet) why it does not work at work.

Here's what I tried on my own clusters. Note the access privileges for =
"paul".


Source DB PostgreSQL 15.10
--------------------------

postgres=3D# \l mydb
                                               List of databases
 Name |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | =
Locale Provider |   Access privileges  =20
=
------+----------+----------+-------------+-------------+------------+----=
-------------+-----------------------
 mydb | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | =
libc            | =3DTc/postgres         +
      |          |          |             |             |            |   =
              | postgres=3DCTc/postgres+
      |          |          |             |             |            |   =
              | paul=3DCTc/postgres
(1 row)

$ export PGDATABASE=3Dmydb
$ pg_dump -Fc -Z1 --quote-all-identifiers -b "${PGDATABASE}" -f =
${PGDATABASE}.dump.gz

No output, no error messages. Everything is fine.


Target DB PostgreSQL 17.3
-------------------------

postgres=3D# create role paul login;
CREATE ROLE
postgres=3D# create database mydb template template0;
CREATE DATABASE
postgres=3D# \l mydb
                                                 List of databases
 Name |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype   =
 | Locale | ICU Rules | Access privileges=20
=
------+----------+----------+-----------------+-------------+-------------=
+--------+-----------+-------------------
 mydb | postgres | UTF8     | icu             | en_US.UTF-8 | =
en_US.UTF-8 | en-US  |           |=20
(1 row)

$ pg_restore -C -d mydb mydb.dump.gz=20
pg_restore: error: could not execute query: ERROR:  database "mydb" =
already exists
Command was: CREATE DATABASE "mydb" WITH TEMPLATE =3D template0 ENCODING =
=3D 'UTF8' LOCALE_PROVIDER =3D libc LOCALE =3D 'en_US.UTF-8';


pg_restore: warning: errors ignored on restore: 1

postgres=3D# \l mydb
                                                   List of databases
 Name |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype   =
 | Locale | ICU Rules |   Access privileges  =20
=
------+----------+----------+-----------------+-------------+-------------=
+--------+-----------+-----------------------
 mydb | postgres | UTF8     | icu             | en_US.UTF-8 | =
en_US.UTF-8 | en-US  |           | =3DTc/postgres         +
      |          |          |                 |             |            =
 |        |           | postgres=3DCTc/postgres+
      |          |          |                 |             |            =
 |        |           | paul=3DCTc/postgres
(1 row)

So, "paul" again has CTc after pg_restore. That's what does not work at =
work. I'll have to figure out what's wrong there.

Cheers,
Paul=