[SoCoSA/discuss] PostgreSQL Database Grants?

[Augie Schwer]

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