setup/doc/postgres-create-user.md

Create User with Postgres

• create a database user called user name with password key

CREATE USER <user name> with encrypted password 'key';

• create a SUPERUSER user called user name with password key

CREATE USER <user name> with SUPERUSER encrypted password 'key';

• OPTION: gant user user name access to database database name

GRANT ALL PRIVILEGES ON DATABASE <database name> TO <user name>;

• OPTION: add all privileges for a schema name to a user

GRANT ALL PRIVILEGES ON ALL TABALES ON SCHEMA <schema name> TO <user name>;

• OPTION: if user name already exist, pw can be changed this way

ALTER USER <user name> WITH ENCRYPTED PASSWORD '<key>';

• OPTION: gant user user name access to database postgres so that this user is able to login without providing a database

sudo -u postgres psql -W
GRANT ALL PRIVILEGES ON DATABASE postgres to <user name>;

• OPTION: gant user user name permission to create databases

sudo -u postgres psql -W
ALTER USER <user name> CREATEDB;

• edit the pg_hba.conf file
  NOTE: Insert the postgresql database version that is running on the respective host

sudo cp /etc/postgresql/<psql version>/main/pg_hba.conf /etc/postgresql/<psgl version>/main/pg_hba.conf-backup
sudo vi /etc/postgresql/<psql version>/main/pg_hba.conf

• in order to be able to run applications like e.g. Spring Boot with a local PostgreSQL installation, change the authentication method for the Unix domain socket and local connections to trust like this

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust

• restart PostgreSQL to enable the changes

systemctl status postgresql
sudo systemctl restart postgresql
systemctl status postgresql