0:54 Introduction to Teleport Database Access

0:54 Hello, and welcome to the next workshop in the complete guide to Teleport course. Today, we are gonna be taking a look at a really cool feature of Teleport, which allows you to commoditize the access to your databases. Today, we will be using a Postgres through the Teleport system. We are going to be doing another database access workshop next week on MongoDB. So we'll be aiming to cover Postgres and Mongo in a week to show you this just amazing feature of Teleport. Database access is something that is notoriously tricky to do in a safe and secure way,

1:38 you know, through bastion boxes, the key management, even speaking and talking about passwords over the phone. We do some weird and wonderful things in the IT community when it comes to database access. From having our GUI clients have red colored backgrounds and themes to indicate that we're on production, and maybe we wanna think about our commands a little bit more. We have a GitHub repository for all of the workshops that we have as part of this course. So, let me get my screen shared. You will find it at Rawkode Academy, which is the organization on GitHub.

2:02 Github Repository

2:23 And then there is a repository called courses. We have our first low. Hey, Russell. Thank you for joining us. So you can find this database access here. I think this is part seven, to be honest, I've gotten a little bit confused about the order I planned on doing this in, just because of my father in law's funeral at the end of last year, I'm currently trying to sell my house and move house and of course, the festive and new year period. So my head is a bit scattered, but maybe seven, I may change out there.

3:01 Prerequisites

3:01 However, there are a few prerequisites that are required if you wish to follow along or do this in your own time, depending on if you're watching it right now and you can actually say hello or whether you're doing this in the future. You will require a running and working Teleport. I'm doing things a little bit different today. Normally, I would run my own Teleport. Typically, I spin it up on a cloud provider of choice, throw a DNS name on it, usually something. Rokode.live, and I use that. However, I decided today to try and make my life a little bit easier,

3:11 Setting up Postgres on a VM

3:37 and I'm using Teleport Cloud. So I get a nice DNS name of Rawkode.teleportsh and I get the latest and greatest version of Teleport. So let's see how this goes, right? Of course, when they change things on this, nothing ever goes wrong, right? So I don't need to worry about running and working Teleport. I've let Intelliport do that all for me. Now, it's not free. I'm on a fourteen day trial. I'll see how I like it. But so far, it seems pretty cool. We need a running and working Postgres. Okay. So let's do that together. I

4:17 have a virtual machine running on Civo cloud and I have a terminal. Add. Add a terminal, which is nicely timed out on me. Let's see if I can pop that back open. Rawkode. Well, I fixed this. We have a hello from Jeffrey. Hey, Jeffrey. How's it going? Thank you for joining me for this live workshop where only good, happy things happen and nothing bad ever happens. The IP address is wrong here. So my auto complete didn't close to the latest one. This one has now closed, but here I am. So this is just a Let me just pull this over here.

5:10 This is one of Siebel's large boxes, which is four CPUs and eight gig of RAM coming from a bare metal world. I'm not inclined to call that large, however. I guess in a virtual world, it is quite chunky. I've run an apt update and, you know, Postgres is ubiquitous. This is why I started with the database access for Postgres because an app installed PostgresQL is always gonna work unless it fails, in which case it's probably DNS installed Postgres. You know, there's not a lot of variation here. Not gonna be difficult to get this right. Putting a lot of confidence there myself, but,

5:46 and Postgres is, I see no issue, it's on every operating system. That red line has been worried already. I'm on a bint too. You're supposed to be good at this. We'll let it finish before I start debugging. Data's down. Well, mean, it's definitely not what I was expecting. But app is having a moment. So let's just give it a second. This is why I choose Postgres. It's ubiquitous. Every operating system has it and it fails. I wonder if this is the post installation step, which is now not gonna complete because of that one little red line.

6:51 Hello, Omar. Thank you for joining this very special edition of clustered butler posters. Let's see what the error was. This is this is this is taking too long for me. I don't feel like I'm being impatient. I think this is control c doesn't work, by the way. We're just gonna keep opening terminals. Okay. What we got? Database system is ready to accept connections. Peer authentication field for user Postgres. Okay, that I can fix that. Not fussed. Oh, maybe I was being impatient. And now that my control C has come through as it was processing the triggers on

7:28 Configuring Postgres Authentication (pg_hba.conf)

7:40 the libc bend. So let's just make sure it's happy. Right, okay. So the Pure authentication failed for Postgres. I want to be able to debug things as I go along. And so I need to be able to connect to Postgres. So I am going to do a Postgres pghba, and I'm gonna change local all Postgres to trust. This will keep my life easier. Of course, it does mean that any local user on machine can connect, but then as I have access to this machine, I've got bigger problems. So, before we start, and this should allow me to do the

7:51 Connect to Postgres

8:21 SQL user, Postgres. Tada. That's about what we want. So now we have a working Postgres. So, done. The next thing that we need is a Teleport CLI authenticated. So at the moment, I'm not gonna be able to do app install Teleport. However, it is a single well, I know it's a single line. It's a couple of lanes, but it's particular. It's pretty simple. So we just quickly grab this and then we'll talk about the tasks that we're gonna accomplish on today's workshop. Well, this quickly runs. The curl. Oh, yeah, the curl worked. We'll do apt install.

8:32 Installing and Logging into Teleport CLI

9:11 Js Teleport. And that should just do its thing. I'll authenticate with that in just a second. So, there are a few different stages to exposing your databases through the Teleport system. First thing we need to do, and I think we covered this on application access, is that Teleport is broken down into a few components. There's like the Teleport server, which runs, typically runs the off server and the SSH server, and everything else is kind of opt in after that, the proxy server, the app server, the DB servers, etcetera. In order for us to run a TeleportDB

9:51 proxy DB server next to our Postgres server, you know, we don't probably want these to be far apart because then we'd be exposing our Postgres over the big bad internet. Then we just need to be able to generate a token that speaks to the Teleport server and says, hey, I want a token that's gonna allow me to expose a database on this machine. So we're gonna generate one of those. We then have to generate certificates. This only works with x five zero nine TLS mutual authentication on the client side. So we have to generate those certificates, Teleport will do

10:23 that for us, and configure Postgres to accept that as a means of authentication. Once that's configured, we need to tweak the roles a little bit just to allow my user and the Teleport system to be able to access this database system. And something I found out when I was trialing this workshop is that Teleport Cloud just needs to do one extra little step because I don't control the Teleport server component, but we do have to modify a role to allow impersonation of the DB persona. We then run our Teleport DB proxy, and then we'll start interacting with it. We'll list

10:57 available databases, we'll log into Postgres and connect to Postgres. And then just at the end, to show you how this works with your standard tooling, I'll show you how to connect with just PSQL using a Postgres GUI. So we should be able to do this and just under the hour, at least my tests left it that way. I've included all of the answers, so you don't really need to sit and listen to me if you don't want to, but of course I do appreciate the company. And you should be fine to run to the diskette.

11:29 It should just work minus any little random things. And hopefully that was enough waffle for Teleport to be installed. So if I run TSH status status. That duration is on there. I cannot automate it crazy, but we don't have any credentials. We're not authenticated against the Teleport server. So we're going to do TSH login and we have to provide the proxy server, which for me will be Rawkode. Teleport SSH on port four forty three. I'm not rich. So, that'll be user. Like so. Alright. Now I need my password. Password. OTP. Fingers crossed. And good. Yep. There we go. So now

12:35 I'm authenticated against my Teleport Cloud instance. Magic. Hey, Mozz, thanks for joining us. Okay. So we can see now that I have a TSH status, which is gonna be the same output. I have my Postgres running and we tested the work. We connected to it by enabling the trust and the Teleport is already proven. So the first thing we need to do is it's not formatted correctly. I'll fix that there. It's use T control, Teleport Control, to generate a new token of type database. Now this just means that when a Teleport, it's not a server, what is that? A

13:06 Generating Database Access Token

13:24 Teleport proxy, I think that might be it. Teleport edge server, DB server. I need to confirm the lingo there. But when this Teleport instance that we are gonna run locally can only register the database. It only has access to register databases on the server. We don't want it to be able to do anything else. Like, we don't want it to allow people to connect their own servers to our Teleport, or I suppose their own applications or anything like could be used and abused. So, we're gonna run Teleport Control tokens add type DB, and this should

14:00 just spit out this nice little helper command, which tells us how to start a TeleportDB proxy. The command line arguments we need, it's got the token here that we just generated as well as the CA pin so that we can always guarantee and confirm that we are speaking to the correct Teleport server. It has the auth server dropped in here, which we already knew, and then we have the name, the protocol, the URI. So I'm just gonna keep this, create bb proxy, and just dump this into here so we can use it later. Excel. I believe we can do,

14:49 am I getting too confident? Yeah, there we go. TCTR. I can never remember what's Teleport Control and what's TSH. Tokens, learn to spell. Because LSLX flow and we should see our DB. Well, that proxy one. I probably shouldn't show you that. I'm sure it's okay. Here is our DB token. So, if you ever need to get that back, you can use Teleport Control Tokens l s. And I'll clear my screen just in case, even though you can write it and pause it. But still, it makes me feel better. Alright. Thank you, generate token. We are done.

15:26 And, obviously, I just clicked on the answer there. The documentation is is great. You can come in to here and go to database. There we go. Database access was self hosted Postgres. A lot of these steps are already covered here. This is where you find all of your answers. So we've got a question from Moz asking, is it possible to run Teleport DB proxy as a daemon? Yeah, totally. 100% you can. You can create a system new unit file and manage it anyway you want. It's just a banging away with some arguments. So whatever your supervisor system is, feel free

15:52 Is It Possible To Run Teleport Db Proxy as a Daemon

16:10 to take it and run it that way. Of course, that is a much preferred way, just not something I'm gonna be doing today, but you can totally do it. And there's this increment here. Cool. So, the next step is we wanna be able to generate the certificates for Postgres to run-in TLS mode. So, we'll just pop open this answer and copy this and we'll run through each of these things here. So this is a Teleport Control off SANE. This is just gonna speak to the Teleport server and say that we want to be able to,

16:23 Generate the Certificates for Our Postgres To Run in Tls Mode

16:46 you know, provision some certificates that can authenticate against the server. The format here is because we're very specifically allowing access to the databases, but we have format DB. We need to provide the host. So this is where is my Postgres server. I'm going to use localhost today because I'm running Postgres on the same machine that I am running my DB proxy. We can pass dash dash out. This just tells it what prefix to use for the file names. So you're gonna get like a server dot CRT, a server dot PEM, a server I'm sure there's one more.

17:28 And this is just what they are. So we will just call this server. And we get these attempts to leverage how long should these certificates live. So this should finish. And, you know, Teleport, you know, as I was working through this and trying to see how to get this working, there was one thing that tripped me up and we can talk about that later. But Teleport is very handily just giving you everything that you need along the way. You know, we created the DB token and it's like, Hey, you're gonna wanna run a DB proxy here's

17:56 the command. Like, this is, it's just, it's almost too easy to get this, you know, to get this effect, to get this access that I want. And I really appreciate that from the Teleport team. So, we've got our CA file, our key, and our certificate. It has given us the exact thing that we need to copy into the Postgres configuration to make that work. And if we run ALS, we have our files here. Wonderful. So let's close that. So, Postgres doesn't support TLS mode by default. We do need to configure it. There's a couple of different steps here.

18:37 We need to add, we can just copy this. Well, I've not moved them yet. So we've got slash path to server CRT. So yeah, do we really wanna leave these line and slash root? Probably not. Postgres makes use of varlib. Postgres 12 main as a directory with all its configuration and stuff. It also makes use of etc. Postgres 12 main, where it has more configurate Oh, I guess the varlib is more state than configuration. And all the config lists and etc. I've never really had much of an opinion on whether sets are config or state, I feel the line in

19:21 the middle. But for the basis of today, and because this is how I did it on my tests, we're gonna move all of these server files to the varlab postgres 12 main. And if we run our LS again Oh, wrong LS. I was a bit worried there. We have our server fails. And of course, if we run that with a dash L, right? Spot the elephant in the room, we of course need to change the permissions on these fails too. Postgres does not run as a root user, so it would actually not even be able

19:51 Change the Permissions

20:00 to read those files. So we can do a change on ownership, Postgres, Postgres, Rlib, Postgres, 12 main, server star. I'll run my LS one more time. And that looks pretty good. I'm happy with that. Okay. Did I copy? No, I didn't. So we're gonna scroll up and copy this. So we need to add this to our Postgres configuration. Well, main and that lives inside of PostgresQL dot conf. We're just gonna jump all the way down to the bottom. And I'm just gonna save this quickly so that I can copy the path for where all the files are.

20:57 There we go. Let's see if I'm gonna get this right. Just put this don't allow me to use arbitrary splatters. Let's find that. Drop this in. Thank you, Vim. I know a lot of commands said and such, you can actually use wherever it doesn't have to be a slash after like the S at the start to the substitute. You can use anything, it just has to be repeated on the other side. So that's good. So we'll configure our SSL cert file, our SSL key file and our SSL CA file and turned SSL on. The real test now would be to save

21:46 this and restart Postgres. Okay. Well, let's use the control status. Postgres exited ten seconds ago. So I have broken it. Okay. Let's go to the logs and see what I did. Oh, it's ready. Active exited. I must be reading that wrong. Oh, postgres. Yeah. Yeah. It's fine. System d trying to obviously confuse and scare me there, but we're good. So Mozz asked, what does the hash symbol mean on the VUM substitute command? Yeah. That's a good question. So, I I would turn this into like a VUM session or anything, but let's say we have hello.

22:18 Troubleshooting Postgres Restart

23:10 Why is with my typing? Goodbye. Thanks. Alright. The Scottish version. Is that we can now use I'm not sure. It's regex syntax. I'm not sure if it's specific to them and said that supports the random delimiter, but I'm pretty sure it's pretty common. So find me, replace me. This is a really standard search and replace regex. You do s and slash. So I wish I could tell you that the s means search or substitute. I think it does, but I'm not entirely sure. And then normally with regex, we would do slash search term slash replace term

23:59 slash and then the g on the end is for modifiers to your search and replace. You can do the G, which is global, which means replace everything. You can do I, which is a case sensitive. And there is M for multilane. I can't remember. No. Yeah. If you're not doing global, there's maybe like an M which allows you to do multilanes. There's a whole bunch of modifiers that you can put on the end. But because forward slash is such a common symbol and like web addresses and code and stuff like that, You can actually use any delimiter

24:36 on the search replace modifiers that you want and it just works. So as an example here, if we do s and we're gonna do search, I'll do it slash, I mean, that's the default term. We can say hello should be howdy. Oh, I didn't do global. Hello, howdy. There we go. Our hello changes to howdy. But I can do that again and this time I say how without the slash and I can use a plus and do howdy plus hello plus g and it still works. So, yeah, there you go. It doesn't matter what the dormer is.

25:26 You use whatever works depending on what you wanna search and what you wanna replace. Sweet. Okay. Oh, yes. So our postgres is working, which is fantastic. So let's jump back to our workshop. So configure it for TLS mode. I think we've done this. I'm gonna pop open the answer, make sure I've not missed anything. So I say here, yeah, move the start. So location that Postgres has access to, this is a solid choice. Change the ownership, which it did. We ran the off sync command. We added these lanes. Oh yeah. And one last thing. So this

25:47 Creating a Teleport Database Role (RBAC)

26:13 is in the documentation as well. Host SSL is when you're configuring a server, you can additionally modify the PGHP fail. So this fail is kinda like, you know, your PAM configuration on a Linux machine where you configure whether people are gonna log in with password or just keys. And then the order and the fail is the order that the priority exists within the authentication system. It's pretty much the exact same for Postgres. So, we can just copy these two lanes and put them into our PGHBA. And this will just tell us that we allow when someone connects over SSLTLS

26:55 for all user, all databases and all IP addresses that we allow certificate based authentication. So we need to make that change. So we go to etcetera, Postgres, 12 main, pg. And we're gonna stick this right up here. So we prioritize and prepare certificate based authentication. We're gonna check that first. Now we do another big scary restart. I'm not gonna trust some of the status. I will just test. Still good. Nice. So now we have configured Postgres. Postgres is now ready for us to start proxying our access through the Teleport system. Okay, so I'll close these as I go,

28:00 so I don't get too confused. I'm easy, easy confused. So now we have exercise five and there's a 5B. 5B, I'm gonna have to do it because this is Teleport Cloud. If you're just using Teleport that you configured yourself, you do not need to do that step. So we have to add a role now. Format is dreadful. I don't know if had it can't be because it's a pop down. It's just I've just messed this up. I will fix all the formatting. I mean, in fact, you know what? I'm gonna view this as raw just for today.

28:35 Oh, no. Because then I'll see all my I'll I'll pop it open in raw too. There we go. That's a good idea. And then I can copy and paste what I So exercise five, we're gonna add a rule. Yeah. That's okay. I never like it's weird and like, you need the space after the paragraph. I I could commit it and see, but it's not important. Stop following rabbit holes, Dave. Okay. So we need to create a role that is gonna allow anyone who has that role to be able to access the databases. So we have a workshop on

29:18 a deep dive into the RBAC system and Teleport that we'll be covering in a couple of weeks. So we'll keep it super professional for today. But if you have questions, I'm happy to answer them as we go. So we're gonna create a roles file. We paste this in. We don't need this. So the default are really Kubernetes manifests, they'll say here, kind and version. So this is role V4 with a metadata and the name. The name is arbitrary. I can call this local post for SQL access. And the spec here is that we're gonna allow

29:57 star star all the way down. So anyone with this role can connect to any database that is configured and proxy for the Teleport system. And I'll never remember to type this. So let's just call it DB. We can then apply that. So, you can use t control dash f, very Kubernetes like again, where we just say, hey, go and create this role for me please. That's done. We can use roles ls. Nope. Am I wrong? Thought you could get roles. Alright. Okay. We can run the get command, which I think gives you a back of YAML. I guess

30:48 that's good enough for today. And we can see all the roles in the system. Does it work if No. It doesn't work. Not sure why you can't roles list. You can users list. Not a big deal. Okay. So we The Mars has jumped in the comments saying he can teleport control DB get rules. No, because this rule isn't actually specific to databases. This is a general teleport rule that works across app proxy and database proxy and server management, etcetera. But I will I'll I'll try it. No. Just dbls. Yeah. To be honest, the problem is just not

31:57 implemented rules ls. Not a big deal. We don't use it for today anyway. You can always do p control, get roles, save that YAML file and then create apply over the top of it. So if you don't need to change the roles, that's just how you would do it. Not being able to list them on the end of world, let's get it. Keep moving. What we do need to do is we can use users l s to see me. Yep. There we go. I don't have this rule. So we can do t control, get users,

32:18 Assigning the Database Role to a User

32:39 users dot YAML, and then modify the YAML, go to roles, and I just jumped straight in here, like an absolute wild card and save the YAML and then create apply over the top. Now it allows me to modify my user. There is a nicer way, but I can never remember what it is. What's the subcommand on users? Not reset. That's like a user mod command. Maybe it's in my my cheat sheet. Oh, okay. You can just do that. So I could have done use add roles. Oh, but I have to specify all the roles. Oh yeah. I think that's gonna overrate it

33:37 completely. Let's try it. Right? I like to break things. Access. What are the roles that I have? An order. Audit r David Rawkode. So was that removed DB? We're about to find out. Oh, no. Sorry. Okay. So that's just for adding new users. Alright. Okay. So, maybe the only way to edit is just through the get users, modify the YAML, chuck it back in. Something I'd appreciate, we're using Teleport Cloud. I had never noticed this on open source, but maybe, I don't know if it's different is that you can modify myself from here. Don't want DB. Well, get myself DB.

34:22 I haven't seen this in the open source one. I don't know if it's coming or if it's just on cloud or enterprise, but I thought that was quite a new feature. You can also modify the rules themselves too. Excuse me. And that's dropped straight into your YAML thing. There you go. And they list them here. Maybe there's a secret API for getting the rules. So we have a question from Oz. The access rule also has DB names and DB users. Does that override your rule? No, they're merged. So it's not overwritten in any way, they're just merged.

35:12 So it has DB labels, but not DB names and DB users. So you would still need to give yourself a database role with star star across the board. At least I believe that's my answer. That's how I think it works. One more question from Russell. Does that UI show which users are assigned to the role? No, but here you can see them on this list here. That's a wee bit small, isn't it? You can see the roles a user has here, but there's no user list on the corresponding role thing. So, you can't see the users from here.

35:58 Something I did notice during my testing is that now that I have a saying, I bet this works now, that's gonna be annoying me. If I try and delete the database rule, it will tell me that a user has it. Yeah. Because it's still used by user, so you can't do it. Another thing that wasted a substantial amount of my time during the testing here is that your roles, if you change your roles, you need to log out and log back into the system. So, search status, you'll see here that even though I now have the database role,

36:37 I don't have listed locally. And when I try to execute any of the database commands to connect to the database, etcetera, they failed with an access denied. And it's just because the token that I got when I signed in, obviously, you know, if you're familiar with JSON web tokens, they have like the subject and they have the capabilities all embedded in HMAC consent. And so you have to log out and log back in to refresh that. We got another question from Russell. Can you filter the user screen by role? Let's find out. Yes. Because when I say DB, I don't disappear.

37:10 Can You Filter the User Screen by Roll

37:32 So yes, you can filter by editor. Yeah. So it appear you can type in a role name and it will filter the users. Not the most intuitive search bar, like whether I knew I could do that, but, you know, trial and error, that's how we do things. All right, let's get back on track. So we have created our database role, which is star star across the board. Let's take a look at that one more time. So labels, names, and users, star slash star. So anyone with that role can speak to any database that is proxied by Teleport system.

38:08 I don't have that role yet. I'll need to log out and log back in, but we'll I wonder if there's a way for me to refresh that. Well, let's not get a rabbit hole. I've got twenty three minutes left. Okay. So let's carry on. If you have any more questions, keep them coming. So we created the role, we modified our user, I'll add the get users, edit, apply thing here, just for anyone as using self hosted. You may have to be able to just create a new user. Of course, I could just have created a new user too called my

38:42 special DB user, but that's not we'll forget I said that. Okay. On Teleport Cloud, I did find that we needed to add this impersonate capability. So we're going to use the get rules, edit rules, create rules trick at the to add this to our DB rule. So get rules. Roles. Yaml. This access. Order. D b. Allow. Delete, boom, boom. Yep. That's it. So everyone with this rule can impersonate, gives us some rules. Weird though that this is also on the access one. Yeah. Impersonate here. But I'm pretty sure it had an error when I didn't add it to the DB

38:48 (Teleport Cloud Specific) Adding Impersonate Permission

39:49 one. So just when in doubt, do it twice. And we'll apply that. There's a question from Russell that I can definitely guess based on what I've seen during my Teleport workshops. So Russell is asking, if you also have the role privileges, does it apply straight away? I could you grab start to a role you already have and suddenly get full access? Yes. Because the only thing my authenticated token contains is the roles that I have and not the capabilities of that role. So, if I can speak to the server with my JSON web token saying here, can I look at my

40:43 JSON web token? Key. Should I be putting this over here? This is my Teleport load account. For once in my life, I'm adding on the state of caution. I'm gonna look at it locally first because I'm worried. Yeah. Great advice, Russell. Don't do it. So I'm just pulling up locally. I wanna Yeah. I can't share that. Okay. It's not adjacent web token, which I didn't know either. It's the actual x5.1 language, I guess makes sense. But the roles are, will be same than that rather than the capability. So I guess if we have time at the end, we'll try it.

41:56 We'll remove all of my roles, bar the DB one and incrementally add new functionality to it and see if things work. But I suspect it will. Right. Let's get back on track. So now that we have added the impersonate thing, which is just a cloud thing, don't worry about it for self hosted, we wanna be able to run this proxy. Now we format is annoying. We saved this command and a local file, didn't we? Yep. Create DB proxy. So let's fill this out. So we have a token. We've got the pen. We call this my Postgres,

42:26 Create Db Proxy

42:38 the protocol is Postgres and it is available on local host five forty three. Sure. We can make this executable and away we go. So we can see here that is joining our secure cluster. Things are working. It seems to be happy and the database service has been successfully started. So let's pop open one more terminal and get back to where we were. Perfect. I should still have my status. While this is done is because we've had this database proxy run on the machine and register itself is that we're now in a position where if we pop over to databases,

43:20 we will see that we have a self hosted post GRES cluster and click connect, that gives you all the commands that you need to be able to try and interact with this. Yeah, and there's some documentation for the GUI stuff, but I'll show you that in a minute. So we have our app proxy. Done. Now we want to list the available databases. I already mentioned this command. You can run DBLS, so you can do everything from the command line that we can do from the gray. I can see here that I have a Postgres, but I'm not connected to it. There's

43:43 List the Available Databases

43:57 no information here. I don't give a description or label that same, but we're not connected. And the next task as well, log in and connect to it. So we can do a TSH DB log in and then we look at the parameters here. We have to tell the database, which will just be Postgres for me. But when we log in, we can also specify the database user and database name that we wanna be able to access through that session. So we can do a TSH DB login DB user, Postgres DB name, Postgres. Like so. And Postgres is the DB name.

44:04 Logging into a Specific Database (tsh db login)

44:39 Let's see if that works. Our connection information for database Postgres has been saved. So let's try connect Postgres like it suggests. And we get access denied. So this is how I discovered that your roles yeah. You need to say no and load back in for your roles. And the way that I discovered that, there's nothing in the logs here. I enabled session logging on Postgres. I wasn't seeing any connection. I was pulling my hair out mad, but there's a command. We can add a flag debug to our proxy and we're gonna get a whole lot more logs. Hopefully nothing

45:04 Troubleshooting: Access Denied (Role not Active)

45:40 scary that I should be sharing with the world. But if we run this again, we'll see the exact error message that finally led me to work out what the hell is going on. Let's try that connect again. There we go. That is the culprit there. So access to DB Postgres denied, no allow rule matched, rule access matches, rule editor matches, rule other matches, rule default matches, no role DB. And that's when I finally clicked. The frustrating thing about that is I didn't really get that information from the activity thing. So we come to the auto log. You

46:23 can actually see when people are trying to use your databases to Teleport. Again, very, very cool. To me, it just looks like it's a Postgres that are denying me. And I was jumping through lots of hoops of Postgres and checking that host SSL and the HBA configuration and all this stuff. And all you need to do is log out. That's it. So we'll just leave that in debug mode, not the underworld. We'll log out. We will log in, proxy, Rawkode Teleport, SH four four three user. And Teleport Cloud. Password. OTP. And now you'll see we have our database

46:48 Re-logging into Teleport to Activate Role Changes

47:25 role available. And that just means that if I do a connect to Postgres, we should land an AP SQL prompt. Hey, that's all right. So the problem is I need to log in again. I guess it's telling me I can use the DB Gazerna Connect, but I'm pretty sure that should do it. So when I logged out of TSH and logged back in, I think what happened is the TSH DB connect was I think it did a login for me, but without the DB user and DB name parameters. So let's try and see if that changes anything here.

47:46 Successful CLI Database Connection

48:31 Yeah. So you can see now when I do the list, I don't know the connect would do the login for me. There you go. We can see that we've now configured it as Postgres Postgres. So I should be able to just do connect now and it should just work without any additional parameters. It's doing the dance. There we go. And we now have access to Postgres through Teleport. Awesome. That's cool. So a couple of questions from Moz there. I missed did you enable the DB proxies to use SSL to connect with the Postgres DB? The

49:11 proxy only will communicate through TLS mutual authentication. So it doesn't need to configure it. It's just the way that it works. The commands that we use Yeah. You don't need to configure that. It just works. Where's my mess? And most tell me, yes, I will have to log in again. Yep. Cool that the connect does do the login for you. So I guess it's probably just a bit easier to do connect DB user, blah, blah, blah. Yeah, log in first. Be nice and explicit. All right, we've got ten minutes left. Let's take a look at our audit log.

49:53 Viewing Database Audit Logs and Session Recording

49:58 We have our database session ended. Look at this, is this not just the coolest thing ever, right? Our database session was started. So we can see what time I started my session, the user, we can pop open the queries. So, you know, I could hook, I would love to hook all of this up to influx DB, low key, and just analyze queries and see what's going on. It's just practical. And we can see that I ran a select from the catalog, although I used the alias backslash L. What does it upload the session? I don't think so.

50:42 Record the session has been uploaded. Oh, yeah. Okay. So something Teleport Cloud does is work through a session recording to Amazon s three for you. Don't know why it doesn't show here. Wonder if there's a small delay on picking that up. Not a big deal. But I'm assuming I can replay that, which is nice. I didn't know that. So we'll come back here and we got two tasks to finish just to end this with a bit of fun. So we logged in, command is there. We connected, command is there. There's a really cool thing you can use instead of using

51:13 Connecting with Native PSQL Client (tsh db config)

51:23 a DB login and connect, you can actually ask for a config, which will allow you to use the native tooling. So we can do DB config postgres. I'm sure there was another thing you could do. Yeah. Env. There we go. This will actually drop out something I can then evaluate in my shell and get a whole bunch of the PSQL things that I need to connect. So let's try it that way. That's not in my gate, but I'm gonna add it because it's too cool. So let's do pb env equals to res eval. Make sure that worked.

52:21 Mhmm. So I just did that. Yeah. See, it's going through. We can see the logs that's set in the proxy. And there I am. It's an SSL connection. So typically when I would run PSQL, that's just gonna use the daemon approach and log in. But the fact that we can do the DBN and go through the secure way, which is also recorded, that's really cool. So as a Postgres operator administrator, I would blanket just block any access that rely on our proxy and set authentication. That's awesome. I love that too. So, Mozz asked, would I please share some details on how

53:07 How To Enable Session Recording on Teleport

53:08 to enable session recording on Teleport eight? You tried just to do it, it wasn't working. Feel free to post the error message that you got into the Discord channel. I'll be sure to take a look at it. And I'll do some tests tomorrow as well and make sure it's still working for me. I didn't notice any problems on my local setup, but we'll work it out. Just drop it into the Discord and I'll get you some help. Alright. So, what about the GUI? So, I'm gonna jump onto my local machine. I don't have the rule.

53:31 Gui

53:39 I'm gonna have to log out. Again, Password again. OTP. So this is cool. And as I send the answer here, but you can use the proxy command to get a Oh, wow. There we go. Teleport is watching. Hey, Teleport. Teleport says, you may want to check the RBAC for access. Yeah. So Moz, I believe that's the relation to your question. Okay. So, got a few minutes left. So we wanna use the GUI. So there is a TSH proxy command where we can run a proxy to the DB and to Postgres. And that's just I didn't log in. Didn't

54:46 log in, not this thing. Log in Postgres DB user Postgres DB name Postgres. I guess this has to go yeah. The database name on the end. This should log me in and then I should be able to create that proxy, which will allow me to use a GUI client, which, you know, maybe a lot nicer than learn how to PSQL command. So it runs a proxy on a port as port number changes every time. So you do need to copy this. I don't think Teleport have a Discord channel. They have a Slack. Sorry, Moz is in the comments. Can you

55:35 please share your So my Discord is Rawkode.chat and the Teleport Slack, I believe is goteleport.com/slack. Yeah, there we go. Was trying to log me in, but yeah, so goteleport.com/slack and lots of really helpful people in there as well. Okay, so we've ran our TSH DB proxy postgres. We've got this port number and IP address, just local host, and then we've got a whole bunch of files that we can configure with our client. So far today, I'm gonna use Beekeeper studio. I thought it looked good. That was my reason for picking it. I thought it was the nicest looking of the

56:14 clients I was playing with earlier. So we're gonna connect to Postgres, local host. We drop the port and Oh, it's not letting on the thing, but there we go. Five, was it 55544? Yeah, 55544. We have to enable SSL and then we can hook up these files. So we need our CA cert, which is certs. Pen, our certificate, which is, you know, I'm just reading this from this line on the bottom of my terminal here. The certificate is the Postgres X509, which is in this directory. And we need the key file, which is just my

56:33 Enable Ssl

57:02 x five one nine identity key. So that will be here. And we're going to connect as Postgres. We don't need the password to the Postgres database. Attest. Moment of truth. Longer is better, right? I mean, yeah, that's worked. And we hit connect. Look at that. We're in. We can see on the left, it's finding tables. We'll give that just a few seconds. We've got the query where I can type something here. You know, can just select star from information schema. Nope. That's my all complete. I can't do this on my own. P g catalog. I

58:04 can't remember. Maybe that's pulling out millions of stuff. Let's cancel. Oh no, wasn't too bad. But we can just pop around and take a look at the schema. I thought that was really cool. I haven't used this client an awful lot, but I thought it was a nice looking native black client. So that's what we went with for today. I'm gonna close that. I'm gonna close this and this head back to here. And we have completed all the tasks in today's workshop with a record just under two minutes left to go. I'm gonna pop back over

58:40 Conclusion and Upcoming Workshops

58:40 here. If you have any questions, drop them into the chat and I'll tackle them before we wrap up for today's session. But we'll be back on Monday at 12:30 UTC, where we are doing MongoDB. Let me take a look at my channel. Oh, no. Monday is deploying Teleport on Kubernetes. There we go. And then next this time next week, we'll be doing MongoDB. There we go. I may swap them. Just so we're doing the database ones back to back. We'll do the Kubernetes one at the end of week. We'll see. I'll think about it. All right. So there haven't been any questions.

59:23 Ma says, this is awesome. You're right. This is a really cool feature. I love to do the best access. I love as I was typing Teleport commands that the output was then guiding me on the next step and helping me get forward. I like it when tools really just kind of try to make good life easier. So thanks and props to the Teleport team. All right. Let's call that a wrap. Thanks for joining me today. Some great questions. I hope you like this feature. Let me know how you get on and have a wonderful weekend. Goodbye all.