Thread: Default permissions for CREATE SCHEMA/TABLE?
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.
* 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
Attachment
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