Re: default grant - Mailing list pgsql-general

Sim Zacks escribió:
> I forgot to mention I'm using 8.2
>
> On 4/7/2010 1:40 PM, Sim Zacks wrote:
>
>> Is there a way to grant permissions on any new objects (or at least new
>> tables) to a role?
>>
>>
>> In my app I have a function that drops and recreates a table at certain
>> times (with a different structure). Like a temporary table, but it
>> survives until a process is run which overwrites it. The process is not
>> the same user as the user who wants to view the table.
>>
>>
>> As soon as this happens the users lose rights to the table and I need to
>> manually run:
>>
>> Grant select on NEWTABLE for users;
>>
>>
>> Thanks
>>
>> Sim
>>
>>
>>
>>
>
>
Are you use the 8.4 version?
On that version the GRANT command have many useful changes.
The syntax is the following:

All grant syntax for privileges follow the same general structure
GRANT { { PRIVILEGES } [,...] | ALL [ PRIVILEGES ] }
– ON { OBJECT TYPE } object [, ...]
– TO { [ GROUP ] rolename | PUBLIC } [, ...]
– [ WITH GRANT OPTION ]
• The default 'object type' is TABLE
• Functions include the argument types in the 'object'
• PRIVILEGES are only those which are applicable to the object type
being modified
• The GROUP keyword is only for backwards compatibility
• WITH GRANT OPTION is included then the grantee will be able to
grant the same privilege to others
• Granting to PUBLIC gives all users the privilege

That text is on the excellent presentation on the PgCon2009 by Stephen
Frost called PostgreSQL Access Control (AuthN, AuthZ, Perms)

Other excellent change is the per column permision. For example:

To illustrate a bit more clearly:
• GRANT SELECT (col2, col3) ON mytable TO role1;
– Grants select on columns “col2” and “col3” in table “mytable” to “role1”
• GRANT INSERT(col1), UPDATE (col2) ON mytable TO role2;
– Grants insert on “col1”, update on “col2” in table “mytable” to “role2”
• GRANT SELECT, UPDATE(col3) ON mytable TO role3;
– Grants select on the table, and update on “col3” for “mytable” to “role3”

My advice is that you have to see that presentation.

Regards,

--
--------------------------------------------------------
-- Ing. Marcos Luís Ortíz Valmaseda                   --
-- Linux User # 418229                                --
-- System Database Architect/Administrator            --
--          *** PostgreSQL ***                        --
-- http://www.postgresql.org                          --
-- http://planetpostgresql.org                        --
-- http://www.postgresql-es.org                       --
-- http://commitfest.postgresql.org                   --
-- http://developer.postgresql.org                    --
-- http://www.enterprisedb.com/tservices/certification
--------------------------------------------------------
--          *** Contacto ***                          --
-- Twitter: http://twitter.com/@marcosluis2186        --
-- Facebook: http://www.facebook.com/marcosluis2186   --
-- GTalk: marcosluis2186@gmail.com                    --
--------------------------------------------------------
-- Ruby on Rails Fan/Developer                        --
-- http://rubyonrails.org                             --
-- http://www.37signals.com                           --
--------------------------------------------------------
-- Comunidad Técnica Cubana de PostgreSQL             --
-- http://postgresql.uci.cu                           --
-- http://personas.grm.uci.cu/+marcos                 --
-- Centro de Tecnologías de Gestión de Datos (DATEC)  --
--   Contacto:                                        --
--   Correo: datec@uci.cu                             --
--   Telf: +53 07-837-3737                            --
--         +53 07-837-3714                            --
-- Universidad de las Ciencias Informáticas           --
-- http://www.uci.cu                                  --
--------------------------------------------------------