1
00:00:00,964 --> 00:00:02,705
- [Instructor] Hi and
welcome back to the course.

2
00:00:02,705 --> 00:00:05,342
In this video, we're going
to be linking our Jose user

3
00:00:05,342 --> 00:00:06,342
to Postgres.

4
00:00:08,127 --> 00:00:11,853
So what we're gonna do
is we are going to log in

5
00:00:11,853 --> 00:00:16,020
to our server as we know ssh
jose@ the server's IP address,

6
00:00:18,115 --> 00:00:19,461
and then we're gonna put in the password,

7
00:00:19,461 --> 00:00:21,592
and now we're logged in.

8
00:00:21,592 --> 00:00:24,450
The next thing we're
gonna do is we're going to

9
00:00:24,450 --> 00:00:26,200
become the root user.

10
00:00:30,475 --> 00:00:34,558
And then we're going to
become the postgres user.

11
00:00:37,087 --> 00:00:38,916
So now we are the postgres user,

12
00:00:38,916 --> 00:00:43,756
which has access to all
things PostgreSQL related.

13
00:00:43,756 --> 00:00:46,368
The next thing we're gonna
do is we're going to create

14
00:00:46,368 --> 00:00:47,868
a PostgreSQL user.

15
00:00:49,527 --> 00:00:53,443
Now, the PostgreSQL
user must have, for now,

16
00:00:53,443 --> 00:00:57,193
the same name as the
Unix user we've created.

17
00:01:00,284 --> 00:01:03,367
So we're gonna do createuser Jose -P.

18
00:01:05,665 --> 00:01:08,620
Notice that Jose is the
same name as the Unix user,

19
00:01:08,620 --> 00:01:13,107
but this Jose here is
gonna be a PostgreSQL user.

20
00:01:13,107 --> 00:01:15,848
So it's a user in the database, or rather,

21
00:01:15,848 --> 00:01:19,456
in the server, that is going
to have its own database,

22
00:01:19,456 --> 00:01:21,740
and so on, and it has to have

23
00:01:21,740 --> 00:01:24,328
the same name as the Unix user.

24
00:01:24,328 --> 00:01:28,411
Dash P allows us to set
a password for this user.

25
00:01:30,318 --> 00:01:32,845
So we've created the
user and now we're gonna

26
00:01:32,845 --> 00:01:34,845
put in a password twice,

27
00:01:36,625 --> 00:01:39,625
and now we've created the
user that's been created.

28
00:01:39,625 --> 00:01:41,276
Okay?

29
00:01:41,276 --> 00:01:43,551
So, now what we can do
as well is make sure

30
00:01:43,551 --> 00:01:47,672
that there is a database
created for that user.

31
00:01:47,672 --> 00:01:50,040
So we do that with creatdb
and then we give it

32
00:01:50,040 --> 00:01:51,944
the database name.

33
00:01:51,944 --> 00:01:54,612
Notice, remember, that a PostgreSQL server

34
00:01:54,612 --> 00:01:56,536
can have many databases.

35
00:01:56,536 --> 00:01:59,084
Right now it has one called postgres,

36
00:01:59,084 --> 00:02:02,478
and now we're gonna create
another one called Jose,

37
00:02:02,478 --> 00:02:03,311
and that's it.

38
00:02:03,311 --> 00:02:04,144
Done.

39
00:02:05,197 --> 00:02:07,460
Now, we're gonna stop
becoming the postgres user

40
00:02:07,460 --> 00:02:08,827
and go back to being Jose,

41
00:02:08,827 --> 00:02:11,574
so we have to press exit,
and that goes back to root,

42
00:02:11,574 --> 00:02:14,586
and then exit again and
that goes back to Jose.

43
00:02:14,586 --> 00:02:18,116
And now we can type psql
and that will connect

44
00:02:18,116 --> 00:02:20,652
to the Jose database.

45
00:02:20,652 --> 00:02:23,692
Remember, the user, by default,

46
00:02:23,692 --> 00:02:26,694
connects to the same
database as the user's name.

47
00:02:26,694 --> 00:02:30,140
That's why we've created
this database there.

48
00:02:30,140 --> 00:02:33,114
And there we are, and
now we can press conninfo

49
00:02:33,114 --> 00:02:35,052
so you can see that I'm right.

50
00:02:35,052 --> 00:02:39,219
We are connected to the
database Jose as the user Jose.

51
00:02:40,715 --> 00:02:42,211
Okay?

52
00:02:42,211 --> 00:02:46,378
And now we can press \q to
leave the postgres terminal.

53
00:02:51,776 --> 00:02:52,621
Okay?

54
00:02:52,621 --> 00:02:54,553
At any point, if you want
to delete a database,

55
00:02:54,553 --> 00:02:58,161
you can do dropdb, and that
will delete a database,

56
00:02:58,161 --> 00:03:01,798
but you must be the
postgres user to do that.

57
00:03:01,798 --> 00:03:02,631
Okay?

58
00:03:03,775 --> 00:03:06,550
Now, by default, well, as you can see,

59
00:03:06,550 --> 00:03:09,163
when we connected to
PostgreSQL we didn't have to

60
00:03:09,163 --> 00:03:11,813
put in a password because, by default,

61
00:03:11,813 --> 00:03:16,613
PostgreSQL realises that
there is a user called Jose

62
00:03:16,613 --> 00:03:21,160
in the database and that has
access to the Jose database.

63
00:03:21,160 --> 00:03:23,011
So there's a postgres user called Jose,

64
00:03:23,011 --> 00:03:26,154
and that has access to to Jose database,

65
00:03:26,154 --> 00:03:30,397
and because they are called
the same as the Unix user,

66
00:03:30,397 --> 00:03:33,771
PostgreSQL just accepts that that is okay.

67
00:03:33,771 --> 00:03:35,426
This is not very safe.

68
00:03:35,426 --> 00:03:37,240
We've put in a password for a reason.

69
00:03:37,240 --> 00:03:39,808
We should be using that password.

70
00:03:39,808 --> 00:03:44,264
So let's make sure that PostgreSQL
asks us for our password.

71
00:03:44,264 --> 00:03:47,706
What we're gonna do is
we're gonna say sudo

72
00:03:47,706 --> 00:03:49,530
and now the command that we're gonna run

73
00:03:49,530 --> 00:03:51,949
is going to run as the root user.

74
00:03:51,949 --> 00:03:56,116
We're gonna say vi
/etc/postgresql/9.5/main/pg_hba.conf.

75
00:04:03,586 --> 00:04:04,658
Okay?

76
00:04:04,658 --> 00:04:08,841
So that's sudo, we're gonna
run this command here as root

77
00:04:08,841 --> 00:04:13,008
vi /etc/postgresql/9.5/main/pg_hba.conf.

78
00:04:15,437 --> 00:04:19,604
This is PostgreSQL's user login
security configuration file.

79
00:04:21,578 --> 00:04:23,734
So we are now here, and we're gonna

80
00:04:23,734 --> 00:04:25,511
scroll down to the bottom,

81
00:04:25,511 --> 00:04:28,810
and we're gonna quickly
explain what these things mean

82
00:04:28,810 --> 00:04:30,810
down here on the bottom.

83
00:04:34,038 --> 00:04:34,871
Okay?

84
00:04:35,977 --> 00:04:39,727
So, we have four lines
that are not comments.

85
00:04:40,763 --> 00:04:43,930
The first one local all postgres peer,

86
00:04:45,255 --> 00:04:48,090
and then local all all peer,

87
00:04:48,090 --> 00:04:52,257
and finally we've host
all all 127.0.0.1/32 md5,

88
00:04:53,771 --> 00:04:56,771
host all all ::1/128 md5.

89
00:05:00,780 --> 00:05:01,613
Okay?

90
00:05:01,613 --> 00:05:04,113
So what the first one means is

91
00:05:05,134 --> 00:05:09,301
if we connect from within
our server to any database

92
00:05:12,039 --> 00:05:14,089
as the postgres user,

93
00:05:14,089 --> 00:05:16,460
so if we are logged in
as the postgres user,

94
00:05:16,460 --> 00:05:19,657
the peer means we will
have access to everything

95
00:05:19,657 --> 00:05:21,409
without questions asked.

96
00:05:21,409 --> 00:05:22,242
Okay?

97
00:05:23,400 --> 00:05:26,233
So if we are the postgres user,

98
00:05:26,233 --> 00:05:30,400
we can connect to any database
without questions asked.

99
00:05:32,710 --> 00:05:36,877
The next one, if we are
locally, and we are any user,

100
00:05:39,477 --> 00:05:43,145
asking for any database,
we're going to have access.

101
00:05:43,145 --> 00:05:45,582
So essentially this is very unsafe.

102
00:05:45,582 --> 00:05:48,518
No matter who we are, we will
have access to everything

103
00:05:48,518 --> 00:05:52,685
just because the request is
coming from the local machine.

104
00:05:54,474 --> 00:05:55,779
Now, this may be a bit confusing,

105
00:05:55,779 --> 00:05:56,907
but let me go into the next one

106
00:05:56,907 --> 00:06:00,159
so you can understand the
difference between these two.

107
00:06:00,159 --> 00:06:04,963
For this one, what this means
is that when we are connecting

108
00:06:04,963 --> 00:06:09,601
from a different machine
or through the internet,

109
00:06:09,601 --> 00:06:11,434
or rather, through IP,

110
00:06:12,690 --> 00:06:16,857
so if somebody requests to
join or connect to the database

111
00:06:18,205 --> 00:06:21,705
and that request is coming from 127.0.0.1,

112
00:06:24,233 --> 00:06:25,701
no matter who they are

113
00:06:25,701 --> 00:06:27,829
and what database they're connecting to,

114
00:06:27,829 --> 00:06:30,113
we're going to use the md5 method.

115
00:06:30,113 --> 00:06:33,819
Md5 is essentially asking for a password.

116
00:06:33,819 --> 00:06:38,577
Now, you know from the previous
sections, that 127.0.0.1

117
00:06:38,577 --> 00:06:40,077
is the local host.

118
00:06:40,919 --> 00:06:41,752
So you may wonder,

119
00:06:41,752 --> 00:06:44,118
well, what's the difference
between local and host

120
00:06:44,118 --> 00:06:45,368
with 127.0.0.1.

121
00:06:47,406 --> 00:06:50,739
Well, when we connect via IP, via a URL,

122
00:06:53,897 --> 00:06:55,778
in this case it means that the request

123
00:06:55,778 --> 00:06:57,964
is coming from local,
but it could be coming

124
00:06:57,964 --> 00:06:59,970
from a different server or another server

125
00:06:59,970 --> 00:07:01,487
could be doing the request.

126
00:07:01,487 --> 00:07:05,654
There's no rule for that in
here, but it could happen.

127
00:07:07,491 --> 00:07:10,594
So the host just means
that the request is coming,

128
00:07:10,594 --> 00:07:14,024
the request to login is
coming from the internet

129
00:07:14,024 --> 00:07:16,524
as opposed to from the server.

130
00:07:17,521 --> 00:07:20,604
So local means the request
is coming through the server,

131
00:07:20,604 --> 00:07:23,042
and host means that the request is coming

132
00:07:23,042 --> 00:07:25,108
over the internet.

133
00:07:25,108 --> 00:07:26,857
In this case, the internet connection

134
00:07:26,857 --> 00:07:28,838
is also coming from the local server,

135
00:07:28,838 --> 00:07:30,422
but it could be coming from elsewhere

136
00:07:30,422 --> 00:07:32,172
if we set it to that.

137
00:07:33,091 --> 00:07:36,533
So what we're gonna do to
stop confusing you any longer,

138
00:07:36,533 --> 00:07:38,960
sorry, and this one down
here is exactly the same,

139
00:07:38,960 --> 00:07:42,543
but instead of using
IPB4, it's using IPV6.

140
00:07:43,742 --> 00:07:46,423
What we're gonna do is,
for local connections,

141
00:07:46,423 --> 00:07:49,252
we are going to make sure
to ask for a password.

142
00:07:49,252 --> 00:07:50,837
So what we have to do is go over

143
00:07:50,837 --> 00:07:54,670
to this line here and
make sure to remove peer

144
00:07:55,527 --> 00:07:57,277
and change it to md5.

145
00:08:00,142 --> 00:08:00,975
Okay?

146
00:08:00,975 --> 00:08:03,851
So we're gonna press the I
key to go into insert mode,

147
00:08:03,851 --> 00:08:07,574
and delete peer, and change it to md5.

148
00:08:07,574 --> 00:08:08,873
Then we're gonna press escape,

149
00:08:08,873 --> 00:08:12,032
and then :wq to write and quit,

150
00:08:12,032 --> 00:08:14,498
and that's that saved there.

151
00:08:14,498 --> 00:08:16,079
Okay?

152
00:08:16,079 --> 00:08:20,020
Now we've secured the
PostgreSQL installation

153
00:08:20,020 --> 00:08:24,022
and we have make sured
that whenever we log in,

154
00:08:24,022 --> 00:08:27,036
we have to put in a password.

155
00:08:27,036 --> 00:08:31,283
This is very important because
SQL can really not work

156
00:08:31,283 --> 00:08:34,601
unless we do this, and so, while it's okay

157
00:08:34,601 --> 00:08:38,768
to use the peer-type when we
are using PostgreSQL directly,

158
00:08:40,690 --> 00:08:42,876
when we want to use it through SQLAlchemy

159
00:08:42,876 --> 00:08:45,724
we must change it to
md5 or else SQLAlchemy

160
00:08:45,724 --> 00:08:47,117
will not like it.

161
00:08:47,117 --> 00:08:49,988
The reason why it doesn't like
it is because it's not secure

162
00:08:49,988 --> 00:08:51,816
and so the fact that it
forces you to do that

163
00:08:51,816 --> 00:08:55,291
increases security in your server.

164
00:08:55,291 --> 00:08:58,033
With that said, now that
we've changed that to md5,

165
00:08:58,033 --> 00:08:59,563
we are ready to continue

166
00:08:59,563 --> 00:09:02,875
and get the rest of the
application deployed here.

167
00:09:02,875 --> 00:09:06,023
So, without further ado, I'll
see you in the next video.