Thread: Grant privs on entire database, not table-by-table-by-table

Under a normal role, I am the owner of a database.

CREATE DATABASE buddy_db WITH TEMPLATE = template0 ENCODING = 'SQL_ASCII';

ALTER DATABASE buddy_db OWNER TO james;


As superuser, I have granted all privs on that database to
another role:

$ psql -U pgsql buddy_db
Welcome to psql 8.3.15, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

buddy_db=# grant all on database buddy_db to buddy;
GRANT
buddy_db=#


But now when I use that role to connect to that database, I don't
have privs:

$ psql -U buddy buddy_db
Welcome to psql 8.3.15, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

buddy_db=> \d
              List of relations
 Schema |        Name         | Type  | Owner
--------+---------------------+-------+-------
 public | colors              | table | james
 public | shapes              | table | james
 public | sounds              | table | james
(3 rows)

buddy_db=> select sum(1) from sounds;
ERROR:  permission denied for relation sounds
buddy_db=>

What am I doing wrong?

Thank you!


Jim

On Mon, Nov 21, 2011 at 10:57 PM, James Long
<pgsql-novice@museum.rain.com> wrote:
>
> buddy_db=# grant all on database buddy_db to buddy;
> GRANT
> buddy_db=#
>

this is not for granting privileges on all objects in the database but
for granting all types of privileges on the database to this user.
types of privileges for a database are: CREATE | CONNECT | TEMPORARY |
TEMP

what you're looking for is not possible in 8.3, the ability to grant
privileges to several objects in 1 command was introduced in 9.0 and
even there is limited to one kind of object in one schema at a time.

a workaround in 8.3 would be to read the catalogs and generate a
script, below sql would generate such a script for tables you should
try to make your own for other objects:
"""
copy
(select 'GRANT ALL ON TABLE ' || table_schema || '.' || table_name ||
' TO buddy; '
    from information_schema.tables
  where table_schema not in ('pg_catalog', 'information_schema')
     and table_type = 'BASE TABLE'
) to '/tmp/grants.sql';
"""
--
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación