brew install postgresql

brew services start postgresql

rm /usr/local/var/postgresql@12/postmaster.pid

psql postgres

\l+

\c dbname

CREATE DATABASE mydb;

# Command line samples
createdb -U postgres mydb 
podman exec Bemanning createdb -U postgres Bemanning

drop database mydb;

or from terminal:

dropdb -U db_owner_username -i [-h host] mysitedb

\du+

SELECT usename AS role_name,
 CASE
  WHEN usesuper AND usecreatedb THEN
    CAST('superuser, create database' AS pg_catalog.text)
  WHEN usesuper THEN
    CAST('superuser' AS pg_catalog.text)
  WHEN usecreatedb THEN
    CAST('create database' AS pg_catalog.text)
  ELSE
    CAST('' AS pg_catalog.text)
 END role_attributes
FROM pg_catalog.pg_user
ORDER BY role_name desc;

CREATE USER postgres WITH PASSWORD 'supersecret';

CREATE USER root;
ALTER USER root WITH SUPERUSER;

ALTER USER myrole WITH OPTION1 OPTION2 OPTION3;
These options range from CREATEDB, CREATEROLE, CREATEUSER

ALTER USER librarian WITH NOSUPERUSER;

CREATE TABLE films (
    code        char(5) CONSTRAINT firstkey PRIMARY KEY,
    title       varchar(40) NOT NULL,
    did         integer NOT NULL,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute
);

\dt

\d tablename

show config_file;
SELECT setting FROM pg_settings WHERE name = 'data_directory';
show log_destination;

pg_dump -U dbuser dbname > dumpfile

pg_restore --verbose --clean --no-acl --no-owner -h localhost -U postgres -d dbname ./dumpfile

psql -h localhost -U postgres
psql (14.5)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=#

cd /var/lib/postgresql/data
openssl req -nodes -new -x509 -keyout server.key -out server.crt -subj "/C=US/L=NYC/O=Percona/CN=postgres"
chmod 400 server.{crt,key}
chown postgres:postgres server.{crt,key}
ls -la server.{crt,key}

alter system set ssl=on;

select pg_reload_conf();

openssl s_client -connect postgres:5432 -starttls postgres

table pg_hba_file_rules;

vi /var/lib/postgresql/data/pg_hba.conf

Shell
# TYPE   DATABASE       USER    ADDRESS            METHOD
local    all            all                        peer
host     all            all     127.0.0.1/32       scram-sha-256
host     all            all     ::1/128            scram-sha-256
hostssl  all            all     0.0.0.0/0          md5
hostssl  replication    all     10.124.33.113/24   md5

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

hostssl all all all md5

select pg_reload_conf();

table pg_hba_file_rules ;

psql "host=postgres user=keycloak sslmode=disable"

psql "host=postgres user=keycloak sslmode=require"

openssl s_client -connect postgres:5432 -starttls postgres

\conninfo

select pg_ssl.pid, pg_ssl.ssl, pg_ssl.version,
           pg_sa.backend_type, pg_sa.usename, pg_sa.client_addr
           from pg_stat_ssl pg_ssl
           join pg_stat_activity pg_sa
             on pg_ssl.pid = pg_sa.pid;