You can create postgresql superuser following two meyhme
1.To create the user joe
as a superuser, and assign a password immediately:
$ createuser -P -s -e joe
Enter password for new role: xyzzy
Enter it again: xyzzy
CREATE ROLE joe PASSWORD 'md5b5f5ba1a423792b526f799ae4eb3d59e' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;
2.Altering Existing User Permissions
Now that our librarian
user exists, we can begin using ALTER USER
to modify the permissions granted to librarian
.
The basic format of ALTER USER
includes the name of the user (or ROLE
) followed by a series of options
to inform PostgreSQL which permissive alterations to make:
=# ALTER USER role_specification WITH OPTION1 OPTION2 OPTION3;
These options range from CREATEDB
, CREATEROLE
, CREATEUSER
, and even SUPERUSER
. Additionally, most options also have a negative counterpart, informing the system that you wish to deny the user that particular permission. These option names are the same as their assignment counterpart, but are prefixed with NO
(e.g. NOCREATEDB
, NOCREATEROLE
, NOSUPERUSER
).
ASSIGNING SUPERUSER
PERMISSION
Now that we understand the basics of creating users and using ALTER USER
to modify permissions, we can quite simply use the SUPERUSER
option to assign our librarian
user SUPERUSER
permission:
=# ALTER USER librarian WITH SUPERUSER;
ALTER ROLE
Sure enough, if we display our permission list now, we’ll see librarian
has the new SUPERUSER
permission we want:
=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
librarian | Superuser | {}
postgres | Superuser, Create role, Create DB, Replication | {}
REVOKING PERMISSIONS
In the event that we make a mistake and assign a permission we later wish to revoke, simply issue the same ALTER USER
command but add the NO
prefix in front of the permissive options to be revoked.
For example, we can remove SUPERUSER
from our librarian
user like so:
=# ALTER USER librarian WITH NOSUPERUSER;
ALTER ROLE
=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
librarian | | {}
postgres | Superuser, Create role, Create DB, Replication | {}