Additional Grants To Superuser?

CarlosinFL · #1 (**permalink**) 02-04-11, 13:26

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.

Code:

            List of roles
 Role name | Attributes  | Member of 
-----------+-------------+-----------
 carlos    | Superuser   | {}
 jason | Create DB   | {}
 mike  | 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?

rski · #2 (**permalink**) 02-05-11, 15:33

SUPERUSER, CREATEDB and CREATE ROLE are not grants but attributes. See here PostgreSQL: Documentation: Manuals: PostgreSQL 9.0: Role Attributes

CarlosinFL · #3 (**permalink**) 02-07-11, 12:42

Quote:

Originally Posted by rski

SUPERUSER, CREATEDB and CREATE ROLE are not grants but attributes.

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

rski · #4 (**permalink**) 02-08-11, 17:32

Postgres is a default database administrator. Why do you want to change that?
Just set password for that accout and configure pg_hba.conf file to require possword when login as postgres user.

CarlosinFL · #5 (**permalink**) 02-08-11, 20:10

Quote:

Originally Posted by rski

Postgres is a default database administrator. Why do you want to change that?
Just set password for that accout and configure pg_hba.conf file to require possword when login as postgres user.

Thank you. One question however. I've configured my pg_hba.conf to require a password for all users but is there a way in PostgreSQL that I can see all attributes for a specific role? How would you know if your role 'bob' has LOGIN, INHERIT, or something else? When I run the \du command, not all attributes for the user are visible. Obviously I could try them out as that user but that seems time consuming and impractical.

rski · #6 (**permalink**) 02-10-11, 14:48

Check view pg_roles, it contain all informations you need.
See here
PostgreSQL: Documentation: Manuals: PostgreSQL 9.0: pg_roles

Internet Services

Web Development

Digital Marketing

Consumer Tech