List OF DATABASES :
psql -l
\? Help
\d ~= DESC
 
\c <database> -- select database
 
\dt SHOW TABLES
\dT list datatypes
\df list functions
\di list indexes
\dv list views
 
 
  \da [PATTERN]  list aggregate functions
  \db [PATTERN]  list tablespaces (ADD "+" FOR more detail)
  \dc [PATTERN]  list conversions
  \dC            list casts
  \dd [PATTERN]  SHOW comment FOR object
  \dD [PATTERN]  list domains
  \df [PATTERN]  list functions (ADD "+" FOR more detail)
  \dg [PATTERN]  list groups
  \dn [PATTERN]  list schemas (ADD "+" FOR more detail)
  \do [NAME]     list operators
  \dl            list LARGE objects, same AS \lo_list
  \dp [PATTERN]  list TABLE, VIEW, AND SEQUENCE access privileges
  \dT [PATTERN]  list DATA types (ADD "+" FOR more detail)
  \du [PATTERN]  list users
  \l             list ALL DATABASES (ADD "+" FOR more detail)
  \z [PATTERN]   list TABLE, VIEW, AND SEQUENCE access privileges (same AS \dp)

CREATE ROLE k2patel LOGIN PASSWORD 'test' VALID UNTIL 'infinity';

CREATE TABLESPACE k2patel OWNER k2patel LOCATION '/var/lib/pgsql/k2patel';

CREATE DATABASE k2patel WITH OWNER = k2patel ENCODING = 'UTF8' TABLESPACE = k2patel LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' CONNECTION LIMIT = -1;

SELECT * FROM pg_stat_activity;

ALTER USER postgres WITH ENCRYPTED PASSWORD 'password';

This only allow user to access database but can not write to it.

CREATE ROLE carol;
ALTER ROLE carol WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN ENCRYPTED PASSWORD 'password';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO carol;

SELECT inet_server_addr();

Following is the available procedures / variable.

Name Return Type Description current_catalog name name of current database (called “catalog” in the SQL standard) current_database() name name of current database current_schema[()] name name of current schema current_schemas(boolean) name[] names of schemas in search path, optionally including implicit schemas current_user name user name of current execution context current_query() text text of the currently executing query, as submitted by the client (might contain more than one statement) pg_backend_pid() int Process ID of the server process attached to the current session pg_listening_channels() setof text channel names that the session is currently listening on inet_client_addr() inet address of the remote connection inet_client_port() int port of the remote connection inet_server_addr() inet address of the local connection inet_server_port() int port of the local connection pg_my_temp_schema() oid OID of session's temporary schema, or 0 if none pg_is_other_temp_schema(oid) boolean is schema another session's temporary schema? pg_postmaster_start_time() timestamp with time zone server start time pg_conf_load_time() timestamp with time zone configuration load time session_user name session user name user name equivalent to current_user version() text PostgreSQL version information