Title: "PostgreSQL basic commands" Date: 2017-06-01 20:06:32 Categories: [data base] tags: [postgresql]
$ sudo -u postgres psql
CREATE USER username WITH PASSWORD 'MYPASS';
You must edit pg_hba.conf in Debian/Ubuntu you have this dir structure:
/etc/postgresql/
└── 9.5
└── main
├── environment
├── pg_ctl.conf
├── pg_hba.conf
├── pg_ident.conf
├── postgresql.conf
└── start.conf
The above file looks like this:
# PostgreSQL Client Authentication Configuration File
#
# ...
#
# local DATABASE USER METHOD [OPTIONS]
# host DATABASE USER ADDRESS METHOD [OPTIONS]
# hostssl DATABASE USER ADDRESS METHOD [OPTIONS]
# hostnossl DATABASE USER ADDRESS METHOD [OPTIONS]
#
# ...
#
# METHOD can be "trust", "reject", "md5", "password", "gss", "sspi",
# "ident", "peer", "pam", "ldap", "radius" or "cert". Note that
# "password" sends passwords in clear text; "md5" is preferred since
# it sends encrypted passwords.
#
# ...
#
# Database administrative login by Unix domain socket
local all postgres peer
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 md5
host breinty_db breinty 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local replication postgres peer
#host replication postgres 127.0.0.1/32 md5
#host replication postgres ::1/128 md5
Then add your own rule to it.
ALTER USER username WITH SUPERUSER;
CREATE DATABASE dbname OWNER username;
DROP DATABASE dbname;
\l
$ psql username -h hostname -d dbname < dump.sql
\c dbname;
CREATE EXTENSION Postgis;
\dt
\q