Skip site navigation (1) Skip section navigation (2)

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

From: James Long <pgsql-novice(at)museum(dot)rain(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Grant privs on entire database, not table-by-table-by-table
Date: 2011-11-22 03:57:03
Message-ID: 20111122035703.GA61024@ns.umpquanet.com (view raw or flat)
Thread:
Lists: pgsql-novice
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

Responses

pgsql-novice by date

Next:From: Jaime CasanovaDate: 2011-11-22 05:17:31
Subject: Re: Grant privs on entire database, not table-by-table-by-table
Previous:From: Andreas KretschmerDate: 2011-11-21 07:59:49
Subject: Re: Set NOT NULL error

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group