1 like 0 dislike
81 views
asked in PostgreSQL Database Forum by (2.4k points)  
How will  you create postgresql super user any idea ? At the time of user creation,and how to give superuser privilege already existing user ?

Please log in or register to answer this question.

1 Answer

0 like 0 dislike
answered by (2k points)  

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 CREATEDBCREATEROLECREATEUSER, 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. NOCREATEDBNOCREATEROLENOSUPERUSER).

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 USERcommand but add the NO prefix in front of the permissive options to be revoked.

For example, we can remove SUPERUSERfrom 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 | {}

Related questions

0 like 0 dislike
1 answer
0 like 0 dislike
0 answers
0 like 0 dislike
1 answer
Welcome to PostgreSQL Database Discussion Forum where you can ask questions and receive answers from other members of the community. Can discuss here Oracle, Postgresql, mariadb , mySQL , AWS , Linux , MSSQL , MongoDB , Greenplum databases related queries ...etc.
...