How to create roles, databases and tables in PostgreSQL (getting started with PostgreSQL, part two)

Setting up PostgreSQL the right way starts long before your first table exists. In this guide, you’ll learn how to create a dedicated role instead of relying on the postgres superuser, spin up a database with proper ownership, organize it with schemas, and build out your first tables with primary and foreign keys.

Every step includes the exact psql commands and the real terminal output you should expect to see, so you can follow along in your own container and know immediately if something’s off.

How to create a role in PostgreSQL

One of the more important aspects of PostgreSQL to understand is ownership. The ownership of any object – whether a schema inside a database, or the database itself – is fundamental to PostgreSQL security and behaviors.

With that in mind, instead of first creating a database to work on within PostgreSQL, we’ll start by creating a role. With the role created, we can then log in to that role and validate that it works. Once it’s working, we can move on to creating a database to work on within PostgreSQL.

How to run a script in PostgreSQL with psql

Instead of typing in psql or copying and pasting into it, we’ll let psql run a script for us this time. Externally, in your operating system, we’re going to copy the file creatingrole.sql into the volume we created with the container.

In Linux, you can use the Files GUI (graphical user interface), or issue a command like
cp "05. Creating a Role/creatingrole.sql" /home/user/bu

If you’re in Windows and not running through the Windows Subsystem for Linux (WSL), use either File Explorer or a command similar to Copy-Item "05. Creating a Role\creatingrole.sql" C:\Users\grant\bu.

Of course, you’ll have to modify these commands for the paths on your own system. What this does is place the file into the local folder visible inside the container as a volume.

Do note that the container must be running. You’ll want to open a Bash shell to the container if you’ve closed it (see earlier in the guide INSERT LINK). Once connected to Bash in the container, you’ll see something like root@543bcc6b5270:/#.

We can then validate that the file exists by running the ls /var/lib/postgresql/bu command within the Bash shell, adjusting the path as appropriate to your system. The output will look something like this:

How to execute a script in PostgreSQL with psql

Now that we know the script is available, we can execute it through psql using the command
psql -d postgres -h localhost -U postgres -f "/var/lib/postgresql/bu/creatingrole.sql".

This should run successfully to create a role called radiodev with a hard-coded password (*securepassword*). While still in Bash, you could also run a simple query to see if the role actually exists, and if it has the permission to create a database. The command we issued through the script is:

Now, let’s break this down a little. The first part should be straightforward enough: CREATE ROLE radiodev means we’re creating a role called ‘radiodev‘. The WITH command then allows us to add some additional behaviors to the role. We’re also creating this role as one that can connect to the cluster through the LOGIN process.

Then, we’re giving it an explicit permission – the ability to create databases (CREATEDB) – and we’re supplying a password for the role. We won’t need the password in this guide, since we’ll be doing the work through psql running as a local connection (therefore, a trusted one.)

How to validate the creation of a role in PostgreSQL

I’ll need to leave Bash to operate within my OS. I’ll then use the appropriate command to copy the file into the volume folder, before heading back into Bash to execute the query:

The results should come back like this:

Going forward, we’ll copy any (or all) files at the same time, rather than going in and out of Bash to do it. Of course, you can use other mechanisms as well. Later in the series (INSERT LINK HERE), I’ll introduce additional tools for querying PostgreSQL on top of psql that can make these operations easier.

How to test a role in PostgreSQL

We can now use the new role with our PostgreSQL database. To see it in action, we can use the
psql -d postgres -h localhost -U radiodev -c "\conninfo" script.

A couple of points here. In our initial use of psql, we typed in commands directly. Then, in the beginning of this part of the guide, we executed the scripts from within files. Now, we’re simply passing a command through the -c flag. Putting SQL (or, in this case, a meta command) inside double quotes will allow the command to be executed.

The output from my machine is:

Importantly here, ‘Superuser’ is set to the value ‘off’. Put simply, this is a different role on the cluster – not the principal administrator (postgres).

We’re now ready to create our first database in PostgreSQL!

How to create a database in PostgreSQL

Now that we have a role with the permission to create databases in PostgreSQL, we could immediately create one as that role. However, first, I want to show some other ways of creating a database in PostgreSQL.

How to create a database in PostgreSQL as a superuser (and should you?)

If we were to issue a CREATE DATABASE command like this, that database would be owned by postgres (the superuser): psql -d postgres -h localhost -U postgres -c "CREATE DATABASE my_first_db;". Not only would this create a database owned by postgres but, since we gave it no other option, it’s just using the defaults.

PostgreSQL has two template databases: template0 and template1. Template0 is for the system and should be left alone, while Template1 is the default template for creating databases.

Since this is a brand new cluster with no other databases or templates created, this method is perfectly OK for our purposes. You can create custom templates or even use other databases as a template when creating a new database, but that’s beyond the (simple) scope of what we’re doing here.

Why you shouldn’t create a database in PostgreSQL as a superuser

On the other hand, there are a number of reasons we don’t want to use the superuser (postgres) to create databases in PostgreSQL. First, and most importantly, it violates the principal of least privilege.

This is because the superuser bypasses a lot of security constraints, so we’re potentially exposing things in the system. Remember: ownership and permissions in PostgreSQL cascade from the role that created the object, and even a database in PostgreSQL is an object!

You want a clear, easily-to-audit ownership chain, which is why it’s best to start with defining ownership through roles other than the superuser.

We can look at the owner of a database using a simple query:

We don’t have to use the login to create the database. It’s perfectly possible to assign ownership on creation with
psql -d postgres -h localhost -U postgres -c "CREATE DATABASE my_second_db OWNER radiodev;".

We then see the following results:

Altering the script as follows:

We then get the following results:

How to create a database in PostgreSQL (the simple way)

For the rest of the guide, we’ll be using a database called ham_shack. And, since we’re going to let the database owner also own the other objects we create within ham_shack, we now need to actually create the database.

To do this, we can just let the defaults handle things for us:
psql -d postgres -h localhost -U radiodev -c "CREATE DATABASE ham_shack;"

We can do it like this because, other than the owner, there are no database options we need to worry about right now.

With the database created, let’s connect to it through interactive psql instead of issuing commands all the time:
psql -d ham_shack -h localhost -U radiodev

Since we’re connected to a database other than postgres, the resulting connection will look different than what we’ve seen in other parts of the guide: ham_shack=>

Let’s also issue a few commands and queries to satisfy ourselves that we are indeed in a new database. First, we could run the tried and tested \conninfo meta command, giving us:

Another way would be to query using a built-in function:

You can simply copy and paste this command into psql. Since nothing is executed until the statement terminator is reached – the semi-colon (;) – you can easily have multi-line commands. The function in use is current_database(), which returns the database we’re connected to. The results are:

As you can see, we’re now in a new database owned by the role we just created. Let’s exit psql for the moment, using /q – but we will still be connected to the container through Bash.

With our role and database in place, it’s time to start to work within the database. Specifically, creating a schema.

How to create a schema in PostgreSQL

Similar to other data platforms, PostgreSQL uses the concept of a schema as a mechanism for organizing a database. A schema can also be used for security, data isolation, and as a means of logically storing related objects.

By default, each database (including the postgres database) comes with the public schema. While many databases simply store all tables in this schema, it’s better to store your tables, views, etc., within defined schema. This is partly for security, but it’s mainly just good management practice.

Let’s start by connecting directly to psql and seeing what schema are already in the database, if any:
psql -d ham_shack -h localhost -U radiodev

Once connected to psql, we can take a look at the schema in the ham_shack database using the /dn meta command. The results are:

So, yes, this confirms that it’s only the default schema (public). However, if we want more information, the meta commands can actually show you more than just a simple list. We can make the call with \dn+, which gives us additional information (mainly on the privileges for the schema in question):

How to create your first schema in PostgreSQL

Let’s create a schema called gear from within psql:
CREATE SCHEMA gear;

Using the \dn meta command, we can now see our new schema:

However, let’s now focus on the search_path. First, let’s see what’s currently in it by running the SHOW search_path; command.

On a completely new database such as this, it should return:

What is the PostgreSQL SHOW command?

The PostgreSQL SHOW command is used to return runtime configuration parameters. This could be settings such as timezone or max_connections but, in our case, we’re going for the search_path.

What is the PostgreSQL search_path?

The search_path is where the role you’re operating within looks through the database for objects. If you don’t have a schema in the search path, you won’t be able to see objects within that schema in order to query them.

The order of the search_path matters as well, as PostgreSQL will look within the first schema in the search path for an object. If it finds it there, it won’t continue to look through the other schema, assuming you haven’t used the schema when referencing the object.

If you do use the schema in the object name (such as gear.radio for a table name), and you have access to that schema, the search_path doesn’t matter.

However, if you simply reference the table radio with the search_path we currently have, you won’t find that table – even if you have the right access.

The "$user" is a token that resolves to the currently connected role. This is where you could create a schema called radiodev and it would automatically be referenced by our login.

Thankfully, modifying the search_path is easy:
SET search_path TO "$user", gear, public;

If you don’t include "$user" or public when you change the search_path, they’ll be removed – so be careful. However, using the SET command will only change the search_path for the duration of the current connection.

How to add the gear schema to the role

In order to change the PostgreSQL search_path permanently, you have to either ALTER the role itself, or the database.

Here’s how to add the gear schema to the role:

This change will only take affect on new connections for this role. You would either have to use the SET command along with the ALTER ROLE, or, disconnect and reconnect to the database. However, before we test the new and improved search_path, let’s add one more schema:

Now, if you connect again – using the Docker command to Bash (from part 1 of the series) to connect to psql – and run the SHOW command, you’ll see the following:

One point to add: there’s no way to simply ALTER the search_path to add a schema. You must use the SET or ALTER ROLE commands, as laid out, with the full list of items you want in the search_path. If you leave items off the search_path, they simply won’t be there.

Up next: creating tables!

How to create a table in PostgreSQL

With a database and a schema in place, we can begin to create tables in PostgreSQL. I’m not going to fully explore all the possible mechanisms around table creation and management in this guide – however, we will both CREATE and ALTER tables so that you get a real sense of the syntax within PostgreSQL.

This will include all the basic PostgreSQL behaviors such as data types, nullability, and primary and foreign keys.

The CREATE TABLE command in PostgreSQL

The essential command to add a table to a database in PostgreSQL is CREATE TABLE. Much of this command’s behavior is similar to other data platforms, but not everything, as I’ll explain.

To begin, here’s an example of the basic syntax to create a new table:

The basic syntax is clear. You supply the name of the table at the start, and the parenthesis are then opened and closed to contain the columns being created.

In this simple example, we’re creating two parenthesis’: antenna_id and antenna_name, both within the antenna table. Do note that, with PostgreSQL syntax, all objects are stored as lower case. There’s no mixed case (or spaces) unless you use double quotes around the object name at all times – for example, "AntennaName".

However, do avoid using double quotes. We can instead implement “snake case”, which uses underscores to separate words in object names, making them clearer to understand. Just bear in mind that the object name length limit is 63 bytes (63 characters) unless you’re using UTF-8, in which case it’s 15-31 chars.

We’re referencing two data types for these two columns: int and text. You don’t need to define a size for int (integer) because it’s fixed-length, but you do for text.

A note on using TOAST in PostgreSQL

While PostgreSQL does supports fixed-length string columns (such as VARCHAR(50) for a variable length column limited to 50 characters), common practice is to simply use text, and then let TOAST (The Oversized-Attribute Storage Technique) deal with the data.

Arguments could be had that this is a poor design choice. However, since it’s so common, I’ve chosen to follow that standard in this guide.

Back to the walkthrough, and we’ve set up our first column – an IDENTITY column. This will be auto-incremented and generated on the fly. I chose to set it as BY DEFAULT instead of ALWAYS to allow for the need for an IDENTITY INSERT in the future.

To run the query, connect to psql and enter psql -d ham_shack -h localhost -U radiodev into the command line. Execute the query and then use the /dt meta command to open up a list of tables in the database.

The output should be:

Why schemas are so important

Note that, despite not supply a schema name to the table, the table is in the gear schema. This is because every table must belong to a schema – including all objects, functions, views, etc., within a database.

If a schema is not supplied, search_path is used to determine where the object is created. Remember from earlier, when we left off with this search_path?

How this works is similar to trying to find an object: when you create an object, the search_path is used. It’ll try to find both a valid schema and one you have permissions to create an object for.

You may also recall that the “$user” schema is a default, and is linking to a schema based on the role. However, that schema doesn’t actually exist in our current configuration, so it went to the gear schema next.

And, since radiodev is the owner of the database, the role had permission to place the table there. This illustrates how important it is to reference the schema when creating objects within PostgreSQL.

A more appropriate example would be to create a radio table like this:

To explain: we are supplying the schema now, so there’s no more ambiguity. You’ll note that the column brand doesn’t specify if it’s nullable or not. If it’s not defined, the column can contain NULL values. We’ve also created a primary key constraint.

We can leave the name off because PostgreSQL defaults to <table name>_pkey, but it can be added if necessary.

Let’s create one more table before we move on to ALTER:

Nothing of significance to note in this table. I did however decide to illustrate the use of constrained length columns.

The ALTER TABLE command in PostgreSQL

Let’s start with adding a primary key to the antenna table, since one was left off:

The syntax should be straightforward, since it’s the same as what we’ve used when creating tables. Finally, let’s add two more tables to the database:

These tables show how foreign keys are created on tables in PostgreSQL. You can also use ALTER TABLE to add a foreign key.

Note that we’re able to cross schema for one of the foreign keys – radio_id -in the people.contact table, as long as we use the appropriate schema. We can also create compound primary keys.

Then, for the last table, I included the very common IF NOT EXISTS command. This is way for you to validate that there isn’t already a table in existence when you attempt to create one.

The DROP TABLE command in PostgreSQL

Just as we can CREATE and ALTER tables in PostgreSQL, we can drop them as well.

For example, let’s test this script: DROP TABLE IF EXISTS gear.radio; (while keeping the tables we’ve already created).

Once run, you should receive this error:

PostgreSQL protects the data integrity of the database by ensuring you can’t simply drop objects with dependencies. CASCADE will drop dependent objects, so exercise extreme caution in its use. You’ll also note that I used IF EXISTS to validate that there is a table prior to running the DROP command.

With that, we now have enough tables in place. Next up: adding data!