name. This is a great reminder when you’re ready to create your database objects: You can easily tell
where you are in the system.
When you’re done working with the PostgreSQL server, just enter the \q command to return to the
Linux command prompt.
By the Way: PostgreSQL Schemas
PostgreSQL adds another layer of control, called the schema, to the database. A
database can contain multiple schemas, and each schema can contain multiple tables.
This allows you to subdivide a database for specific applications or users.
By default, every database contains one schema, called public. If you’re going to
have only one application use the database, you’re fine with just using the public
schema. If you’d like to get fancy, you can create new schemas. The following example
just uses the public schema for the tables.After you create the database to use in your Python scripts, you need to create a separate user account
that your scripts can use to log in to the database.
Creating a User Account
After you create a new database, the next step is to create a user account that has access to it for your
Python scripts. As you’ve already seen, user accounts in PostgreSQL are significantly different from
those in MySQL.
User accounts in PostgreSQL are called login roles. The PostgreSQL server matches login roles to
the Linux system user accounts. Because of this, there are two common thoughts about creating login
roles to run Python scripts that access the PostgreSQL database:
Create a special Linux account with a matching PostgreSQL login role to run all your Python
scripts.
Create a PostgreSQL account for each Linux user account that needs to run Python scripts to
access the database.This example uses the second method: You create a PostgreSQL account that matches the default pi
Linux system account on the Raspberry Pi. This way, you can run Python scripts that access the
PostgreSQL database directly from the default Raspberry Pi user account.
First, you must create the login role, like this:
Click here to view code image
pytest=# CREATE ROLE pi login;
CREATE ROLE
pytest=#This is simple enough. Without the login parameter, the role is not allowed to log in to the
PostgreSQL server, but it can be assigned privileges. This type of role is called a group role. Group
roles are great if you’re working in a large environment with lots of users and tables. Instead of
having to keep track of which user has which type of privileges for which tables, you just create
group roles for specific types of access to tables and then assign the login roles to the proper group
role.