CREATE USER <user> WITH PASSWORD '<password>' SUPERUSER;
\du
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
admin | Superuser, Create role, Create DB | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
psql -h localhost -U admin
psql (13.20)
Type "help" for help.
admin=#
CREATE ROLE readaccess;
GRANT CONNECT ON DATABASE <database> TO readaccess;
GRANT USAGE ON SCHEMA public TO readaccess;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readaccess;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readaccess;
-- 1. Grant usage on the schema
GRANT USAGE ON SCHEMA public TO readaccess;
-- 2. Grant SELECT on all existing tables in schema public
DO
$$
DECLARE t RECORD;
BEGIN
FOR t IN
SELECT tablename FROM pg_tables WHERE schemaname = 'public'
LOOP
EXECUTE format('GRANT SELECT ON TABLE public.%I TO readaccess;', t.tablename);
END LOOP;
END;
$$;
-- 3. Grant SELECT on all existing sequences in schema public
DO
$$
DECLARE s RECORD;
BEGIN
FOR s IN
SELECT sequence_name FROM information_schema.sequences WHERE sequence_schema = 'public'
LOOP
EXECUTE format('GRANT SELECT ON SEQUENCE public.%I TO readaccess;', s.sequence_name);
END LOOP;
END;
$$;
-- 4. Ensure default privileges for future tables in schema public
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readaccess;
-- 5. Ensure default privileges for future sequences in schema public
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO readaccess;
CREATE USER <app_user> WITH ENCRYPTED PASSWORD '<app_password>';
GRANT readaccess TO <app_user>;
CREATE USER <app_user> WITH ENCRYPTED PASSWORD '<app_password>';
CREATE DATABASE <app_db> OWNER <app_user>;
GRANT ALL PRIVILEGES ON DATABASE <app_db> TO <app_user>;
\l
\c <satabase>
(switch to database in question first)
\dn
pg_dump -h localhost -U admin -d <app_db> -f /path/to/file/<app_db>.sql
psql -h localhost -U admin
drop database <app_db>;
CREATE DATABASE <app_db> OWNER <app_user>;
\q
psql -h localhost -U admin -d <app_db> < /path/to/file/<app_db>.sql
psql -h localhost -U admin
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = '<database>' AND leader_pid IS NULL;
psql -h localhost -U admin
drop database <database>
TRUNCATE TABLE table_name;
CREATE TABLESPACE <table space> OWNER <owner> LOCATION '</path/to/directory>'
CREATE DATABASE <database> WITH OWNER = <owner> ENCODING = 'UTF8' TABLESPACE = <table space>;
use this to create a combined user / password hash for instance to use in Kubernetes secrets
SELECT 'md5' || md5('password' || 'username');
login as superuser or admin user with full access
SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;