pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
GRANT ALL PRIVILEGES ON DATABASE
12+ messages / 6 participants
[nested] [flat]

* GRANT ALL PRIVILEGES ON DATABASE
@ 2005-08-16 23:07 Andrew Gold <agold@cbamedia.com>
  2005-08-17 00:00 ` Re: GRANT ALL PRIVILEGES ON DATABASE Oliver Elphick <olly@lfix.co.uk>
  0 siblings, 1 reply; 12+ messages in thread

From: Andrew Gold @ 2005-08-16 23:07 UTC (permalink / raw)
  To: pgsql-admin

Hi everyone,

I'm sure there's an easy answer for this question, but I confess the 
situation puzzles me.

I have database "example1."
I have example1 owner user "user1."

user1 is not a super user.

I have superuser "su1."

su1 creates table "lookatme" in example1. su1 now realizes that he wants 
to give control of table lookatme and a number of other tables (all of 
them) to user1, so he executes the following sql:

GRANT ALL PRIVILEGES ON DATABASE example1 TO user1.

user1 now executes the following sql:

SELECT * FROM lookatme;

The response is: ERROR:  permission denied for relation lookatme

Obviously, I can write a script to iterate through all the tables, but 
what exactly does "GRANT ALL PRIVILEGES ON DATABASE XXX" do if it 
doesn't even grant basic access?

Any insight on the subject is welcome.

Thanks,

Andrew Gold



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: GRANT ALL PRIVILEGES ON DATABASE
  2005-08-16 23:07 GRANT ALL PRIVILEGES ON DATABASE Andrew Gold <agold@cbamedia.com>
@ 2005-08-17 00:00 ` Oliver Elphick <olly@lfix.co.uk>
  2005-08-17 15:35   ` Re: GRANT ALL PRIVILEGES ON DATABASE Andrew Gold <agold@cbamedia.com>
  0 siblings, 1 reply; 12+ messages in thread

From: Oliver Elphick @ 2005-08-17 00:00 UTC (permalink / raw)
  To: Andrew Gold <agold@cbamedia.com>; +Cc: pgsql-admin

On Tue, 2005-08-16 at 16:07 -0700, Andrew Gold wrote:
> Obviously, I can write a script to iterate through all the tables, but 
> what exactly does "GRANT ALL PRIVILEGES ON DATABASE XXX" do if it 
> doesn't even grant basic access?

See the man page for GRANT.

It gives the right to create schemas and temporary tables in the
database.

-- 
Oliver Elphick                                          olly@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
   Do you want to know God?   http://www.lfix.co.uk/knowing_god.html




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: GRANT ALL PRIVILEGES ON DATABASE
  2005-08-16 23:07 GRANT ALL PRIVILEGES ON DATABASE Andrew Gold <agold@cbamedia.com>
  2005-08-17 00:00 ` Re: GRANT ALL PRIVILEGES ON DATABASE Oliver Elphick <olly@lfix.co.uk>
@ 2005-08-17 15:35   ` Andrew Gold <agold@cbamedia.com>
  2005-08-17 15:57     ` Re: GRANT ALL PRIVILEGES ON DATABASE Alvaro Herrera <alvherre@alvh.no-ip.org>
  2005-08-17 16:01     ` Re: GRANT ALL PRIVILEGES ON DATABASE Oliver Elphick <olly@lfix.co.uk>
  0 siblings, 2 replies; 12+ messages in thread

From: Andrew Gold @ 2005-08-17 15:35 UTC (permalink / raw)
  To: pgsql-admin

Actually, I read the GRANT man page several times; what you state so 
clearly and simply, isn't described so plainly in that document.

I posted because I found the GRANT man page deficient.

Strictly speaking the GRANT man page indicated the following:

-----------------begin quote---------------------------
ALL PRIVILEGES

 Grant all of the privileges applicable to the object at once. The 
PRIVILEGES key word is optional in PostgreSQL, though it is required by 
strict SQL.

-------------------end quote---------------------------

This followed a series of descriptions of various privileges (select, 
insert, update, etc.). From the context of the description, it appears 
that "GRANT ALL PRIVILEGES" bestows all of the preceding rights on the 
recipient user.

It is not at all intuitive that "GRANT ALL PRIVILEGES" gives a user the 
right to create schemas and temporary tables, and that alone.

Whether you meant it or not, your initial comment came across as 
patronizing.

Andrew Gold


Oliver Elphick wrote:

>On Tue, 2005-08-16 at 16:07 -0700, Andrew Gold wrote:
>  
>
>>Obviously, I can write a script to iterate through all the tables, but 
>>what exactly does "GRANT ALL PRIVILEGES ON DATABASE XXX" do if it 
>>doesn't even grant basic access?
>>    
>>
>
>See the man page for GRANT.
>
>It gives the right to create schemas and temporary tables in the
>database.
>
>  
>




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: GRANT ALL PRIVILEGES ON DATABASE
  2005-08-16 23:07 GRANT ALL PRIVILEGES ON DATABASE Andrew Gold <agold@cbamedia.com>
  2005-08-17 00:00 ` Re: GRANT ALL PRIVILEGES ON DATABASE Oliver Elphick <olly@lfix.co.uk>
  2005-08-17 15:35   ` Re: GRANT ALL PRIVILEGES ON DATABASE Andrew Gold <agold@cbamedia.com>
@ 2005-08-17 15:57     ` Alvaro Herrera <alvherre@alvh.no-ip.org>
  1 sibling, 0 replies; 12+ messages in thread

From: Alvaro Herrera @ 2005-08-17 15:57 UTC (permalink / raw)
  To: Andrew Gold <agold@cbamedia.com>; +Cc: pgsql-admin

On Wed, Aug 17, 2005 at 08:35:06AM -0700, Andrew Gold wrote:

> This followed a series of descriptions of various privileges (select, 
> insert, update, etc.). From the context of the description, it appears 
> that "GRANT ALL PRIVILEGES" bestows all of the preceding rights on the 
> recipient user.
> 
> It is not at all intuitive that "GRANT ALL PRIVILEGES" gives a user the 
> right to create schemas and temporary tables, and that alone.

If you have a suggestion for a better wording, please share -- there's
always room for improving the documentation.

-- 
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"Tiene valor aquel que admite que es un cobarde" (Fernandel)



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: GRANT ALL PRIVILEGES ON DATABASE
  2005-08-16 23:07 GRANT ALL PRIVILEGES ON DATABASE Andrew Gold <agold@cbamedia.com>
  2005-08-17 00:00 ` Re: GRANT ALL PRIVILEGES ON DATABASE Oliver Elphick <olly@lfix.co.uk>
  2005-08-17 15:35   ` Re: GRANT ALL PRIVILEGES ON DATABASE Andrew Gold <agold@cbamedia.com>
@ 2005-08-17 16:01     ` Oliver Elphick <olly@lfix.co.uk>
  1 sibling, 0 replies; 12+ messages in thread

From: Oliver Elphick @ 2005-08-17 16:01 UTC (permalink / raw)
  To: Andrew Gold <agold@cbamedia.com>; +Cc: pgsql-admin

On Wed, 2005-08-17 at 08:35 -0700, Andrew Gold wrote:
> Actually, I read the GRANT man page several times; what you state so 
> clearly and simply, isn't described so plainly in that document.
> 
> I posted because I found the GRANT man page deficient.
> 
> Strictly speaking the GRANT man page indicated the following:
> 
> -----------------begin quote---------------------------
> ALL PRIVILEGES
> 
>  Grant all of the privileges applicable to the object at once. The 
> PRIVILEGES key word is optional in PostgreSQL, though it is required by 
> strict SQL.
> 
> -------------------end quote---------------------------
> 
> This followed a series of descriptions of various privileges (select, 
> insert, update, etc.). From the context of the description, it appears 
> that "GRANT ALL PRIVILEGES" bestows all of the preceding rights on the 
> recipient user.
> 
> It is not at all intuitive that "GRANT ALL PRIVILEGES" gives a user the 
> right to create schemas and temporary tables, and that alone.

You missed the bit where it describes what privileges apply to various
kinds of object.

> Whether you meant it or not, your initial comment came across as 
> patronizing.

Sorry about that.
-- 
Oliver Elphick                                          olly@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
   Do you want to know God?   http://www.lfix.co.uk/knowing_god.html




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* GRANT ALL PRIVILEGES ON DATABASE
@ 2005-08-16 23:17 Andrew Gold <agold@cbamedia.com>
  0 siblings, 0 replies; 12+ messages in thread

From: Andrew Gold @ 2005-08-16 23:17 UTC (permalink / raw)
  To: pgsql-admin

Hi everyone,

I'm sure there's an easy answer for this question, but I confess the
situation puzzles me.

I have database "example1."
I have example1 owner user "user1."

user1 is not a super user.

I have superuser "su1."

su1 creates table "lookatme" in example1. su1 now realizes that he wants
to give control of table lookatme and a number of other tables (all of
them) to user1, so he executes the following sql:

GRANT ALL PRIVILEGES ON DATABASE example1 TO user1.

user1 now executes the following sql:

SELECT * FROM lookatme;

The response is: ERROR:  permission denied for relation lookatme

Obviously, I can write a script to iterate through all the tables, but
what exactly does "GRANT ALL PRIVILEGES ON DATABASE XXX" do if it
doesn't even grant basic access?

Any insight on the subject is welcome.

Thanks,

Andrew Gold




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: GRANT ALL PRIVILEGES ON DATABASE
@ 2005-08-17 15:14 Zuoxin.Wang@kp.org
  2005-08-17 15:54 ` Re: GRANT ALL PRIVILEGES ON DATABASE Bruno Wolff III <bruno@wolff.to>
  2005-08-17 16:08 ` Re: GRANT ALL PRIVILEGES ON DATABASE Oliver Elphick <olly@lfix.co.uk>
  0 siblings, 2 replies; 12+ messages in thread

From: Zuoxin.Wang@kp.org @ 2005-08-17 15:14 UTC (permalink / raw)
  To: olly@lfix.co.uk; +Cc: agold@cbamedia.com; pgsql-admin

Could you tell us what is the best way to grant a user who can select all 
data from all tables, if I don't like to write a script which Andrew 
memtioned earlier.

Thanks.





Oliver Elphick <olly@lfix.co.uk>
Sent by: pgsql-admin-owner@postgresql.org
08/16/2005 05:00 PM
Please respond to olly

 
        To:     Andrew Gold <agold@cbamedia.com>
        cc:     pgsql-admin@postgresql.org
        Subject:        Re: [ADMIN] GRANT ALL PRIVILEGES ON DATABASE


On Tue, 2005-08-16 at 16:07 -0700, Andrew Gold wrote:
> Obviously, I can write a script to iterate through all the tables, but 
> what exactly does "GRANT ALL PRIVILEGES ON DATABASE XXX" do if it 
> doesn't even grant basic access?

See the man page for GRANT.

It gives the right to create schemas and temporary tables in the
database.

-- 
Oliver Elphick                                          olly@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
   Do you want to know God?   http://www.lfix.co.uk/knowing_god.html


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org

^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: GRANT ALL PRIVILEGES ON DATABASE
  2005-08-17 15:14 Re: GRANT ALL PRIVILEGES ON DATABASE Zuoxin.Wang@kp.org
@ 2005-08-17 15:54 ` Bruno Wolff III <bruno@wolff.to>
  1 sibling, 0 replies; 12+ messages in thread

From: Bruno Wolff III @ 2005-08-17 15:54 UTC (permalink / raw)
  To: Zuoxin.Wang@kp.org; +Cc: olly@lfix.co.uk; agold@cbamedia.com; pgsql-admin

On Wed, Aug 17, 2005 at 08:14:02 -0700,
  Zuoxin.Wang@kp.org wrote:
> Could you tell us what is the best way to grant a user who can select all 
> data from all tables, if I don't like to write a script which Andrew 
> memtioned earlier.

Other than some kind of script, you can make them a superuser. That may not
work for you though.



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: GRANT ALL PRIVILEGES ON DATABASE
  2005-08-17 15:14 Re: GRANT ALL PRIVILEGES ON DATABASE Zuoxin.Wang@kp.org
@ 2005-08-17 16:08 ` Oliver Elphick <olly@lfix.co.uk>
  2005-08-18 14:37   ` Re: GRANT ALL PRIVILEGES ON DATABASE Tim Goodaire <tgoodaire@linux.ca>
  1 sibling, 1 reply; 12+ messages in thread

From: Oliver Elphick @ 2005-08-17 16:08 UTC (permalink / raw)
  To: Zuoxin.Wang@kp.org; +Cc: agold@cbamedia.com; pgsql-admin

On Wed, 2005-08-17 at 08:14 -0700, Zuoxin.Wang@kp.org wrote:
> 
> Could you tell us what is the best way to grant a user who can select
> all data from all tables, if I don't like to write a script which
> Andrew memtioned earlier. 

Here's a quick way to generate a script and then run it, all from within
a psql session.  This grants all privileges on all visible tables to a
user called that_user.  It works by generating the commands and writing
them to a file, then executing that file as an SQL script:

$ psql -d my_database
\a
\t
\o /tmp/grant_privileges
select 'GRANT ALL PRIVILEGES ON ' || c.relname || ' TO that_user;' from
pg_catalog.pg_class AS c LEFT JOIN pg_catalog.pg_namespace AS n ON n.oid
= c.relnamespace where relkind = 'r' AND n.nspname NOT IN ('pg_catalog',
'pg_toast') AND pg_catalog.pg_table_is_visible(c.oid);
\o
\i /tmp/grant_privileges

-- 
Oliver Elphick                                          olly@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
   Do you want to know God?   http://www.lfix.co.uk/knowing_god.html




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: GRANT ALL PRIVILEGES ON DATABASE
  2005-08-17 15:14 Re: GRANT ALL PRIVILEGES ON DATABASE Zuoxin.Wang@kp.org
  2005-08-17 16:08 ` Re: GRANT ALL PRIVILEGES ON DATABASE Oliver Elphick <olly@lfix.co.uk>
@ 2005-08-18 14:37   ` Tim Goodaire <tgoodaire@linux.ca>
  0 siblings, 0 replies; 12+ messages in thread

From: tgoodaire@linux.ca @ 2005-08-18 14:37 UTC (permalink / raw)
  To: Zuoxin.Wang@kp.org; +Cc: pgsql-admin

> On Wed, 2005-08-17 at 08:14 -0700, Zuoxin.Wang@kp.org wrote:
> > 
> > Could you tell us what is the best way to grant a user who can select
> > all data from all tables, if I don't like to write a script which
> > Andrew memtioned earlier. 

You may be interested in the acl admin plpgsql scripts that Andrew
Hammond wrote to grant/revoke privileges: http://pgedit.com/public/sql/acl_admin/index.html 

Tim

Attachments:

  signature.asc (application/pgp-signature, 188B) download

^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: GRANT ALL PRIVILEGES ON DATABASE
@ 2005-08-17 16:17 Zuoxin.Wang@kp.org
  2005-08-18 03:10 ` Re: GRANT ALL PRIVILEGES ON DATABASE Bruno Wolff III <bruno@wolff.to>
  0 siblings, 1 reply; 12+ messages in thread

From: Zuoxin.Wang@kp.org @ 2005-08-17 16:17 UTC (permalink / raw)
  To: olly@lfix.co.uk; +Cc: agold@cbamedia.com; pgsql-admin

Which means everytime I create a new table or a new schema, I have to run 
the script again, right?

Thanks.





Oliver Elphick <olly@lfix.co.uk>
08/17/2005 09:08 AM
Please respond to olly

 
        To:     Zuoxin Wang/CA/KAIPERM@Kaiperm
        cc:     agold@cbamedia.com, pgsql-admin@postgresql.org
        Subject:        Re: [ADMIN] GRANT ALL PRIVILEGES ON DATABASE


On Wed, 2005-08-17 at 08:14 -0700, Zuoxin.Wang@kp.org wrote:
> 
> Could you tell us what is the best way to grant a user who can select
> all data from all tables, if I don't like to write a script which
> Andrew memtioned earlier. 

Here's a quick way to generate a script and then run it, all from within
a psql session.  This grants all privileges on all visible tables to a
user called that_user.  It works by generating the commands and writing
them to a file, then executing that file as an SQL script:

$ psql -d my_database
\a
\t
\o /tmp/grant_privileges
select 'GRANT ALL PRIVILEGES ON ' || c.relname || ' TO that_user;' from
pg_catalog.pg_class AS c LEFT JOIN pg_catalog.pg_namespace AS n ON n.oid
= c.relnamespace where relkind = 'r' AND n.nspname NOT IN ('pg_catalog',
'pg_toast') AND pg_catalog.pg_table_is_visible(c.oid);
\o
\i /tmp/grant_privileges

-- 
Oliver Elphick                                          olly@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
   Do you want to know God?   http://www.lfix.co.uk/knowing_god.html

^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: GRANT ALL PRIVILEGES ON DATABASE
  2005-08-17 16:17 Re: GRANT ALL PRIVILEGES ON DATABASE Zuoxin.Wang@kp.org
@ 2005-08-18 03:10 ` Bruno Wolff III <bruno@wolff.to>
  0 siblings, 0 replies; 12+ messages in thread

From: Bruno Wolff III @ 2005-08-18 03:10 UTC (permalink / raw)
  To: Zuoxin.Wang@kp.org; +Cc: olly@lfix.co.uk; agold@cbamedia.com; pgsql-admin

On Wed, Aug 17, 2005 at 09:17:28 -0700,
  Zuoxin.Wang@kp.org wrote:
> Which means everytime I create a new table or a new schema, I have to run 
> the script again, right?

You will need to do something to grant access to the new object to whoever
is supposed to have access.



^ permalink  raw  reply  [nested|flat] 12+ messages in thread


end of thread, other threads:[~2005-08-18 14:37 UTC | newest]

Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-16 23:07 GRANT ALL PRIVILEGES ON DATABASE Andrew Gold <agold@cbamedia.com>
2005-08-17 00:00 ` Oliver Elphick <olly@lfix.co.uk>
2005-08-17 15:35   ` Andrew Gold <agold@cbamedia.com>
2005-08-17 15:57     ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2005-08-17 16:01     ` Oliver Elphick <olly@lfix.co.uk>
2005-08-16 23:17 GRANT ALL PRIVILEGES ON DATABASE Andrew Gold <agold@cbamedia.com>
2005-08-17 15:14 Re: GRANT ALL PRIVILEGES ON DATABASE Zuoxin.Wang@kp.org
2005-08-17 15:54 ` Bruno Wolff III <bruno@wolff.to>
2005-08-17 16:08 ` Oliver Elphick <olly@lfix.co.uk>
2005-08-18 14:37   ` Tim Goodaire <tgoodaire@linux.ca>
2005-08-17 16:17 Re: GRANT ALL PRIVILEGES ON DATABASE Zuoxin.Wang@kp.org
2005-08-18 03:10 ` Bruno Wolff III <bruno@wolff.to>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox