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
Show quoted text
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 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.
Show quoted text
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. TABLESPACEWhat 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. TABLESPACEWhat 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:
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).
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