Thread: GRANT ALL PRIVILEGES ON DATABASE

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

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

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

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)

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

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

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.

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.

> 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