connectdb.sh

psql -p 5432 -h localhost -U postgres

psql-cheatsheet.md

// Remote Access PSQL

psql --host=host --port=5432 --username=username@servername --dbname=postgres

// create user

sudo -u postgres createuser <username>

// create database

sudo -u postgres createdb <dbname>

// grant privilleges

sudo -u postgres psql
psql=# alter user <username> with encrypted password '<password>';

// from psql

CREATE DATABASE yourdbname;
CREATE USER youruser WITH ENCRYPTED PASSWORD 'yourpass';
GRANT ALL PRIVILEGES ON DATABASE yourdbname TO youruser;

psql-console.sh

#check size db
SELECT pg_size_pretty( pg_database_size('ppda_datalake'));

#

read-access.sql

-- Create a group
CREATE ROLE readaccess;

-- Grant access to existing tables
GRANT USAGE ON SCHEMA public TO readaccess;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readaccess;

-- Grant access to future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readaccess;

-- Create a final user with password
CREATE USER tomek WITH PASSWORD 'secret';
GRANT readaccess TO tomek;

remote-access.sh

#postgresql.conf
listen_addresses = '*' 

#pg_hba.conf
host all all 0.0.0.0/0 md5