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

On 2/13/25 09:31, Paul Foerster wrote:
> Hi Adrian,
> 
>> On 13 Feb 2025, at 17:40, Adrian Klaver <adrian.klaver@aklaver.com> 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,
itsprivileges 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
REVOKEon an object will instantiate the default privileges (producing, for example, miriam=arwdDxt/miriam) and then
modifythem per the specified request. Similarly, entries are shown in “Column privileges” only for columns with
nondefaultprivileges. (Note: for this purpose, “default privileges” always means the built-in default privileges for
theobject's type. An object whose privileges have been affected by an ALTER DEFAULT PRIVILEGES command will always be
shownwith 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
emptyanyway and the .err file only contains the messages about the database being not be able to be dropped (-c)
becauseI 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
thedatabase owner while the others have certain rights like reading or manipulating data, but no DDL. These four roles
allhave their privileges shown with \l in the access privileges column. Contrary to how I understand the documentation
ofpg_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
adrian.klaver@aklaver.com