Re: Backup

[Andy Hartman]

Thanks.. I'm on a Windows platform using PG.

On Fri, Oct 18, 2024 at 10:45 AM Adrian Klaver 
wrote:

> On 10/18/24 05:39, Andy Hartman wrote:
> > I would like to use the closest thing to a mssql backup...
> >
> > How do I install pgbackrest after downloading  and UnZip?
>
> Read the docs?:
>
> https://pgbackrest.org/user-guide.html#installation
>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

Re: Backup

[Adrian Klaver]

On 10/18/24 05:39, Andy Hartman wrote:

I would like to use the closest thing to a mssql backup...



As in?

Searching on 'mssql backup' returns a  lot of choices. You will need to 
be more specific on what you want to achieve. Also be specific about 
what versions of OS and Postgres you are using.


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Backup

[Adrian Klaver]

On 10/18/24 05:39, Andy Hartman wrote:

I would like to use the closest thing to a mssql backup...

How do I install pgbackrest after downloading  and UnZip?


Read the docs?:

https://pgbackrest.org/user-guide.html#installation



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Permissions for Newly Created User

[Laurenz Albe]

On Fri, 2024-10-18 at 15:41 +0530, sreekanta reddy wrote:
> I would also like to suggest an enhancement to the default behavior for newly 
> created users in PostgreSQL.
> 
> Observed Issue:
> User Created: testdb
> Command used: CREATE USER testdb WITH PASSWORD 'dhsfjobodjjbsdj';
> After creating the user testdb, I observed that the user could still view 
> objects, schemas, and their
> structures, as well as system tables and views, which contradicts the 
> intended restricted permissions.

People have complained about that before, but that's working as designed: most 
metadata are visible
to everybody.  Perhaps we should add that as "feature we don't want" to the 
TODO list.

The standard suggestion is to use different databases if users shouldn't see 
each other's
objects' metadata.

> Suggested Privileges for Newly Created Normal Users:
> I would like to suggest enhancements to the default behavior for newly 
> created normal users in PostgreSQL to improve data security:
> 
> Database Connection: The user should have the ability to connect only to 
> postgres  databases by default

I am not fundamentally against that, but it would be a painful compatibility 
break, and the
gain is small.  After all, the default "pg_hba.conf" file forbids remote 
connections, and
you can get the same effect with the right entries in "pg_hba.conf".

> Read-Only Configurations: The user should have read-only access to view 
> database configuration parameters.

Why?  The ability to change certain parameters on the fly in your session is a 
feature.

> I would also like to highlight a security concern regarding password handling:
> 
> When creating or altering a user's password, the log file captures the 
> password in plain text format, which could be a potential security risk.
> However, when using the \password command in psql, the password is logged in 
> its hashed format (SHA-256), which is a more secure practice.
> I recommend extending this hashed logging format to all password creation and 
> modification operations.

You mean to hash it just for logging?

After you sent it to the server in clear text, so that the DBA could capture it 
with an
event trigger?  Where is the point?  The log file is to be treated as sensitive 
data.

Yours,
Laurenz Albe

Re: Backup

[Adrian Klaver]

On 10/18/24 08:34, Andy Hartman wrote:

Thanks.. I'm on a Windows platform using PG.


AFAIK pgBackRest does not run on Windows:

https://github.com/pgbackrest/pgbackrest/issues/2431



--
Adrian Klaver
adrian.kla...@aklaver.com

Permissions for Newly Created User

[sreekanta reddy]

Dear PostgreSQL Support Team,

I would also like to suggest an enhancement to the default behavior for
newly created users in PostgreSQL.


*Observed Issue:*User Created: testdb
Command used: CREATE USER testdb WITH PASSWORD 'dhsfjobodjjbsdj';
After creating the user testdb, I observed that the user could still view
objects, schemas, and their structures, as well as system tables and views,
which contradicts the intended restricted permissions. Specifically:
The user was able to connect to the database and see all schemas, including
those they should not have visibility into.
Even when permissions were revoked for specific schemas, the user could
still list available tables and view their structures.


*Suggested Privileges for Newly Created Normal Users:*I would like to
suggest enhancements to the default behavior for newly created normal users
in PostgreSQL to improve data security:

*Database Connection:* The user should have the ability to connect only to
postgres  databases by default

*Schema and Table Access:* If the public schema contains 100 tables, the
newly created user should not be able to list or view the structure of any
table unless at least one specific privilege has been granted on those
tables.
Ideally, the system should provide a hint like "user has insufficient
privilege to view schema or table details" when access is restricted.
Restricted Visibility: The user should not have access to list schemas,
tables, or any non-system-related objects unless explicitly authorized.

*Read-Only Configurations:* The user should have read-only access to view
database configuration parameters.
*Privileges:* Additional by default privileges provided if necessary


*Additional Suggestion:*I would also like to highlight a security concern
regarding password handling:

When creating or altering a user's password, the log file captures the
password in plain text format, which could be a potential security risk.
However, when using the \password command in psql, the password is logged
in its hashed format (SHA-256), which is a more secure practice. I
recommend extending this hashed logging format to all password creation and
modification operations.
These suggestions aim to strengthen PostgreSQL's security by minimizing
unnecessary access to sensitive data and improving password handling.

Thank you in advance for considering these.

Best regards,
Sreekanta Reddy

Re: Backup

[Andy Hartman]

Windows Server 2022

I would like a backup process similar to what I'm used to using in the
mssql world if that's  possible with Postgres... I will be loading monthly
archive data to Postgresql so that's why I probably only need once a backup
.. once loaded data is just viewed thru an app... no updates..

in mssql you create the .bak and easily restorable   that's what I'm
looking for with Postgres

thanks for replies..



On Fri, Oct 18, 2024 at 1:00 PM Adrian Klaver 
wrote:

> On 10/18/24 09:52, Andy Hartman wrote:
>
> Reply to list also
> Ccing list.
>
> > Windows 22 and Postgres 16.4
> >
> Is that Windows Server 2022 or Windows 10/11 with the 22H2 update?
>
> You also need to provide a more detailed description of what you want
> the backup to do?
>
> >
> >
> > On Fri, Oct 18, 2024 at 12:04 PM Adrian Klaver
> > mailto:adrian.kla...@aklaver.com>> wrote:
> >
> > On 10/18/24 05:39, Andy Hartman wrote:
> >  > I would like to use the closest thing to a mssql backup...
> >  >
> >
> > As in?
> >
> > Searching on 'mssql backup' returns a  lot of choices. You will need
> to
> > be more specific on what you want to achieve. Also be specific about
> > what versions of OS and Postgres you are using.
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com 
> >
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

Re: Permissions for Newly Created User

[Tom Lane]

Laurenz Albe  writes:
> On Fri, 2024-10-18 at 15:41 +0530, sreekanta reddy wrote:
>> When creating or altering a user's password, the log file captures the 
>> password in plain text format, which could be a potential security risk.
>> However, when using the \password command in psql, the password is logged in 
>> its hashed format (SHA-256), which is a more secure practice.
>> I recommend extending this hashed logging format to all password creation 
>> and modification operations.

> You mean to hash it just for logging?

> After you sent it to the server in clear text, so that the DBA could capture 
> it with an
> event trigger?  Where is the point?  The log file is to be treated as 
> sensitive data.

Yeah.  To enlarge on that: if you are capturing SQL commands in the
log file, they most likely contain all kinds of sensitive data ---
think credit card numbers, bank account numbers, HIPAA-protected
medical details, yadda yadda.  Most of that, the database has no idea
whether it's sensitive, so "please hide sensitive data in the log" is a
non-starter.  You have to guard the postmaster log about as carefully
as you guard the database contents.

regards, tom lane

Re: Backup

[Adrian Klaver]

On 10/18/24 09:52, Andy Hartman wrote:

Reply to list also
Ccing list.


Windows 22 and Postgres 16.4


Is that Windows Server 2022 or Windows 10/11 with the 22H2 update?

You also need to provide a more detailed description of what you want 
the backup to do?





On Fri, Oct 18, 2024 at 12:04 PM Adrian Klaver 
mailto:adrian.kla...@aklaver.com>> wrote:


On 10/18/24 05:39, Andy Hartman wrote:
 > I would like to use the closest thing to a mssql backup...
 >

As in?

Searching on 'mssql backup' returns a  lot of choices. You will need to
be more specific on what you want to achieve. Also be specific about
what versions of OS and Postgres you are using.

-- 
Adrian Klaver

adrian.kla...@aklaver.com 



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Backup

[Andy Hartman]

I would like to use the closest thing to a mssql backup...

How do I install pgbackrest after downloading  and UnZip?



On Thu, Oct 17, 2024 at 6:28 AM Andy Hartman 
wrote:

> This is great, thank you so much!
>
> On Thu, Oct 17, 2024 at 12:47 AM Asad Ali  wrote:
>
>>
>> Hi Andy,
>>
>> I hope you're doing well. Based on your inquiry about PostgreSQL backups
>> for your 100GB historical database with images, here are some suggestions
>> that should help you achieve compressed, efficient backups without running
>> into storage issues.
>>
>> *1. Use Custom Format with Compression*
>> A more efficient option would be to use the custom format (-Fc) with
>> compression. You can also adjust the compression level and make use of your
>> machine's multiple CPUs by using parallel jobs:
>>
>> pg_dump -Fc -Z 9 -j 4 --blobs -f /path/to/backup/file.dump
>> your_database_name
>>
>>- -Fc: Custom format (supports compression and flexible restore
>>options).
>>- -Z 9: Maximum compression level (0-9 scale).
>>- -j 4: Number of parallel jobs (adjust based on CPU cores).
>>- --blobs: Includes large objects (important for your images).
>>
>> This approach should give you a smaller backup file with faster
>> performance.
>>
>> *2. Splitting Backups into Parts*
>> If you're concerned about running out of storage space, consider
>> splitting the backup by table or schema, allowing more control over the
>> backup size:
>>
>> pg_dump -Fc --table=my_large_table -f /path/to/backup/my_large_table.dump
>> your_database_name
>> pg_dump -Fc --table=other_table -f /path/to/backup/other_table.dump
>> your_database_name
>>
>> This can be helpful when you archive different tables or sections of data.
>>
>> *3. External Compression Tools*
>> If you need additional compression, you can pipe the pg_dump output
>> through an external compression tool like gzip:
>>
>> pg_dump -Fc --blobs your_database_name | gzip >
>> /path/to/backup/file.dump.gz
>>
>> This should further reduce the size of your backups.
>>
>> *4. Consider Alternative Backup Methods*
>> - Explore other backup methods like `*pgBackRest` or `WAL-E`*. These
>> tools are specifically designed for PostgreSQL backups and offer features
>> like incremental backups and point-in-time recovery
>> pgbackrest --stanza=your-database --type=full --compress-type=zst
>> --compress-level=6 --process-max=4 backup
>>
>> - You can use *pg_basebackup* for PostgreSQL backups, but it has
>> limitations compared to tools like pgBackRest. While pg_basebackup is easy
>> to use and built-in with PostgreSQL, it is primarily designed for physical
>> backups (base backups) and doesn't offer as many advanced features such as
>> incremental backups, sophisticated compression, or parallelism.
>>
>> However, it does support basic compression and can be used for full
>> backups.
>>
>> pg_basebackup -D /path/to/backup/directory -F t -z -Z 9 -P -X stream
>>
>>- -D: The destination directory for the backup.
>>- -F t: Specifies the tar format for the backup, which is required
>>for compression.
>>- -z: Compresses the output.
>>- -Z 9: Compression level (0–9, where 9 is the highest).
>>- -P: Shows the progress of the backup.
>>- -X stream: Includes the WAL files needed to make the backup
>>consistent (important for recovery).
>>
>> pg_basebackup -D /backups/db_backup -F t -z -Z 9 -P -X stream
>> This command will take a full physical backup of the database, compress
>> the output using gzip, and store the backup in a tarball.
>>
>> *5. Automating Backups*
>> Since you need monthly backups, I recommend automating this process with
>> a cron job. For example, you can set this up to run on the 1st of every
>> month at 2 AM:
>>
>> 0 2 1 * * /usr/bin/pg_dump -Fc -Z 9 -j 4 --blobs -f
>> /path/to/backup/file.dump your_database_name
>>
>> *6. Monitoring Disk Usage & * *Backup Performance*
>> Finally, it's important to monitor your available storage. You can either
>> ensure you have enough free space or consider moving older backups to
>> external or cloud storage to free up space.
>> Use monitoring tools to track the performance of your backups. This will
>> help you identify any potential bottlenecks and optimize the backup process.
>>
>> I hope this helps you create smaller and quicker backups for your
>> PostgreSQL database. Let me know if you have any questions or need further
>> assistance!
>>
>> Best regards,
>>
>> Asad Ali
>>
>>
>> On Thu, Oct 17, 2024 at 12:38 AM Andy Hartman 
>> wrote:
>>
>>> I am very new to Postgres and have always worked in the mssql world. I'm
>>> looking for suggestions on DB backups. I currently have a DB used to store
>>> Historical information that has images it's currently around 100gig.
>>>
>>> I'm looking to take a monthly backup as I archive a month of data at a
>>> time. I am looking for it to be compressed and have a machine that has
>>> multiple cpu's and ample memory.
>>>
>>> Suggestions on things I can try ?
>>> I did a pg_dump usin

Re: Permissions for Newly Created User

[Adrian Klaver]

On 10/18/24 03:11, sreekanta reddy wrote:


Dear PostgreSQL Support Team,

I would also like to suggest an enhancement to the default behavior for 
newly created users in PostgreSQL.


*Observed Issue:
*User Created: testdb
Command used: CREATE USER testdb WITH PASSWORD 'dhsfjobodjjbsdj';
After creating the user testdb, I observed that the user could still 
view objects, schemas, and their structures, as well as system tables 
and views, which contradicts the intended restricted permissions. 


What restrictions?

The user has what is specified here:

https://www.postgresql.org/docs/current/ddl-priv.html

Pay particular attention to what is granted to the PUBLIC role.

If you want the role to have less privilges that what the defaults are 
then you will need to explicitly revoke them.




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Backup

[Adrian Klaver]

On 10/18/24 11:25 AM, Andy Hartman wrote:

Windows Server 2022

I would like a backup process similar to what I'm used to using in the 
mssql world if that's  possible with Postgres... I will be 
loading monthly archive data to Postgresql so that's why I probably only 
need once a backup .. once loaded data is just viewed thru an app... no 
updates..


in mssql you create the .bak and easily restorable   that's what I'm 
looking for with Postgres




Then pg_dump for database backup:

https://www.postgresql.org/docs/current/app-pgdump.html

and pg_dumpall for entire cluster backup:

https://www.postgresql.org/docs/current/app-pg-dumpall.html


thanks for replies..



On Fri, Oct 18, 2024 at 1:00 PM Adrian Klaver > wrote:


On 10/18/24 09:52, Andy Hartman wrote:

Reply to list also
Ccing list.

 > Windows 22 and Postgres 16.4
 >
Is that Windows Server 2022 or Windows 10/11 with the 22H2 update?

You also need to provide a more detailed description of what you want
the backup to do?

 >
 >
 > On Fri, Oct 18, 2024 at 12:04 PM Adrian Klaver
 > mailto:adrian.kla...@aklaver.com>
>> wrote:
 >
 >     On 10/18/24 05:39, Andy Hartman wrote:
 >      > I would like to use the closest thing to a mssql backup...
 >      >
 >
 >     As in?
 >
 >     Searching on 'mssql backup' returns a  lot of choices. You
will need to
 >     be more specific on what you want to achieve. Also be
specific about
 >     what versions of OS and Postgres you are using.
 >
 >     --
 >     Adrian Klaver
 > adrian.kla...@aklaver.com 
>
 >

-- 
Adrian Klaver

adrian.kla...@aklaver.com 



--
Adrian Klaver
adrian.kla...@aklaver.com

Postgres installation is failing in mac

[Venkata Nori]

Problem running post-install step. Installation may not complete correctly
The database cluster initialisation failed.


Regards,
Prasad Nori
Senior Data Scientist
Indian Software Labs (ISL)
E-mail: venor...@in.ibm.com

Re: Postgres installation is failing in mac

[Daniel Gustafsson]

> On 18 Oct 2024, at 04:21, Venkata Nori  wrote:
> 
> Problem running post-install step. Installation may not complete correctly
> The database cluster initialisation failed.

There is no information in this post which could assist anyone in helping you,
if you would like help I suggest you provide a lot more details.  Which
installer was used?  Where was it downloaded from?  Which macOS version?  Which
PostgreSQL version?  Does the system log say anything?  Is there enough space
on the partition?  Do you have admin rights or is it a managed device? And so 
on..

--
Daniel Gustafsson