Postgres (SQL) - Create user
by
Jeremy Canfield |
Updated: August 28 2023
| 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