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:

Role which can connect - Called Users

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

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

Create database with owner

Now we can create a database using this user:

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:

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: