Re: Roles

On Fri, 2008-11-21 at 18:58 -0800, Bruce Hyatt wrote:
> --- On Fri, 11/21/08, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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.