[SoCoSA/discuss] PostgreSQL Database Grants?

[Kevan Benson]

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

-- 
- Kevan Benson
- A-1 Networks