Thread: Defining Role Privileges

I'm wondering about my CREATE ROLE statements for PostgreSQL. I guess
I don't know if there's an official answer but I feel like I'm
entering a lot of redundant privileges to a role for example:

CREATE ROLE tom NOINHERIT LOGIN SUPERUSER CREATEDB CREATEROLE REPLICATION;
CREATE ROLE

My question is do I need to specify CREATEDB & CREATEROLE if I'm
already granting the SUPERUSER privilege? Seems kind of redundant to
me, no? Is there any logical reason someone would be a SUPERUSER and
not have CREATEDB or CREATEROLE?

Also when I generate a new role, is there any difference between using:

ALTER ROLE tom ENCRYPTED PASSWORD 'md5081bea17b5503506d29531af33cc6f4e';

\password tom

Is there a downside to using the \password psql command? Is it also
encrypted like the statement above? How do you create roles and do you
do it manually or have some kind of template?

Carlos Mennens <carlos.mennens@gmail.com> writes:
> My question is do I need to specify CREATEDB & CREATEROLE if I'm
> already granting the SUPERUSER privilege?

No, not really.  The point of those options is to grant privileges to
roles that aren't superuser.

> Also when I generate a new role, is there any difference between using:
> ALTER ROLE tom ENCRYPTED PASSWORD 'md5081bea17b5503506d29531af33cc6f4e';

> \password tom

Don't think so, except that in the former case you have to work out the
encrypted password by hand.  The latter is safer since the cleartext
password will not escape the psql executable.

            regards, tom lane

On 2012-02-08, Carlos Mennens <carlos.mennens@gmail.com> wrote:

> ALTER ROLE tom ENCRYPTED PASSWORD 'md5081bea17b5503506d29531af33cc6f4e';
>
> \password tom
>
> Is there a downside to using the \password psql command? Is it also
> encrypted like the statement above? How do you create roles and do you
> do it manually or have some kind of template?

I checked that a few weeks ago when doing the latter version the
password is translated into a form similar to former version
your chosen new password is sent over the wire encrypted.

--
⚂⚃ 100% natural