Обсуждение: Best practice to grant all privileges on all bjects in database?

Hello,

I need to grant all privileges on all objects in database. Without
using SUPERUSER.

It's strange that GRANT ALL PRIVILEGES ON DATABASE is useless, it
don't grant privileges on tables.

I've found out this "best practice", (more like ugly workaround):

select 'grant all on '||schemaname||'.'||tablename||' to
\\\"$USER\\\";' from pg_tables where schemaname in ('public');
select 'grant all on '||schemaname||'.'||viewname||' to
\\\"$USER\\\";' from pg_views where schemaname in ('public');

and same for functions,sequences etc.


Is there nicer, more friendly way? Maybe there is something like
contrib module or procedure that does that in user-friendly way?

If not, anyone has a better version of above grant script?

Thanks.

You can find some helpful grant scripts here:

http://pgedit.com/tip/postgresql/access_control_functions

On a related note, which objects need to be GRANTed specifically?
There is a saying that following objects can have permissions GRANTed:
1. TABLE
2. DATABASE
3. FUNCTION
4. LANGUAGE
5. SCHEMA
6. TABLESPACE

What about SEQUENCE, TRIGGER? PostgreSQL manual has no mention about this.

Thanks.

On 5/20/06, John DeSoi <desoi@pgedit.com> wrote:
> You can find some helpful grant scripts here:
>
> http://pgedit.com/tip/postgresql/access_control_functions
>
>
>
>
>
>  On 5/19/06, Joe Kramer <cckramer@gmail.com> wrote:
> > Hello,
> >
> > I need to grant all privileges on all objects in database. Without
> > using SUPERUSER.
> >
> > It's strange that GRANT ALL PRIVILEGES ON DATABASE is useless, it
> > don't grant privileges on tables.
> >
> > I've found out this "best practice", (more like ugly workaround):
> >
> > select 'grant all on '||schemaname||'.'||tablename||' to
> > \\\"$USER\\\";' from pg_tables where schemaname in ('public');
> > select 'grant all on '||schemaname||'.'||viewname||' to
> > \\\"$USER\\\";' from pg_views where schemaname in ('public');
> >
> > and same for functions,sequences etc.
> >
> >
> > Is there nicer, more friendly way? Maybe there is something like
> > contrib module or procedure that does that in user-friendly way?
> >
> > If not, anyone has a better version of above grant script?
> >
> >
>
>

On Mon, May 22, 2006 at 12:59:06 +0300,
  Joe Kramer <cckramer@gmail.com> wrote:
> On a related note, which objects need to be GRANTed specifically?
> There is a saying that following objects can have permissions GRANTed:
> 1. TABLE
> 2. DATABASE
> 3. FUNCTION
> 4. LANGUAGE
> 5. SCHEMA
> 6. TABLESPACE
>
> What about SEQUENCE, TRIGGER? PostgreSQL manual has no mention about this.

Did you look at:
http://developer.postgresql.org/docs/postgres/sql-grant.html

On May 24, 2006, at 10:50 AM, Bruno Wolff III wrote:
> On Mon, May 22, 2006 at 12:59:06 +0300,
>   Joe Kramer <cckramer@gmail.com> wrote:
>> On a related note, which objects need to be GRANTed specifically?
>> There is a saying that following objects can have permissions
>> GRANTed:
>> 1. TABLE
>> 2. DATABASE
>> 3. FUNCTION
>> 4. LANGUAGE
>> 5. SCHEMA
>> 6. TABLESPACE
>>
>> What about SEQUENCE, TRIGGER? PostgreSQL manual has no mention
>> about this.
>
> Did you look at:
> http://developer.postgresql.org/docs/postgres/sql-grant.html

Only helps if the OP is willing to run on HEAD; grant on sequence is
not in 8.1 (at least not according to the docs).

As for triggers, I don't really see how that would make any sense.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Em Quinta 25 Maio 2006 19:33, Jim Nasby escreveu:
>
> Only helps if the OP is willing to run on HEAD; grant on sequence is
> not in 8.1 (at least not according to the docs).
>
> As for triggers, I don't really see how that would make any sense.

A trigger could start some modification in a table where the user has no
direct access to, e.g. a logging table.  By granting access to the trigger
and making the trigger able to access that table, then the operation could be
completed and data could ba safe from users.  I dunno, though, if the
permissions set to the function would allow that...

--
Jorge Godoy           <jgodoy@gmail.com>

On 5/26/06, Jim Nasby <jnasby@pervasive.com> wrote:
you can grant on sequences using syntax for tables. works:

(pgdba@[local]:5810) 08:59:21 [depesz]
# create sequence test;
CREATE SEQUENCE

(pgdba@[local]:5810) 08:59:27 [depesz]
# \c - depesz
You are now connected as new user "depesz".

(depesz@[local]:5810) 08:59:29 [depesz]
> select nextval('test');
ERROR:  permission denied for sequence test

(depesz@[local]:5810) 08:59:34 [depesz]
> \c - pgdba
You are now connected as new user "pgdba".

(pgdba@[local]:5810) 08:59:36 [depesz]
# grant select, update on table test to depesz;
GRANT

(pgdba@[local]:5810) 08:59:43 [depesz]
# \c - depesz
You are now connected as new user "depesz".

(depesz@[local]:5810) 08:59:46 [depesz]
> select nextval('test');
 nextval
---------
       1
(1 row)


though i can't find it anywhere in documentation :(

depesz

--
http://www.depesz.com/ - nowy, lepszy depesz