Compose PostgreSQL - Making users and more

PostgreSQL is a powerhouse of SQL driven database power and Compose's PostgreSQL is all that with the power of Compose's cloud deployments. But before you can harness that power you need to create users to access your database. In this article, we're going to show you the quick way to do that and then introduce you to one of PostgreSQL's powerful tools. Let's begin, right after you've created your first PostgreSQL database.

Making users

When you create a PostgreSQL deployment, there's only one role that has been created for the database and that's the admin role. "Wait a minute" you may be thinking "I want a user not a role". PostgreSQL has pulled in all the concepts of users, groups and permissions and turned them into one concept, roles. Roles can represent one or many users, one role can grant another role membership to grant privileges, roles can own tables or other database objects. Roles are PostgreSQL's swiss army knife of access control.

Now the admin role that is created is not the database superuser. Thats a more restricted account that isn't remotely accessible. The admin role is, basically, the first user Compose creates on the PostgreSQL database and it has permission to create new databases and create new roles. This user role is also there so essential maintenance can be performed by Compose's own automated processes. You can use the admin role as your sole login to the database, or you can create your own roles. Let's first look at the admin role.

When you look at your overview page for the database you'll see that information in the Connection info panel under Credentials. Or rather you won't because by default it is obscured.

Below the credentials are the connection string you use to connect applications to your database and the command line you can use, if you have psql installed, to create an interactive command line into the database. Notice all the sensitive information is hidden in these too by marking where you would substitute a username or password.

When you click the Show link – you'll be prompted for your Compose account password before anything is shown – the credentials, connection string and command line will all be populated with the admin role's credentials. You can use these as is in your applications if you wish. Make a mental note of the admin password then click the Hide link which appeared where the Show link to obscure that information.

Creating roles

You may, though, want to create more roles to be used for particular purposes. It's not necessary to create a role-per-user or role-per-application. Experience tells us that the more roles you create, the more complex your access control will be and the harder to manage. With that in mind we suggest that you create one or two roles at most with appropriate capabilities and use them. The quickest way to create one of those roles is to use the data browser. click on the Browser button in the sidebar.

We've talked about the data browser before, but since that article was published, its gained the ability to let you add and remove roles. The first thing the browser shows you are the databases currently configured and by default on Compose, your first database is called compose. You can create more databases here by clicking on Create Database in the top right, but for now we're going to work with the default compose database so click on that in the database list.

And now we're into the table level view of the browser, or rather not, because this database doesn't have any tables yet. We'll get back to that. Right now we want to create a role, and we can do this by clicking on the Roles option in the sidebar.

But, yes, we just want a user and if you look, you can see we are viewing Users, roles which are configured as database users. And there in the list is our admin user. You can see the power that user has because listed there are the three roles it is a member of - login so admin can log in, createdb so it can create databases and createrole so it can create new users and roles. We can make a user of our own here by clicking on Add User in the top right.

This is where we can add user roles; we just fill in the blanks in the command line, so if we're making a user called fred and giving them a password 'drowssap' we put those in the appropriate fields.

If we press Add role now though, the created role would just be an unprivileged user who could not login. Click on the Login button to add that privilege to the user. If you want the user to be able the create databases and roles, click the appropriate buttons too but remember in the world of privileges, less privilege usually means more security. When you are done, press Add role and the new user will be created. Apart from dropping the user, thats all you can do with the database browser but its enough to create new users. The next stage will be to connect to the database using the new user we created.

Meeting psql

PostgreSQL's command line is called psql and you probably don't have it as it is usually bundled with the PostgreSQL database system. This is a common occurrence with database software and it means you'll have to download and install the database software locally to get the official tools – the important part is you shouldn't run the database itself. You can find where to download binaries of PostgreSQL on the project's website. For discussion purposes, we'll set up Mac OS X. If you look on the Mac OS X packages page you'll find a number of options. There's an EnterpriseDB graphical installer and the Postgres.app GUI installer; we'll skip them as they are very much about getting the database itself running. There's also packages in the Fink, MacPorts and Homebrew package managers. At Compose, we're big fans of Homebrew because it works so well. You'll need to install Homebrew first, follow the instructions on the home page. Once that's installed, just run brew install postgresql...

Most of the text there relates to how to configure the database server to start up so we can ignore that. At the end of the process, what's important is the psql binary is installed in /usr/local/bin. Now you can get to connecting. Head back to the Overview for your Compose PostgreSQL database and look for the Command Line connection string. Use that entire line, substituting in your new username. You'll be prompted for your password and then connected:

Congratulations, you've just plugged into one of the most powerful command line tools for any database. At its most basic, you can type in Postgres SQL commands and see them executed. Let's create a table:

The command like doesn't consider a command complete until it is terminated with a correctly placed semi-colon. The thing to keep an eye on it the prompt, specifically the character after the database name and before the >. When it's = it is the start of a new command. When it's - it means that this is a continuation of the previous line and when it's ( it means that what's been typed so far has opened a parentheses, but not yet closed it so a semi-colon entered before closing the parentheses would be an error.

Here we are creating the table rockets and we open the parentheses on the first line then hit return. Then we enter the columns, with commas as separators – we could type these all on one line but this is easier to type and read back. Finally we close the parentheses and end the statement with a semicolon. Psql then echos back the type of command that has been run (or displays and error) and returns to the = prompt. We can then insert some data into our new table:

The last number after the INSERT reflect the number of rows inserted (usually). Of course, we can select to get that information back. Here we'll break up the command over a couple of lines, because we can:

If you want the full list of commands available, enter \h to list all the SQL commands and follow the \h with the name of a command to get further details on it. It's useful help, but remember the PostgreSQL documentation is also a useful companion available online or offline as A4 or US sized PDFs.

But it's not only SQL commands that can be entered into psql. It has its own rich command set too. To list those commands type \? - all the psql commands are preceded with a backslash. One of the essential commands is \d which will tell you about the objects and their relations in your database. Without any parameters it'll tell you about tables like so:

Give it a parameter like the name of a table and it will give you information about the columns and indexes of that table:

There's a huge number of psql commands you may want to put to use. The \e command will call up vi or the editor set in the environment variable "PSQL_EDITOR" and let you edit the last SQL command. \i will read and execute commands from a file. \s will display your command history and yes you can cursor up and down through that history. \w lets you write the query buffer (where the last command is saved) to disk. One favorite is \watch which will repeat the last query every two seconds – follow it with a number of seconds to adjust that. We could spend an entire article looking at the applications and uses of the psql command set. Suffice to say for now it is extensive and very useful.

Wrapping up

We've shown you how to create users quickly on Compose PostgreSQL and how to use those new users with PostgreSQL's powerful command line. If you think that's powerful, just wait till you see what you can do with the database itself!