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
```