For simple Python scripting, you most likely won’t need to worry about creating group roles, and you
can just assign privileges directly to the login roles. That’s what you’ll do in this example.
However, PostgreSQL also handles privileges a bit differently than MySQL. It doesn’t allow you to
grant overall privileges to all objects in a database that filter down to the table level. Instead, you
need to grant privileges for each individual table you create. While this is kind of a pain, it certainly
helps enforce strict security policies. Because of that, though, you need to hold off on assigning
privileges until you’ve created the table for your application. That’s the next step in the process.
Creating a Table
Just like the MySQL server, the PostgreSQL server is a relational database. That means you need to
group your data fields into tables. As you can see here, you use the same CREATE TABLE statement
to create the employees table in the PostgreSQL pytest database:
Click here to view code image
pi@raspberrypi ~ $ sudo -u postgres psql
psql (9.1.9)
Type "help" for help.
postgres=# \c pytest
You are now connected to database "pytest" as user "postgres".
pytest=# CREATE TABLE employees (
pytest(# empid int not null,
pytest(# lastname varchar(30),
pytest(# firstname varchar(30),
pytest(# salary float,
pytest(# primary key (empid));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "employees_pkey" for
table "employees"
CREATE TABLE
pytest=#Once you have created the table, you can list the tables by using the \dt meta-command:
Click here to view code image
pytest=# \dt
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+----------
public | employees | table | postgres
(1 row)pytest=#Now you’re ready to assign privileges for the employees table to the pi login role so that it can
access the table. Here’s how you do this:
Click here to view code image
pytest=# GRANT SELECT,INSERT,DELETE,UPDATE ON public.employees To pi;
GRANT
pytest=#You can now log in to the PostgreSQL server with the pi user account to connect directly to the
pytest database. From the pi user account’s command prompt, you enter this command:
Click here to view code image