Re: Question about permissions on database.

On Sun, Sep 23, 2012 at 6:47 AM, Ryan Kelly <rpkelly22(at)gmail(dot)com> wrote:
> On Sat, Sep 22, 2012 at 11:35:00PM +0300, Condor wrote:
>> Hello,
>> I wanna ask: is there a short way to giver permission to one user to
>> select/insert (all privileges) on whole database ?
>> Im create a user and try to give him all permission on existing
>> database, but when I try to select always got:
>> ERROR: permission denied for relation table_name
> You don't want to GRANT on the database. That doesn't do what you think
> it does. You, however, can do:
>
> GRANT ALL ON ALL TABLES IN SCHEMA public TO your_user;
>
> This is generally a bad idea.
>
> You can alternatively make the user a super user:
>
> ALTER ROLE your_user WITH SUPERUSER;
>
> But this is an even worse idea.

I have a similar situation; I want to have a userid for doing the
regular backups, which therefore (for pg_dump) needs SELECT privilege
on all tables. Is:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup_userid;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO backup_userid;

the right way to do this? (Obviously I need to also do this for every
other schema I use.)

Alternatively, is there some better way to do backups? I'd rather not
snapshot the entire data directory; we burn to disc, so a larger
backup requires more media and slower backup/restore process.

ChrisA