Re: PostgreSQL 8.4 - permissions for newly created tables?

Thanks for the info! ;)

2013/6/21 Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>

> Rafal Radecki wrote:
> > I have to setup a new infrastructure with postgre software. I would like
> to use user access
> > patterns from mysql if possible. For every application:
> > 1) one user for reads: grant select on db.* to 'reader'@'ip space' ...;
> > 2) one user for reads/writes: grant update,insert,select,delete on db.*
> to 'writer'@'ip space'
> > ...;
> > 3) every user comes from a defined IP address space.
> > I would like to give one postgre db to one application.
> > 1) and 2) - Is it really necessary to give permissions for newly created
> tables for such users
> > every time a table is created (I am using 8.4, not 9.X)?
>
> From 9.0 on, you could use ALTER DEFAULT PRIVILEGES to
> set default permissions for objects you create.
> So yes, if you are using 8.4, you'll have to set the permissions
> for every object you create.
>
> > 3) I think that kind of functionality is best to accomplish with
> pg_hba.conf?
>
> With pg_hba.conf you can restrict access to the database to
> certain IP ranges, if that's what you are asking.
>
> > I also would like to give ALL database for newly created application
> database rights for a lead
> > programmer, so then he/she could give permissions for newly created
> objects to reader/writer
> > application roles.
> >
> > Is it the best way? Any hints?
> >
> > I can also make this lead programmer an owner of the newly created
> database instead of giving him ALL
> > permissions. "owner" is better than "all"?
>
> It does not make much difference, except that the owner can
> use ALTER/DROP DATABASE. As long as a user has CREATE privilege
> on a database, he or she can create schemata in it.
>
> Yours,
> Laurenz Albe
>