pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedGRANT 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