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.

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 :)

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

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 :)

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
---------------------------+----------------
 newsitoweb                | 18 GB
 cruscotti                 | 9826 MB
 vis                       | 8508 MB
 ania_ltc_db               | 3231 MB
 sca                       | 358 MB
 gdpr                      | 245 MB
 portalestatisticonew      | 210 MB
 isec                      | 161 MB
 infobila                  | 156 MB
 ania_circolari_db3        | 109 MB
 ania_sportelloauto_db     | 52 MB
 ania_circolari_db         | 39 MB
 ania_circolari_db2        | 39 MB
 ania_circolari_stag_db    | 32 MB
 dirdir                    | 16 MB
 ania_periti_db            | 13 MB
 ania_documentaledirettori | 11 MB
 ania_gestione_attivita_db | 9081 kB
 prenotazioni              | 7625 kB
 autorizzazioni            | 7401 kB
 consumatori_auto          | 7393 kB
 schedulatore              | 7209 kB
 attivitasi                | 6473 kB
 postgres                  | 6321 kB
 template0                 | 6081 kB
 ania_comu_personale       | 5425 kB
 ania_app                  | 5297 kB
 ania_comu_dirigenti       | 5297 kB
 sicweb                    | 5209 kB
 Questionario              | 5065 kB
 template1                 | 5033 kB
(31 rows)