PostgreSQL

December 7, 2019

Jé pa l'temps #5 - Postgresql

Reset database without delete it

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;
commit;

PSQL command

Show users : \du
Show databases : \list

Load / Dump database

# Load SQL
psql postgres://user:password@localhost:port/database --file myDumpFile.sql
# Dump Database
pg_dump postgres://user:password@localhost:port/database --file myDumpFile.sql

Generate fake data

INSERT INTO myTable (name)
SELECT 'name #' || x.id
  FROM generate_series(1,100) AS x(id);

How can load a dump to the database if port is not exposed ?

cat your_dump.sql | docker exec \
  -i {docker-postgres-container} psql -U {user} -d {database_name}

Right management

REVOKE CONNECT ON DATABASE miniflux FROM funkwhale;
REVOKE ALL PRIVILEGES ON DATABASE miniflux FROM funkwhale;
REVOKE ALL ON DATABASE miniflux FROM PUBLIC; -- By default public schema are accessible by others users, but not the data

/!\ It appears to be normal that we can not hide database existence from others users, but we can prevent them from connecting.

jplt database