Postgres Hacks

create-user.md

su - postgres psql

Create Role

CREATE ROLE Read_Only_User WITH LOGIN PASSWORD 'Test1234' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity';

Add permissions

GRANT CONNECT ON DATABASE YourDatabaseName TO Read_Only_User; GRANT USAGE ON SCHEMA public TO Read_Only_User; GRANT SELECT ON ALL TABLES IN SCHEMA public TO Read_Only_User; GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO Read_Only_User;

install-pg11-ubuntu.md

Resource: https://tecadmin.net/install-postgresql-server-on-ubuntu/

1. Install

sudo apt-get install wget ca-certificates
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'
sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

2. Setup user

sudo -u postgres psql
postgres=# create database mydb;
postgres=# create user myuser with encrypted password 'mypass';
postgres=# grant all privileges on database mydb to myuser;

postgres-setup-plesk.md

Ref: https://support.plesk.com/hc/en-us/articles/115003321434-How-to-enable-remote-access-to-PostgreSQL-server-on-a-Plesk-server-

find / -name "postgresql.conf"

listen_addresses = '*'

host all all 0.0.0.0/0 md5

service postgresql restart