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 | {}
Comments
No comments have yet been submitted. Be the first!