PostgreSQL cheat sheet

postgresql_cheat_sheet.md

Create role, DB and connect to DB

Switch to the posthres Linux account

~$ sudo -i -u postgres

Create role db_name

~$ createuser -U username -W --interactive

Enter db_name in the name of a new role

Create DB db_name

~$ createdb <db_name>

Connect to db_name with the role db_name

~$ psql -U <db_name>

Dump table1 table 2 from database databasename

~$ pg_dump -U username -W --data-only -t table1 -t table2 databasename > databasename_dump.psql

Load database databasename from dump file

~$ psql databasename -U username -W < databasename_dump.psql

See this post for the details

Change user

Password

ALTER USER davide WITH PASSWORD 'hu8jmn3';

Give another_user privileges on db_name

Connect

REVOKE CONNECT ON DATABASE <db_name> FROM PUBLIC;
GRANT CONNECT ON DATABASE <db_name> TO <another_user>;

Read, perform operations on existing tables

\c db_name
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO <another_user>;

Read, perform operations on future tables

ALTER DEFAULT PRIVILEGES
IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO <another_user>;

Read existing sequences

GRANT SELECT, USAGE ON ALL SEQUENCES IN SCHEMA public TO <another_user>;

Read future sequences

ALTER DEFAULT PRIVILEGES
IN SCHEMA public
GRANT SELECT, USAGE ON SEQUENCES TO <another_user>;