Fwd: Question about Privileges

On Thu, Mar 12, 2009 at 3:45 PM, John R Pierce <pierce(at)hogranch(dot)com> wrote:

> Jack W wrote:
>
>>
>> I also find that if I only grant privileges on database to dbuser as
>> below, without granting privileges on Schema and table to dbuser, dbuser
>> still can not do SELECT on the tables.
>> mydb=# grant all privileges on Database mydb to dbuser;
>>
>> Is there any simple way to grant All privileges to dbuser on all the 10
>> tables?
>>
>
> the simplest way is to make dbuser the OWNER of the database, and have him
> create all the tables too, then he automatically has full rights to it.
>

Thanks. In my application, one user will create the database, then other
users can remotely access it through ODBC/JDBC to access the database. In
this case, I have to grant the privileges to each user one by one, right?

>
> SCHEMA privileges grant the rights to connect to a schema, and
> create/modify schemas
>
> table/view/etc privileges grant the rights to select/insert/update/etc the
> mentioned tables.
>
>
> In my test as below:
mydb=#select * from mydb_schema.mydb_table1;

If I only grant the privileges to the table:
mydb=# grant all privileges on mydb_schema.mydb_table1 to dbuser;

The SELECT will fail, the error is:

STATEMENT: select * from mydb_schema.mydb_table1;
ERROR: permission denied for schema mydb_schema

So I have to grant the privileges to the schema mydb_schema in order to do
SELECT on mydb_table1.

Jack