Bootstrap FreeKB - Postgres (SQL) - Create user
Postgres (SQL) - Create user

Updated:   |  Postgres (SQL) articles

psql can be used to create a user.

~]$ sudo -u postgres psql
postgres=# create user <username> with encrypted password '<password>';

 

Or the createuser command can be used.

sudo -u postgres createuser <username>

 

Then the psql alter user command can be used to set the users password.

$ sudo -u postgres psql
postgres=# alter user <username> with encrypted password '<password>';

 

At this point your user will now exist but have no roles.

~]$ sudo -u postgres psql
psql (9.2.24)
Type "help" for help.

postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 johndoe   |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}

 

Or like this.

~]$ sudo -u postgres psql --command "\du" 
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 johndoe   |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}

 

You way want to grant the user roles.

alter role johndoe with superuser;
alter role johndoe with createrole;
alter role johndoe with createdb;
alter role johndoe with replication;

 

Then update /var/lib/pgsql/data/pg_hba.conf to have "peer" for postgres user and md5 for the user account you just created. MD5 is used so that you can sign in using a password.

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer
local   all             johndoe                                 md5

 

SInce a change was made to a configuration file, restart Postgres for this change to take effect.

sudo systemctl restart postgresql

 




Did you find this article helpful?

If so, consider buying me a coffee over at Buy Me A Coffee



Comments


Add a Comment


Please enter 3b8e89 in the box below so that we can be sure you are a human.