I’ve used mostly MySQL in the past, but a recent project called for working with PostgreSQL. The most immediate difference was that databases and schemas were semantically different. I took a liking to the DBMS – database management system, and plan to be using it in future projects. So, here I will notate some commands to quickly get up and running with PostgreSQL.

Connecting to the Server

Another difference MySQL users will encounter early on is how to access the DBMS. In MySQL, when installing and configuring the server, a password is setup for the root user, and that user then makes the initial connection to the DBMS and creates subsequent users. With PostgreSQL, initial configuration is done by the postgres user on the default postgres database.

First Connection to the DBMS

From the console, issue the following command to connect to PostgreSQL:

psql -R postgres -d postgres

Roles vs. Users

Analogous to MySQL users, PostgreSQL controls access via Roles. Notice the -R flag on the connection command, followed by postgres. Earlier, I called postgres a user, but in PostgreSQL terms this is a Role; hence the meaning of the -R flag. In case it needs to be said, the -d flag tells the psql command to connect to the given database.

Create a new Role

A Role is analogous to a user. Roles are used to control access to DBMS objects (databases, schemas, tables, etc.). To create a Role, when logged into the system issue the following command:

CREATE ROLE my_role WITH LOGIN ENCRYPTED PASSWORD 'password';

Databases and Schemas

In MySQL, tables are grouped into databases; so to create a new space for tables, a new database would need to be setup. In PostgreSQL, tables are grouped by schemas and schemas are the constituents of databases. This is an appealing organization because you can, for example, have a database for a company composed of multiple schemas for the different departments of the company, and each schema can contain tables relevant to the department.

Create a new Database

To create a database to hold schemas:

CREATE DATABASE my_db;

Switching to a Database

To connect to a database use the \c command.

\c my_db
my_db => 

Create a new Schema

CREATE SCHEMA my_schema;

Create Table in a Schema

CREATE TABLE my_schema.my_tbl (
    id serial,
   ... table definition ...
);

Connecting Apps to Databases

Once a role, a schema, and tables have been created, we’ll often want to connect an application to the database.

Grant a Role Connect Permissions

First, log into PostgreSQL.

psql -R my_role -d my_database

We’ll need to allow the role CONNECT privileges.

GRANT CONNECT ON DATABASE my_db TO my_role;

Grant Role Usage on Schema

To allow the role to access the schema:

GRANT USAGE ON SCHEMA my_schema TO my_role;

Grant Rights to Perform CRUD Operations

Finally, we’ll need to grant the role the access needed to do Create, Read, Update, and Delete operations.

GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA my_schema TO my_role;

Also, if your tables have sequences (analogous to auto increment integers in MySQL), you’ll also need to grant access to those sequences.

GRANT USAGE,SELECT ON ALL SEQUENCES IN SCHEMA my_schema TO my_role;