When setting up automated database backups in PostgreSQL, the pg_dump utility requires specific privileges to function properly. The minimal set of permissions needed includes:

• SELECT on all tables to be dumped
• SELECT on all sequences to be dumped
• USAGE on schemas containing objects to be dumped
• CONNECT to the target database

Here's the SQL to create a minimal-privilege backup user:

CREATE ROLE backup_user WITH LOGIN PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE target_db TO backup_user;
GRANT USAGE ON SCHEMA public TO backup_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup_user;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO backup_user;

To ensure the backup user can access objects created after its creation, add these default privileges:

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO backup_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON SEQUENCES TO backup_user;

You can verify the granted permissions with:

SELECT grantee, privilege_type, table_name
FROM information_schema.role_table_grants
WHERE grantee = 'backup_user';

SELECT grantee, privilege_type, table_schema
FROM information_schema.role_usage_grants
WHERE grantee = 'backup_user';

Here's a sample shell script for automated backups using the created user:

#!/bin/bash
PGPASSWORD="secure_password" pg_dump -U backup_user -h localhost -Fc target_db > /backups/target_db_$(date +%Y%m%d).dump

For production environments, consider these additional security measures:

• Restrict the backup user's login to specific IP addresses in pg_hba.conf
• Use SSL connections for remote backups
• Rotate backup passwords regularly
• Store backup files with appropriate filesystem permissions

When automating PostgreSQL database backups, you need to carefully configure user permissions. The minimal privileges required for pg_dump depend on your specific backup scenario:

-- Basic SELECT privileges for most tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup_user;

-- For sequences used in serial columns
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO backup_user;

-- To dump the entire database (including schema)
GRANT TEMPORARY ON DATABASE target_db TO backup_user;

Some scenarios require extra permissions:

-- For dumping large objects
GRANT SELECT ON TABLE pg_largeobject TO backup_user;

-- When using --blobs option
GRANT SELECT ON ALL TABLES IN SCHEMA pg_catalog TO backup_user;

-- For schema-only dumps (-s option)
GRANT USAGE ON SCHEMA public TO backup_user;

Best practice suggests creating a dedicated user with limited privileges:

CREATE ROLE backup_user WITH LOGIN PASSWORD 'secure_password';
ALTER ROLE backup_user SET statement_timeout = '30min';

After setting up, verify the permissions work:

psql -U backup_user -d target_db -c "SELECT * FROM pg_tables LIMIT 1;"
pg_dump -U backup_user -Fc target_db > backup.dump

Always follow principle of least privilege:

• Never grant SUPERUSER or CREATEDB privileges
• Set appropriate connection limits
• Consider using pg_hba.conf to restrict access