Inside Psql

List DB

\l

Change DB

\c dbname

List extensions

\dx

Execute a file

psql --file create_db_with_user.sql

Execute a file with arguments

psql --file create_db_with_user.sql -v db="dbname"

# Note the ' this is necessary to interpret as string
psql --file create_db_with_user.sql -v some_string="'password'"

No access?

By default postgres only allow access using "ident" mode or "peer" mode (unix user)

su postgres
psql

Edit pg_hba.conf to allow access by password

pg_hba="/var/lib/pgsql/12/data/pg_hba.conf"
cp -r $pg_hba "${pg_hba}.bak"
sed -i '/^host[ ]*all[ ]*all[ ]*127.0.0.1\/32[ ]*ident/ s/ident/md5/' $pg_hba
sed -i '/^host[ ]*all[ ]*all[ ]*::1\/128[ ]*ident/ s/ident/md5/' $pg_hba

Create a new db and a user for it

su - postgres
createdb dbname
psql -d dbname

CREATE USER username pasword 'userpwd';
GRANT ALL PRIVILEGES ON DATABASE dbname TO username;

Now you can user other user

psql -U username -d dbname -h localhost

Access DB from inside Docker Container

Edit pg_hba.confg

find / -name "pg_hba.confg.conf"  -exec vi {} \;

Find docker network bridge info:

docker network inspect bridge

And change IPv4 to

host    all             all             172.17.0.0/8            md5

According to docker network inspect bridge CIDR should be /16 but actually only /8 works.

Edit postgresql.conf

find / -name "postgresql.conf"  -exec vi {} \;

And change listen_addresses to docker's gateway inet

listen_addresses = 'localhost,172.17.0.1'

Restart pgsql

systemctl restart postgresql-12