[SoCoSA/discuss] PostgreSQL Database Grants?

[Susan Baur]

Augie,

Does Postgres support roles at all? If it did, you could grant the  
privileges to a role, add the appropriate folks to the role and then  
only have to grant subsequent privileges to that role instead of all  
the people in it. Still not as easy as MySQL but worth exploring if  
it does.

--Susan


On Jul 10, 2006, at 12:31 PM, Augie Schwer wrote:

> On 7/7/06, Kevan Benson <kbenson at a-1networks.com> wrote:
>> On Thursday 06 July 2006 17:32, Augie Schwer wrote:
>> > So in MySQL if you want to grant privileges on all tables in a
>> > database (even future ones) you say something like:
>> >
>> > GRANT ALL PRIVILEGES ON dbname.* TO luser;
>> >
>> > But Postgres seems to only understand such SQL query grants for
>> > existing table names.
>> > (http://www.postgresql.org/docs/8.0/interactive/sql-grant.html)  
>> Which
>> > would be a real pain in the butt if say you have 100 tables that  
>> need
>> > grants, or heaven forbid you actually added another 100 tables  
>> to an
>> > existing DB.
>> >
>> > Does anyone know of a good solution? I googled around and all the
>> > solutions of creating functions and re-running those functions  
>> every
>> > time you add a table seemed pretty hackish.
>>
>> Not all that familiar with Postgres, but if the underlying  
>> mechanism is
>> similar to MySQL, and the GRANT syntax is just a wrapper to a few  
>> inserts i
>> the administrative DB, you could just manually add the  
>> corresponding rows to
>> the administrative DB without using the GRANT command, which I  
>> assume is what
>> is doing the check that the DB exists.
>>
>> e.g., in MySQL, you can manually add entries to the mysql.user and  
>> mysql.db
>> tables and flush privileges to allow access from a specific user  
>> (set up in
>> the user table) coming from a specific host (set up in the db table).
>
> Right, but where MySQL does the grants proactively, PostgreSQL only
> allows you to do them retroactively.
>
> So in MySQL I can say "GRANT SELECT ON dbname.* to developer" and so
> "developer" has privileges on the whole tables space under that DB;
> even if a new table gets created the developer will still have
> privileges.
>
> In Postgres if you add a new table you have to redo the grants, which
> makes administering it a real pain, since your developers or your
> customers must now wait on the DBA to update the grant table.
>
>
> -- 
> Augie Schwer    -    Augie at Schwer.us    -    http://schwer.us
> Key fingerprint = 9815 AE19 AFD1 1FE7 5DEE 2AC3 CB99 2784 27B0 C072
>
> _______________________________________________
> SoCoSA discuss mailing list
> discuss at socosa.org
> Your address: susan at cdl.edu
> http://socosa.org/mailman/listinfo/discuss
> http://socosa.org/mailman/options/discuss/susan%40cdl.edu