public inbox for [email protected]
help / color / mirror / Atom feedRe: libc to libicu via pg_dump/pg_restore?
5+ messages / 2 participants
[nested] [flat]
* Re: libc to libicu via pg_dump/pg_restore?
@ 2025-02-13 16:40 Adrian Klaver <[email protected]>
2025-02-13 17:31 ` Re: libc to libicu via pg_dump/pg_restore? Paul Foerster <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Adrian Klaver @ 2025-02-13 16:40 UTC (permalink / raw)
To: Paul Foerster <[email protected]>; +Cc: pgsql-general list <[email protected]>
On 2/13/25 08:17, Paul Foerster wrote:
> Hi Adrian,
>
> sorry for the late answer. I'm just too busy.
>
>> On 7 Feb 2025, at 17:19, Adrian Klaver <[email protected]> wrote:
>>
>>> With create database <options> being "template template0", this is what my script does. But I need the -cC options for pg_restore to get ACLs back. Leaving out either one of them will not get me the ACLs back.
>>
>> That does not make sense.
>>
>> Are there ACLs(privileges) in the database at all?
>>
>> What is the pg_dump command you are running?
>
> I use this pg_dump command:
>
> pg_dump -Fc -Z1 --quote-all-identifiers -b "${PGDATABASE}" -f ${dumpBase}/${clusterName}.${PGDATABASE}.dump.gz >${PGDATABASE}.out 2>${PGDATABASE}.err &
>
> The command is embedded in a Bash script for loop that loops PGDATABASE over all database names inside the cluster and launches pg_dump as a background job. It then waits for all jobs to complete ("wait" command). dumpBase is just the destination directory.
>
> If I don't use -cC, i.e. both, then the Access privileges will not be restored. Checking with \l just shows an empty field as usual for a newly created database. This happens at least with 17.2. I didn't check that with 17.3 yet.
Per:
https://www.postgresql.org/docs/current/ddl-priv.html
"If the “Access privileges” 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=arwdDxt/miriam) and then modify them per the
specified request. Similarly, entries are shown in “Column privileges”
only for columns with nondefault privileges. (Note: for this purpose,
“default privileges” 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.)"
From this:
1) It not unusual for the field to be blank.
2) \l only lists the privileges for the database object itself, not any
of it's contained objects.
In the original database are you executing explicit GRANTs on the
database object?
Do:
pg_restore -s -f db_name.sql ${PGDATABASE}.out
This will create a text version restore of the schema objects in the
dump file. Then search the file for GRANT statements.
>
> Cheers,
> Paul
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: libc to libicu via pg_dump/pg_restore?
2025-02-13 16:40 Re: libc to libicu via pg_dump/pg_restore? Adrian Klaver <[email protected]>
@ 2025-02-13 17:31 ` Paul Foerster <[email protected]>
2025-02-13 18:05 ` Re: libc to libicu via pg_dump/pg_restore? Adrian Klaver <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Paul Foerster @ 2025-02-13 17:31 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: pgsql-general list <[email protected]>
Hi Adrian,
> On 13 Feb 2025, at 17:40, Adrian Klaver <[email protected]> wrote:
>
> Per:
>
> https://www.postgresql.org/docs/current/ddl-priv.html
>
> "If the “Access privileges” 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=arwdDxt/miriam) and then modify them per the specified request. Similarly, entries are shown in “Column privileges” only for columns with nondefault privileges. (Note: for this purpose, “default privileges” 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.)"
>
> From this:
>
> 1) It not unusual for the field to be blank.
>
> 2) \l only lists the privileges for the database object itself, not any of it's contained objects.
>
> In the original database are you executing explicit GRANTs on the database object?
>
> Do:
>
> pg_restore -s -f db_name.sql ${PGDATABASE}.out
>
> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: libc to libicu via pg_dump/pg_restore?
2025-02-13 16:40 Re: libc to libicu via pg_dump/pg_restore? Adrian Klaver <[email protected]>
2025-02-13 17:31 ` Re: libc to libicu via pg_dump/pg_restore? Paul Foerster <[email protected]>
@ 2025-02-13 18:05 ` Adrian Klaver <[email protected]>
2025-02-13 19:57 ` Re: libc to libicu via pg_dump/pg_restore? Paul Foerster <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Adrian Klaver @ 2025-02-13 18:05 UTC (permalink / raw)
To: Paul Foerster <[email protected]>; +Cc: pgsql-general list <[email protected]>
On 2/13/25 09:31, Paul Foerster wrote:
> Hi Adrian,
>
>> On 13 Feb 2025, at 17:40, Adrian Klaver <[email protected]> wrote:
>>
>> Per:
>>
>> https://www.postgresql.org/docs/current/ddl-priv.html
>>
>> "If the “Access privileges” 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=arwdDxt/miriam) and then modify them per the specified request. Similarly, entries are shown in “Column privileges” only for columns with nondefault privileges. (Note: for this purpose, “default privileges” 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.)"
>>
>> From this:
>>
>> 1) It not unusual for the field to be blank.
>>
>> 2) \l only lists the privileges for the database object itself, not any of it's contained objects.
>>
>> In the original database are you executing explicit GRANTs on the database object?
>>
>> Do:
>>
>> pg_restore -s -f db_name.sql ${PGDATABASE}.out
>>
>> 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.
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.
>
> 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.
From here:
https://www.postgresql.org/docs/current/app-psql.html
\l[+] or \list[+] [ pattern ]
List the databases in the server and show their names, owners,
character set encodings, and access privileges. If pattern is specified,
only databases whose names match the pattern are listed. If + is
appended to the command name, database sizes, default tablespaces, and
descriptions are also displayed. (Size information is only available for
databases that the current user can connect to.)
This only shows the information the actual database object not the
objects contained within it.
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?
>
> Cheers,
> Paul
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: libc to libicu via pg_dump/pg_restore?
2025-02-13 16:40 Re: libc to libicu via pg_dump/pg_restore? Adrian Klaver <[email protected]>
2025-02-13 17:31 ` Re: libc to libicu via pg_dump/pg_restore? Paul Foerster <[email protected]>
2025-02-13 18:05 ` Re: libc to libicu via pg_dump/pg_restore? Adrian Klaver <[email protected]>
@ 2025-02-13 19:57 ` Paul Foerster <[email protected]>
2025-02-13 21:30 ` Re: libc to libicu via pg_dump/pg_restore? Adrian Klaver <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Paul Foerster @ 2025-02-13 19:57 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: pgsql-general list <[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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: libc to libicu via pg_dump/pg_restore?
2025-02-13 16:40 Re: libc to libicu via pg_dump/pg_restore? Adrian Klaver <[email protected]>
2025-02-13 17:31 ` Re: libc to libicu via pg_dump/pg_restore? Paul Foerster <[email protected]>
2025-02-13 18:05 ` Re: libc to libicu via pg_dump/pg_restore? Adrian Klaver <[email protected]>
2025-02-13 19:57 ` Re: libc to libicu via pg_dump/pg_restore? Paul Foerster <[email protected]>
@ 2025-02-13 21:30 ` Adrian Klaver <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Adrian Klaver @ 2025-02-13 21:30 UTC (permalink / raw)
To: Paul Foerster <[email protected]>; +Cc: pgsql-general list <[email protected]>
On 2/13/25 11:57, Paul Foerster wrote:
> Hi Adrian,
>
> 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".
>
>
> 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.
1) Verify the GRANT is in the dump file.
2) Check the Postgres log manually to see if there is an error that is
causing the GRANT to be skipped.
>
> Cheers,
> Paul
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-02-13 21:30 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-02-13 16:40 Re: libc to libicu via pg_dump/pg_restore? Adrian Klaver <[email protected]>
2025-02-13 17:31 ` Paul Foerster <[email protected]>
2025-02-13 18:05 ` Adrian Klaver <[email protected]>
2025-02-13 19:57 ` Paul Foerster <[email protected]>
2025-02-13 21:30 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox