Thread: Additional Grants To SuperUser?

I created a role named 'carlos' which is my current user account with
'superuser' grants but my question is when I look at 'postgres'
account, he has additional grants that I don't understand.

            List of roles
 Role name | Attributes  | Member of
-----------+-------------+-----------
 carlos       | Superuser   | {}
 jmadeline  | Create DB   | {}
 mwilshaw  | Create DB   | {}
 postgres    | Superuser   | {}
           : Create role
           : Create DB

So from what I see above, 'carlos' is a superuser but do I need to
grant him 'CREATEROLE' & 'CREATEDB' rights along with 'SUPERUSER' or
is 'SUPERUSER' by itself good enough?

Not to be smart about it but you could just logon as carlos (or a different
superuser you create for this purpose) and issue "Create Database xxx" and
"Create Role xxx" statements and see whether they work.  A superuser should
(imo) be able to do everything (including dropping) without any additional
permissions required so unless you see that carlos cannot I would say you
are good.

David J

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Carlos Mennens
Sent: Friday, February 04, 2011 1:28 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Additional Grants To SuperUser?

I created a role named 'carlos' which is my current user account with
'superuser' grants but my question is when I look at 'postgres'
account, he has additional grants that I don't understand.

            List of roles
 Role name | Attributes  | Member of
-----------+-------------+-----------
 carlos       | Superuser   | {}
 jmadeline  | Create DB   | {}
 mwilshaw  | Create DB   | {}
 postgres    | Superuser   | {}
           : Create role
           : Create DB

So from what I see above, 'carlos' is a superuser but do I need to grant him
'CREATEROLE' & 'CREATEDB' rights along with 'SUPERUSER' or is 'SUPERUSER' by
itself good enough?

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, Feb 4, 2011 at 2:18 PM, David Johnston <polobo@yahoo.com> wrote:
> Not to be smart about it but you could just logon as carlos (or a different
> superuser you create for this purpose) and issue "Create Database xxx" and
> "Create Role xxx" statements and see whether they work.  A superuser should
> (imo) be able to do everything (including dropping) without any additional
> permissions required so unless you see that carlos cannot I would say you
> are good.

Yes but I'm trying to understand the difference because the default
'postgres' user that is auto-configured to have 'SUPERUSER',
'CREATEDB', & 'CREATEROLE' grants. I'm trying to understand if those
are redundant grants or if there is a reason PostgreSQL developers
grant the 'postgres' user with SUPERUSER, CREATEDB, & CREATEROLE.
Seems to me logically that if a someone is a superuser, then they
should be able to CREATEDB & CREATEROLE, no? So why would the
'postgres' user need those additional attributes?


postgres=# \du
            List of roles
 Role name  | Attributes  | Member of
------------+-------------+-----------
 cmennens   | Superuser   | {}
 postgres   | Superuser   | {}
            : Create role
            : Create DB

On Fri, Feb 4, 2011 at 5:08 PM, Dmitriy Igrishin <dmitigr@gmail.com> wrote:
> These all (SUPERUSER, CREATEDB, SUPERUSER) are role attributes.
> By performing ALTER ROLE postgres NOSUPERUSER it is possible to
> turn role with a superuser status into a role that just can create databases
> and manage roles (admin, but without superuser privileges).

So is it very bad to alter ANY of the default role attributes granted
to the 'postgres' user? I don't know if removing role attributes from
him will have negative consequences to features / functional tasks of
the PostgreSQL server / client application(s).