Skip to main content
Database Administrators

Return to Question

improved formatting
Source Link
Hannah Vernon ♦
Hannah Vernon ♦
  • 70.5k
  • 22
  • 177
  • 321

Postgresql How can I create readonly Backup user for backups in PostgreSQL?

Is it true that it is IMPOSSIBLE to havecreate a readonly backup user in Postgresql PostgreSQL? I tried to hack around this as 

I've been advised on an IRC channel that you simply can't have a backup only user with no ownership privileges, but I just. I find it very strange so I want to make sure I'm not missing something.

Below is what I tried but it doesn't not getgive me the results - as whenI'm looking for. When I do pg_dumppg_dump on a given table I'm getting Permission denied for relation...:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup; 
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO backup; 
GRANT SELECT, USAGE ON ALL SEQUENCES IN SCHEMA public TO backup;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, USAGE ON SEQUENCES TO backup;

Any help would be greatly appreciated!

Postgresql readonly Backup user

Is it true that it is IMPOSSIBLE to have readonly backup user in Postgresql ? I tried to hack around this as I've been advised on IRC channel that you simply can't have a backup only user with no ownership privileges, but I just find it very strange so I want to make sure I'm not missing something.

Below is what I tried but it doesn't not get me the results - as when I do pg_dump on a given table I'm getting Permission denied for relation...:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup; 
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO backup; 
GRANT SELECT, USAGE ON ALL SEQUENCES IN SCHEMA public TO backup;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, USAGE ON SEQUENCES TO backup;

Any help would be greatly appreciated!

How can I create readonly user for backups in PostgreSQL?

Is it true that it is IMPOSSIBLE to create a readonly backup user in PostgreSQL? 

I've been advised on an IRC channel that you simply can't have a backup only user with no ownership privileges. I find it very strange so I want to make sure I'm not missing something.

Below is what I tried but it doesn't give me the results I'm looking for. When I do pg_dump on a given table I'm getting Permission denied for relation...:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup; 
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO backup; 
GRANT SELECT, USAGE ON ALL SEQUENCES IN SCHEMA public TO backup;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, USAGE ON SEQUENCES TO backup;

Any help would be greatly appreciated!

Tweeted twitter.com/#!/StackDBAs/status/396619973235572736
Source Link
milosgajdos
milosgajdos
  • 429
  • 1
  • 5
  • 8

Postgresql readonly Backup user

Is it true that it is IMPOSSIBLE to have readonly backup user in Postgresql ? I tried to hack around this as I've been advised on IRC channel that you simply can't have a backup only user with no ownership privileges, but I just find it very strange so I want to make sure I'm not missing something.

Below is what I tried but it doesn't not get me the results - as when I do pg_dump on a given table I'm getting Permission denied for relation...:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup; 
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO backup; 
GRANT SELECT, USAGE ON ALL SEQUENCES IN SCHEMA public TO backup;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, USAGE ON SEQUENCES TO backup;

Any help would be greatly appreciated!