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;