Thread: PostgreSQL 8.4 - permissions for newly created tables?

Hi All.

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)?

3) I think that kind of functionality is best to accomplish with pg_hba.conf?

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?

Best regards,

Rafal Radecki.

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