Hi Adrian,
> On 13 Feb 2025, at 19:05, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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
