PostgreSQL Module #59

Question 1

What type of database is PostgreSQL?

Answer 1

It is a relational database. Think TABLES :-)

Question 2

What command needs to be entered on the command i line in terminal to get access to the database?

Answer 2

psql postgres (which gives access to the postgres db created by default)

Question 3

How are new databases added to Postgre ?

Answer 3

By calling the CREATE DATABASE test; command. Don’t forget the semicolon as it is necessary.

Question 4

What’s the syntax to create a new table in our DB?

Answer 4

CREATE TABLE users {
id SERIAL PRIMARY KEY,
email VARCHAR(255) NOT NULL
);

Question 5

How can you connect to a specific database

Answer 5

By typing

\c test or the name of the db

Question 6

What’s the syntax for listing your databases (and templates) in PostgreSQL?

Answer 6

\list or \l

Question 7

How are templates and databases delineated in PostgreSQL?

Answer 7

Databases take the name you assign and templates are called templates which are numbers.

template1
template2 etc

Question 8

What are templates in PostgreSQL? How are they created?

Answer 8

Templates are predefined databases. You can use them to pre-populate new databases and you use the syntax to generate them:

CREATE DATABASE databasename Template template0

Question 9

How can we view a more detailed overview of our databases showing the disc sizes for each?

Answer 9

\list+ or +

Question 10

How are new users created in PostgreSQL?

Answer 10

There are no users, just roles.

Question 11

How are roles created in PostgreSQL?

Answer 11

On MacOS, you’re automatically assigned a role when you run psql postgres.

Question 12

How can you view roles?

Answer 12

By typing: \du

Question 13

How would you create a new role?

Answer 13

Use this syntax:

CREATE ROLE ; a better example is
CREATE ROLE testing;

But this example creates a user without login rights.

Question 14

How are Roles with Login capabilities created?

Answer 14

CREATE ROLE rolename WITH LOGIN;

Question 15

How can users login under their ROLE?

Answer 15

Use this syntax:

psql postgres -U testing rolename

Question 16

From terminal how can you quickly tell the difference between a superuser and a normal role?

Answer 16

The prompt changes from =# to =>

Question 17

How can we password protect a role?

Answer 17

CREATE ROLE WITH LOGIN PASSWORD ‘’;

Question 18

How are roles altered?

Answer 18

ALTER ROLE WITH LOGIN;

Question 19

What are the other built-in role attributes?

Answer 19

LOGIN / NOLOGIN: allow (or not) to login to Postgres

SUPERUSER / NOSUPERUSER: allow (or not) superuser permissions. A database superuser will bypass other permission checks, except for LOGIN (it must be granted separately).

CREATEDB / NOCREATEDB: allow (or not) the ability to create new databases

CREATEROLE / NOCREATEROLE: allow (or not) the ability to create new roles

CREATEUSER / NOCREATEUSER: allow (or not) the ability to create new users

INHERIT / NOINHERIT: allow (or not) the ability to make the privileges inheritable

REPLICATION / NOREPLICATION: grant (or not) replication permissions (an advanced topic we’ll not cover)

Question 20

What’s an alternate way to create a user / role with a password. TIP: This method is less steps than the others?

Answer 20

CREATE USER rolename PASSWORD ‘password’;

Question 21

Just like there really aren’t users in PostgreSQL there aren’t groups either. How are group roles created?

Answer 21

CREATE ROLE groupname;

Question 22

Once the group role is created access to that group needs to be granted, how is that done?

Answer 22

Use this syntax:
GRANT TO

For example, we can create a flavio user role, a “employee” group role, and assign the user to the group role:

CREATE USER flavio PASSWORD ‘superSecret123$’;
CREATE ROLE employee;
GRANT employee TO flavio;

Question 23

How are group roles removed?

Answer 23

REVOKE FROM
Example:

REVOKE employee FROM flavio;

Question 24

Can roles get group attributes once they’re added to the group?

Answer 24

No, by default, adding a role to a group role will not make the role inherit attributes (permissions) from the group role.

You need to create the group role with the INHERIT attribute.

Question 25

How can you create a role that inherits group attributes?

Answer 25

CREATE ROLE employee WITH CREATEDB INHERIT;

The next time you grant a role to a a role that’s in a group, it will INHERIT those group attributes.

CREATE ROLE flavio;
GRANT employee TO flavio;

Question 26

What is one of the easiest Cloud providers for postgreSQL?

Answer 26

Heroku. https://www.heroku.com/

Heroku for example provides a built-in managed PostgreSQL cloud database as a service, which might be very convenient instead of managing your own database server

Question 27

Extra Credit: what is the fully open source fork of MySQL from Oracle called?

Answer 27

MariaDB

Question 28

Define ACID?

Answer 28

Atomicity, Conssistency, Isolation, Durability