Table of Contents

Overview

As any other good database server, PostgreSQL is consisted by many databases. Access to these databases is controlled and usually one user (role) doesn't have access to a database if it isn't granted such. A database has also a owner, so called Database owner which can be given during creation.

Create user

In PostgreSQL, there are 2 kind of roles:

  1. Role which can connect - Called Users
  2. Roles which cannot connect - Called Groups

Thus it is easy to grant one role to other role and so on. From Oracle point of view (which has strictly defined roles and user) this seems nuts :) 

psql>CREATE ROLE integration_pariplay with password 'somethingSomethingDarkSide' INHERIT LOGIN;
CREATE
psql>commit;

This command will create a role, which can login, thus User :)

Create database with owner

Now we can create a database using this user: 

psql>create database integration_pariplay owner=integration_pariplay;
CREATE
psql>commit;

This will create us a database with name: integration_pariplay with owner, the role(User) from above

Grant privileges

Just because a user is owner of a database, this doesn't mean the user can select tables on that database, from all we know, this user might not even be able to connect to the database it owner: (It should be specified in pg_hba.conf) file.

Thus in order to grant access to it, simply execute the following from within the database: 

psql>grant select on all tables in schema public to integration_pariplay;
GRANT
psql>commit;

P.S. Do not forget to update the pg_hba.conf and reload the profile so the user(ROLE) can connect to it :)

Get the size of all databases

Easy way to get the size of databases is to execute the following query: 

postgres=# 
SELECT database_name, pg_size_pretty(size) 
from 
      (SELECT pg_database.datname as "database_name", pg_database_size(pg_database.datname) AS size 
      FROM pg_database ORDER by size DESC) as ordered;
      
      
       database_name       | pg_size_pretty
---------------------------+----------------
 DB1                       | 18 GB
 DB2                       | 9826 MB
 DB3                       | 6969 MB
(3 rows)