Thread: Question about Privileges

Assume that I have the following database:

Database:    mydb
Schema:      mydb_schema
Tables:        mydb_table1
                   mydb_table2
                   mydb_table3   

I create a role:     Create Role dbuser LOGIN;

mydb=# grant all privileges on mydb_schema.mydb_table1 to dbuser;
mydb=# grant all privileges on SCHEMA mydb_schema to dbuser;

Remotely connect dbuser to mydb and run SELECT:
mydb=#select * from mydb_schema.mydb_table1;

Here I want to do a SELECT on mydb_table1 as the above line shows. It seems to me that I have to grant Privileges to dbuser on both the  mydb_schema and mydb_table1 so that the dbuser can run SELECT.
If there are 10 tables under mydb_schema, do I need to grant privileges to the dbuser  on all the 10 tables individully so that dbuser can operate the 10 tables?

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?

Thanks.

Jack

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.

there are also various scripts that can be used to enumerate objects and
grant specific privileges...

    http://pgedit.com/public/sql/acl_admin/index.html
    http://unf.be/postgresql/postgres_grant_all.perl
    http://www.archonet.com/pgdocs/grant-all.html

in general, DATABASE privileges relate to connecting to that database,
and having the rights to create schemas in that database.

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.

see http://www.postgresql.org/docs/current/static/sql-grant.html for
more specifics.

On Thursday 12 March 2009 5:00:39 pm Jack W wrote:
> On Thu, Mar 12, 2009 at 3:45 PM, John R Pierce <pierce@hogranch.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?

Easier to create a group role and assign it the privileges to the tables as you
create them. Then as you create users assign them to the group.

>
> > 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



--
Adrian Klaver
aklaver@comcast.net

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

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
>
>
>
>
>