Create a non-superuser role in PostgreSQL

Use this easy procedure to establish a non-superuser role in PostgreSQL.

In PSQL, a “superuser” can override all access restrictions within the database. Superuser status is dangerous and should be used only when really needed. The following is a procedure to create a non-superuser role in PostgreSQL:

Step 1

Log into the database as a superuser and type \du to review all current roles.

Step 2

Create a new user account (in this case, it will be called ‘adimech’ and have the password ‘f9sh3jf5m3’):

CREATE USER adimech WITH PASSWORD 'f9sh3jf5m3';

Step 3

Alter the role to enable a non-expiring password:

ALTER ROLE adimech VALID UNTIL 'infinity';

Step 4

Alter the role to enable the creation of roles:

ALTER ROLE adimech CREATEROLE;

Step 5

Alter the role to prevent superuser:

ALTER ROLE adimech NOSUPERUSER;

Step 6

Grant all table privileges:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO adimech;

Step 7

Type \du to review the new role. The output may appear as follows:

Role name   |                   Attributes                   | Member of
------------+------------------------------------------------+-----------
 AdminUser  | Superuser, Create role, Create DB, Replication+| {}
            | Password valid until infinity                  |
 adimech    | Create role                                   +| {}
            | Password valid until infinity                  |
 postgres   | Superuser, Create role, Create DB, Replication | {}

Have Your Say

Δ