PostgreSQL Module #59 Flashcards

1
Q

What type of database is PostgreSQL?

A

It is a relational database. Think TABLES :-)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

How are new databases added to Postgre ?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

How can you connect to a specific database

A

By typing

\c test or the name of the db

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

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

A

\list or \l

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

How are templates and databases delineated in PostgreSQL?

A

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

template1
template2 etc

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What are templates in PostgreSQL? How are they created?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

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

A

\list+ or +

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

How are new users created in PostgreSQL?

A

There are no users, just roles.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

How are roles created in PostgreSQL?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

How can you view roles?

A

By typing: \du

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

How would you create a new role?

A

Use this syntax:

CREATE ROLE ; a better example is
CREATE ROLE testing;

But this example creates a user without login rights.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

How are Roles with Login capabilities created?

A

CREATE ROLE rolename WITH LOGIN;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

How can users login under their ROLE?

A

Use this syntax:

psql postgres -U testing rolename

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

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

A

The prompt changes from =# to =>

17
Q

How can we password protect a role?

A

CREATE ROLE WITH LOGIN PASSWORD ‘’;

18
Q

How are roles altered?

A

ALTER ROLE WITH LOGIN;

19
Q

What are the other built-in role attributes?

A

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)

20
Q

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

A

CREATE USER rolename PASSWORD ‘password’;

21
Q

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

A

CREATE ROLE groupname;

22
Q

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

A

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;

23
Q

How are group roles removed?

A

REVOKE FROM
Example:

REVOKE employee FROM flavio;

24
Q

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

A

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.

25
Q

How can you create a role that inherits group attributes?

A

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;

26
Q

What is one of the easiest Cloud providers for postgreSQL?

A

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

27
Q

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

A

MariaDB

28
Q

Define ACID?

A

Atomicity, Conssistency, Isolation, Durability