psql cheat sheet

psql.sql

--search and replace vars
--Database=some_db
--User=some_user

-- show roles, and db level permissions
\du

--show permissions on table
\z *.*

--list of schemas
SELECT schema_name FROM information_schema.schemata;

--list schema objects
\dt *.*  --tables
\ds *.*  --sequences
\dv *.*  --views

--remove global permisions
REVOKE ALL ON DATABASE some_db FROM public;

-- allow connection, temp tables, and schema creation in db
GRANT CONNECT,TEMP,CREATE ON DATABASE some_db TO some_user;

-- allow access to schema
GRANT USAGE ON SCHEMA public TO some_user;

--give all access to schema objects
GRANT ALL ON ALL TABLES IN SCHEMA public TO some_user;

--all enumerated table permissions on all tables
GRANT SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER,EXECUTE ON ALL TABLES IN SCHEMA public TO some_user;
--nothing fancy
GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA public TO some_user;

-- generate all the statement to reassign ownership of objects, capture output for use with bash/psql
SELECT 'ALTER TABLE '|| schemaname || '.' || tablename ||' OWNER TO some_user;' FROM pg_tables WHERE NOT schemaname IN ('pg_catalog', 'information_schema') ORDER BY schemaname, tablename;

SELECT 'ALTER SEQUENCE '|| sequence_schema || '.' || sequence_name ||' OWNER TO some_user;' FROM information_schema.sequences WHERE NOT sequence_schema IN ('pg_catalog', 'information_schema') ORDER BY sequence_schema, sequence_name;

SELECT 'ALTER VIEW '|| table_schema || '.' || table_name ||' OWNER TO some_user;' FROM information_schema.views WHERE NOT table_schema IN ('pg_catalog', 'information_schema') ORDER BY table_schema, table_name;