Thread: Roles

It appears there are some predefined roles (superuser, admin) but I can't find a definition of these privileges in the
documentation.I understand that users and roles are equal (though create role is not = create user) and that roles can
beredefined but surely one of these roles is what I'm looking for and somewhere the default privileges are listed so I
canfigure out which one it is I want to grant to myself? 

TIA,
Bruce Hyatt

Bruce Hyatt <brucejhyatt@yahoo.com> writes:
> It appears there are some predefined roles (superuser, admin) but I
> can't find a definition of these privileges in the documentation.

There's no builtin concept of an "admin" role.  There is such a thing as
superuser, but that's a property of a role not a specific role (ie, you
can have more than one superuser role if you want).  See "Database roles
and privileges" chapter in the manual.  The CREATE ROLE reference page
has some details too.  Be sure to consult the manual version
corresponding to the PG version you are using, as the features in this
area have changed over time.

            regards, tom lane

--- On Fri, 11/21/08, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> There's no builtin concept of an "admin"
> role.  There is such a thing as
> superuser, but that's a property of a role not a
> specific role (ie, you
> can have more than one superuser role if you want).  See
> "Database roles
> and privileges" chapter in the manual.  The CREATE
> ROLE reference page
> has some details too.  Be sure to consult the manual
> version
> corresponding to the PG version you are using, as the
> features in this
> area have changed over time.
>
>             regards, tom lane

Thanks for the response Tom, especially at this day and hour.

I will revisit these pages but I would like to draw your attention to this page:

http://www.postgresql.org/docs/8.3/interactive/sql-createuser.html

Are the options not essentially existing roles?

Sincerely,
Bruce

On Fri, 2008-11-21 at 18:58 -0800, Bruce Hyatt wrote:
> --- On Fri, 11/21/08, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> > There's no builtin concept of an "admin"
> > role.  There is such a thing as
> > superuser, but that's a property of a role not a
> > specific role (ie, you
> > can have more than one superuser role if you want).  See
> > "Database roles
> > and privileges" chapter in the manual.  The CREATE
> > ROLE reference page
> > has some details too.  Be sure to consult the manual
> > version
> > corresponding to the PG version you are using, as the
> > features in this
> > area have changed over time.
> >
> >             regards, tom lane
>
> Thanks for the response Tom, especially at this day and hour.
>
> I will revisit these pages but I would like to draw your attention to
>  this page:
>
> http://www.postgresql.org/docs/8.3/interactive/sql-createuser.html
>
> Are the options not essentially existing roles?
>
> Sincerely,
> Bruce

They are not roles, but rather attributes that can be assigned to roles
as Tom said.  Ie, you can define a role myadmin, and add the superuser
attribute to that role so the myadmin user has superuser privileges:

CREATE ROLL myadmin WITH SUPERUSER LOGIN;

You can't login as user 'superuser' as such, because there is no such
role.  The closest to that is the postgres role (by convention) which by
default has superuser privileges. You may actually want most admin
functions to be done be a non superuser though, for better protection
against woopsies. ie,

CREATE ROLE myadmin WITH LOGIN CREATEDB CREATEROLE;

If you want more than one admin, perhaps then:

CREATE ROLE admin1 IN ROLE myadmin;

Regards,
Tim Bowden

--
Experience is that marvelous thing that enables you recognize a mistake
when you make it again.