Re: libc to libicu via pg_dump/pg_restore? - Mailing list pgsql-general

Hi Adrian,

> On 13 Feb 2025, at 19:05, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
> Then run pg_restore -s -f db_name.sql against whatever is the dump file produced by pg_dump -Fc -Z1 ...
>
> It will create a plain text version of the schema definitions, no data in the file db_name.sql. Then you can see if
GRANTsare 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 🤣). So I don't have the exact case handy. However, I
triedon 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=# \l mydb
                                               List of databases
 Name |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges
------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
 mydb | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =Tc/postgres         +
      |          |          |             |             |            |                 | postgres=CTc/postgres+
      |          |          |             |             |            |                 | paul=CTc/postgres
(1 row)

$ export PGDATABASE=mydb
$ 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=# create role paul login;
CREATE ROLE
postgres=# create database mydb template template0;
CREATE DATABASE
postgres=# \l mydb
                                                 List of databases
 Name |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | Locale | ICU Rules | Access privileges
------+----------+----------+-----------------+-------------+-------------+--------+-----------+-------------------
 mydb | postgres | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US  |           |
(1 row)

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


pg_restore: warning: errors ignored on restore: 1

postgres=# \l mydb
                                                   List of databases
 Name |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | Locale | ICU Rules |   Access privileges
------+----------+----------+-----------------+-------------+-------------+--------+-----------+-----------------------
 mydb | postgres | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US  |           | =Tc/postgres         +
      |          |          |                 |             |             |        |           | postgres=CTc/postgres+
      |          |          |                 |             |             |        |           | paul=CTc/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