Thread: Privilege problems: access denied on select for owner?

Hi all,

Having some trouble today accessing tables in a database:

sample=# \l+
                            List of databases
   Name    |  Owner   | Encoding | Tablespace |        Description
-----------+----------+----------+------------+---------------------------
 postgres  | postgres | UTF8     | pg_default |
 root      | root     | UTF8     | pg_default |
 sample     | sample    | UTF8     | pg_default |
[...]

sample=# \z users
      Access privileges for database "sample"
 Schema |     Name     | Type  | Access privileges
--------+--------------+-------+-------------------
 public | users | table |
(1 row)

sample=#

So the role "sample" owns the database "sample" and has default
privileges on the table "users" in that database. From the manual:

"There is no need to grant privileges to the owner of an object
(usually the user that created it), as the owner has all privileges by
default."

But:

sample@server:~$ psql -U sample sample
Welcome to psql 8.3.7, 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

sample=> select * from users;
ERROR:  permission denied for relation users
sample=>

I can SELECT from this table if I give the privilege to "sample" by
root on the table. But this is not what I want; I want "sample" to
have all privileges on all tables in the database "sample".

Any insight would be much appreciated.


Thanks,

Nathan

On Mon, Aug 03, 2009 at 01:39:08PM -0500, Nathan Jahnke wrote:
> So the role "sample" owns the database "sample" and has default
> privileges on the table "users" in that database. From the manual:
>
> "There is no need to grant privileges to the owner of an object
> (usually the user that created it), as the owner has all privileges by
> default."

I think all those owners are different because the user that creates
an object is its owner.  So if your users table wasn't created by your
sample user then it won't have permissions to access it.

You probably need to do:

  ALTER TABLE users OWNER TO sample;

--
  Sam  http://samason.me.uk/

Nathan Jahnke <njahnke@gmail.com> writes:
> So the role "sample" owns the database "sample" and has default
> privileges on the table "users" in that database. From the manual:

> "There is no need to grant privileges to the owner of an object
> (usually the user that created it), as the owner has all privileges by
> default."

Owning the database has little or nothing to do with owning the objects
within it.  You have not shown us who owns the table users, but I bet
it's not "sample".

            regards, tom lane