Additional Grants To SuperUser?

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

2011/2/4 Carlos Mennens <carlos.mennens@gmail.com>

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?

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

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

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

--
// Dmitriy.

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

2011/2/7 Carlos Mennens <carlos.mennens@gmail.com>

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

Nothing special in 'postgres' user from the POV of DBMS. It is just a user
with superuser attribute created when you perform initdb(1).
But please note, some OS distributives uses 'postgres' for non-interactive
access to all databases for automatic maintenance (custom daily cronjobs,
replication, and similar tasks) -- please see you pg_hba.conf file where
entry for 'postgres' user usually resides.

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

--
// Dmitriy.