Default permissions for CREATE SCHEMA/TABLE?

* Yang Zhang (yanghatespam@gmail.com) wrote:

Any way I can have all newly created schemas/tables be owned by, or
have all permissions granted to, a certain group, without having to
remember to GRANT ALL ON [SCHEMA|TABLE] TO that group? Thanks in
advance.

ALTER DEFAULT PRIVILEGES FOR ROLE my_user IN SCHEMA my_schema
GRANT SELECT ON TABLES TO other_role;

http://www.postgresql.org/docs/9.0/static/sql-alterdefaultprivileges.html

Thanks,

Stephen

On Thursday, March 24, 2011 1:52:24 pm Yang Zhang wrote:

Any way I can have all newly created schemas/tables be owned by, or
have all permissions granted to, a certain group, without having to
remember to GRANT ALL ON [SCHEMA|TABLE] TO that group? Thanks in
advance.

If you are using 9.0:
http://www.postgresql.org/docs/9.0/interactive/sql-alterdefaultprivileges.html
--
Adrian Klaver
adrian.klaver@gmail.com

Keep in mind if you want to alter the GLOBAL privileges (i.e., the defaults)
granted via PUBLIC you MUST NOT specify a schema.

From what I can tell there is no way to associate a default owner different
that the one executing the CREATE statement (though some inheritance cases
do arise IIRC).

David J.

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Stephen Frost
Sent: Thursday, March 24, 2011 4:56 PM
To: Yang Zhang
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Default permissions for CREATE SCHEMA/TABLE?

* Yang Zhang (yanghatespam@gmail.com) wrote:

Any way I can have all newly created schemas/tables be owned by, or
have all permissions granted to, a certain group, without having to
remember to GRANT ALL ON [SCHEMA|TABLE] TO that group? Thanks in
advance.

ALTER DEFAULT PRIVILEGES FOR ROLE my_user IN SCHEMA my_schema GRANT SELECT
ON TABLES TO other_role;

http://www.postgresql.org/docs/9.0/static/sql-alterdefaultprivileges.html

Thanks,

Stephen

Any luck if I'm still on 8.4?

On Thu, Mar 24, 2011 at 3:17 PM, David Johnston <polobo@yahoo.com> wrote:

Keep in mind if you want to alter the GLOBAL privileges (i.e., the defaults)
granted via PUBLIC you MUST NOT specify a schema.

From what I can tell there is no way to associate a default owner different
that the one executing the CREATE statement (though some inheritance cases
do arise IIRC).

David J.

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Stephen Frost
Sent: Thursday, March 24, 2011 4:56 PM
To: Yang Zhang
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Default permissions for CREATE SCHEMA/TABLE?

* Yang Zhang (yanghatespam@gmail.com) wrote:

Any way I can have all newly created schemas/tables be owned by, or
have all permissions granted to, a certain group, without having to
remember to GRANT ALL ON [SCHEMA|TABLE] TO that group?  Thanks in
advance.

ALTER DEFAULT PRIVILEGES FOR ROLE my_user IN SCHEMA my_schema GRANT SELECT
ON TABLES TO other_role;

http://www.postgresql.org/docs/9.0/static/sql-alterdefaultprivileges.html

       Thanks,

               Stephen

--
Yang Zhang
http://yz.mit.edu/

On Thursday, March 24, 2011 6:10:32 pm Yang Zhang wrote:

Any luck if I'm still on 8.4?

As far as I know, no luck. If you search the list archives there are some
scripts people have posted that will walk through a database or schema and
change permissions.

--
Adrian Klaver
adrian.klaver@gmail.com