インストール

% sudo apt-get update
% sudo apt-get install -y postgresql

セットアップ

初期設定

% sudo -i -u postgres
postgres% psql
postgres=# \du
List of roles
Role name |                         Attributes                         | Member of
----------+------------------------------------------------------------+-----------
postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# \password postgres
Enter password for new role:
Enter it again:

ユーザ作成

postgres% createuser -P hoge
postgres% psql
postgres=# \du
List of roles
Role name |                         Attributes                         | Member of
----------+------------------------------------------------------------+-----------
hoge      |                                                            | {}
postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# alter role hoge with password 'password';

DB作成

postgres=# create database geho;
CREATE DATABASE
postgres=# \l
                              List of databases
Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
geho      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
  　　     |          |          |             |             | postgres=CTc/postgres
template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
　        |          |          |             |             | postgres=CTc/postgres

DBに権限付与

postgres-# GRANT ALL ON DATABASE geho TO hoge;
postgres-# \l
                            List of databases
Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
geho    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
|          |          |             |             | postgres=CTc/postgres+
|          |          |             |             | ii=CTc/postgres
template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
|          |          |             |             | postgres=CTc/postgres
template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
|          |          |             |             | postgres=CTc/postgres
(4 rows)

一般ユーザによるDBアクセス

hoge% psql postgres hoge
postgres=> \connect geho
geho=> CREATE TABLE fuga
geho-> (id int, name character varying(5));
CREATE TABLE
geho=> \dt
      List of relations
Schema | Name | Type  | Owner
--------+------+-------+-------
public | fuga | table | hoge
(1 row)
geho=> SELECT * FROM fuga;
id | name
----+------
(0 rows)

#参考文献
https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-16-04