Search Postgresql Archives

Re: Fwd: Question about Privileges

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





Jack W schrieb:

On Thu, Mar 12, 2009 at 3:45 PM, John R Pierce <pierce@xxxxxxxxxxxx <mailto:pierce@xxxxxxxxxxxx>> 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.

This is a common "mistake". You have to grant the privileges always also to the schema explicit and not just to the table in a schema. PostgreSQL is not going so far to warn you, that the user has now the privileges on the table but *not* to the schema.

As Adrian allready mentioned, you should use group roles and then grant the privileges for the schema to the group role if you have more different users ...

Cheers

Andy

--
St.Pauli - Hamburg - Germany

Andreas Wenk

Jack










--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux