Postgres Pro
Downloads
Home   >   mailing lists

Thread: ALTER USER versus GRANT/REVOKE

ALTER USER versus GRANT/REVOKE

From
Christoph Dalitz
Date:
Dear PG developers,

beeing used to Oracle, I am a bit confused by PG's SQL support for user rights:
Rather than with "GRANT createdb TO user" user rights are set via "ALTER USER".

Why?

I see the following problems with the current implementation:

a) It is not flexible for future extensions: you always need *two*
   keywords for the ALTER USER statement (CREATETABLE/NOCREATETABLE etc.)
   The User rights are boolean flags in pg_user, this requires changes
   in this table for every little new right (CONNECT, CREATEFUNCTION...).

b) (More important) It does not allow for roles with a user defined
   right profile. Eg. it should be possible for database administrators
   to define a custom group (eg. "developers") with specific rights:

     CREATE GROUP developers;
     GRANT CONNECT, CREATETABLE, CREATEFUNCTION to developers;

Or is there some way to accomplish this with the current implementation?

Christoph Dalitz

Re: ALTER USER versus GRANT/REVOKE

From
Ron Snyder
Date:
journal=> \h grant
Command:     GRANT
Description: Grants access privilege to a user, a group or all users
Syntax:
GRANT privilege [, ...] ON object [, ...]
    TO { PUBLIC | GROUP group | username }

Am I misunderstanding your question, or is this what you're looking for?

-ron


> -----Original Message-----
> From: Christoph Dalitz [mailto:christoph.dalitz@hs-niederrhein.de]
> Sent: Friday, August 30, 2002 1:36 AM
> To: PG Mailing List
> Subject: [GENERAL] ALTER USER versus GRANT/REVOKE
>
>
> Dear PG developers,
>
> beeing used to Oracle, I am a bit confused by PG's SQL
> support for user rights:
> Rather than with "GRANT createdb TO user" user rights are set
> via "ALTER USER".
>
> Why?
>
> I see the following problems with the current implementation:
>
> a) It is not flexible for future extensions: you always need *two*
>    keywords for the ALTER USER statement
> (CREATETABLE/NOCREATETABLE etc.)
>    The User rights are boolean flags in pg_user, this requires changes
>    in this table for every little new right (CONNECT,
> CREATEFUNCTION...).
>
> b) (More important) It does not allow for roles with a user defined
>    right profile. Eg. it should be possible for database
> administrators
>    to define a custom group (eg. "developers") with specific rights:
>
>      CREATE GROUP developers;
>      GRANT CONNECT, CREATETABLE, CREATEFUNCTION to developers;
>
> Or is there some way to accomplish this with the current
> implementation?
>
> Christoph Dalitz
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
http://archives.postgresql.org

Re: ALTER USER versus GRANT/REVOKE

From
Christoph Dalitz
Date:
On Fri, 30 Aug 2002 08:29:11 -0700
Ron Snyder <snyder@roguewave.com> wrote:
>
> journal=> \h grant
> Command:     GRANT
> Description: Grants access privilege to a user, a group or all users
> Syntax:
> GRANT privilege [, ...] ON object [, ...]
>     TO { PUBLIC | GROUP group | username }
>
> Am I misunderstanding your question, or is this what you're looking for?
>
I was not refering to access privileges for specific objects, but to
general user privileges like

    - CREATEDB
    - CREATEUSER
    - CREATETABLE    (not yet supported by PG)
    - CREATEFUNCTION (not yet supported by PG)
    - ...            (not yet supported by PG)

These are granted with ALTER USER in PG 7.2.

Although PG has very limited support for these at the moment, I see
a problem with the current implementation if it is extended to more
user privileges.

Christoph