Useful documentation with examples:
Connect to the postgresql
psql -p 5432 -h localhost -U postgres -W
Database and Schema
Show all databases
\l
# Or
SELECT datname FROM pg_database;
Switch to another database
Similar to USE from mysql.
\c databasename
Create database
CREATE DATABASE test_db;
Drop/Delete database
DROP DATABASE IF EXISTS test_db;
A database may contain multiple schemas, which groups tables from a database. By default public schema is created for every database. And also you don't have to specify it in your queries, eg: public.staff is the same as staff. More examples and documentation can be checked here
User/Role
Show all users
\du
# With description
\du+
# Or sql
SELECT rolname FROM pg_roles;
Create a user
CREATE USER test_user WITH PASSWORD 'pass';
Grant user rights to connect to db
GRANT CONNECT ON DATABASE test_db TO test_user;
Try to connect afterwards:
psql -p 5432 -h localhost -U test_user -W -d test_db
Create superuser
CREATE ROLE test_user2 WITH LOGIN SUPERUSER CREATEDB PASSWORD 'pass';
# Connect afterwards
psql -p 5432 -h localhost -U test_user2 -W -d postgres
Alter user, add createdb rights
ALTER ROLE test_user2 WITH CREATEDB;
Grant all privileges on the database
GRANT ALL PRIVILEGES ON DATABASE test_db TO test_user2;
Common users
You ussually need the following users:
Reader:
CREATE USER reader_user WITH PASSWORD 'reader_user';
GRANT CONNECT ON DATABASE test_db TO reader_user;
\c databsename
ALTER DEFAULT PRIVILEGES
FOR USER reader_user
IN SCHEMA public
GRANT SELECT ON TABLES TO reader_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO reader_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reader_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO reader_user;
Writer:
CREATE USER reader_writer WITH PASSWORD 'reader_writer';
GRANT CONNECT ON DATABASE test_db TO reader_writer;
\c databsename
ALTER DEFAULT PRIVILEGES
FOR USER reader_writer
IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO reader_writer;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO reader_writer;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO reader_writer;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO reader_writer;
Admin
CREATE USER admin WITH PASSWORD 'admin';
GRANT CONNECT ON DATABASE test_db TO admin;
\c test_db
ALTER DEFAULT PRIVILEGES
FOR USER admin
IN SCHEMA public
GRANT ALL ON TABLES TO admin;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL ON SEQUENCES TO admin;
GRANT ALL ON ALL TABLES IN SCHEMA public TO admin;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO admin;
Tables
Create table
Documentation, official documentation
CREATE TABLE accounts (
user_id serial PRIMARY KEY,
username VARCHAR ( 50 ) UNIQUE NOT NULL,
password VARCHAR ( 50 ) NOT NULL,
email VARCHAR ( 255 ) UNIQUE NOT NULL,
created_on TIMESTAMP NOT NULL,
last_login TIMESTAMP
);
CREATE TABLE address (
id serial PRIMARY KEY,
name VARCHAR ( 50 ) NOT NULL
);
CREATE TABLE people (
id serial PRIMARY KEY,
name VARCHAR ( 50 ) NOT NULL
);
Describe table
\d accounts
Show tables
\dt+
Grant SELECT on table
First you need to connect with an user that has grant privileges and then switch to the database that contains that table:
\c test_db
GRANT SELECT ON accounts TO test_user;
Grant select on all tables even when new tables are added*
Documentation, official documentation
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO test_user;
See rights for specific table
\dp table
# Default priv
\ddp table