This article mainly focuses on the Ownership/Permissions of database objects. I had to collect these details for migrating a standalone PostgreSQL database to Amazon RDS(Relational Database Service) PostgreSQL and I will be updating this article as long as I find something which belongs here.
All these commands are supposed to be executed in the psql
command line. Adding “+” sign to most of the command gives you more information of those objects.
How to get help in psql command line:
\?
How to list the databases:
\l or \l+
How to connect to a different database with in the shell:
\c <database-name> or \connect <database-name>
How to list all the schemas in a database:
\dn or \dn+
How to list all tables in a schema:
\dt <schema-name>.* or \dt+ <schema-name>.*
How to list all tables in a database:
\dt *.* or \dt+ *.*
How to list all views in a schema:
\dv or \dv+
How to list all views in a database:
\dv *.* or \dv+ *.*
How to know the permissions on tables and views:
\dp+ <schema-name>.*;
How to list all the functions:
\df or \df+
How to list all the sequences in all schemas:
\ds+ *.*;
How to create a role in postgres:
CREATE ROLE bob WITH PASSWORD 'bobpassword';
How to create a user in posrgres:
CREATE USER alice WITH PASSWORD 'alicepassword';
Note: The keywords ROLE and USER are synonyms in PostgreSQL, however the difference between them is, when you use USER, the role will be created with LOGIN privileges.
How to create a group in postgres:
CREATE GROUP developers;
How to create a schema in a database:
CREATE SCHEMA minions AUTHORIZATION gru;
How to grant SELECT privileges on all tables to a group:
Note: Before you grant SELECT privileges to a role/group you need to give USAGE privileges to that role on the schemas those tables are in.GRANT USAGE ON SCHEMA application TO developers; GRANT SELECT ON ALL TABLES IN SCHEMA application TO developers;
How to add roles to a group:
GRANT developers TO bob,alice;
How to grant ALL permissions to a role on tables in a schema:
GRANT ALL ON SCHEMA application TO developers; GRANT ALL ON ALL TABLES IN SCHEMA application TO app_user;
How to know the current database:
SELECT current_database();
How to know the current user:
SELECT current_user;
How to list all the functions in a schema with arguments it take:
SELECT proname || '(' || pg_catalog.pg_get_function_identity_arguments(pr.oid) || ')', n.nspname FROM pg_proc pr join pg_namespace n ON pr.pronamespace = n.oid WHERE n.nspname IN ('<schema1>','<schema2>');
How to change the ownership on a function:
ALTER FUNCTION <schema-name, if-needed>.<function name with arguments, from the output of above query> OWNER TO 'new-owner';
How to grant EXECUTE permission on a function:
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA application TO app_user;
How to list all the functions in a schema along with the permissions on them:
SELECT oid::regprocedure, proacl FROM pg_proc WHERE proacl IS NOT NULL;
How to GRANT ALL permissions to sequences:
GRANT ALL ON ALL SEQUENCES IN SCHEMA appplication to admin;
How to change the search path for user:
ALTER ROLE app_user SET search_path TO application;
How to show the default permissions on schema:
SELECT nspname, defaclobjtype, defaclacl FROM pg_default_acl a JOIN pg_namespace b ON a.defaclnamespace=b.oid;
How to set default permissions on a schema(on Tables):
ALTER DEFAULT PRIVILEGES IN SCHEMA application GRANT SELECT ON TABLES TO developers;
How to revoke default permissions on a schema(on Tables):
ALTER DEFAULT PRIVILEGES IN SCHEMA application REVOKE ALL ON TABLES FROM developers;
How to get the start time of the database from PSQL:
SELECT pg_postmaster_start_time();
How to get the uptime, execute the query below:
SELECT now() - pg_postmaster_start_time();
How to get long running queries:
SELECT blockeda.pid AS blocked_pid, blockinga.pid AS blocking_pid, blockinga.query as blocking_query, blockeda.query as blocked_query FROM pg_catalog.pg_locks blockedl JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid JOIN pg_catalog.pg_locks blockingl ON(blockingl.transactionid=blockedl.transactionid AND blockedl.pid != blockingl.pid) JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid WHERE NOT blockedl.granted;