public inbox for [email protected]  

help / color / mirror / Atom feed

From: Paul Foerster <[email protected]>
To: Adrian Klaver <[email protected]>
Cc: pgsql-general list <[email protected]>
Subject: Re: libc to libicu via pg_dump/pg_restore?
Date: Thu, 13 Feb 2025 20:57:00 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>

Hi Adrian,

> On 13 Feb 2025, at 19:05, Adrian Klaver <[email protected]> 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 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 🤣). 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=# \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

view thread (5+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: libc to libicu via pg_dump/pg_restore?
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox