61 lines
1.9 KiB
Markdown
61 lines
1.9 KiB
Markdown
# 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
|
|
```
|