[Courses] [Postgresql] Week 6

[Michelle Murrain]

Hi all, here's this weeks lesson:

=== Week 6: PostgreSQL permission system and system tables ===

== Database and Table permissions ==

We've already talked a bit about users in Postgresql, and how to create 
them. To recap (since we'll need this later):

   CREATE USER username WITH options
   or
   CREATE ROLE username WITH options

The options include:
   * [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
   * SUPERUSER / NOSUPERUSER
   * CREATEDB/NOCREATEDB

Beware, CREATE ROLE creates a role (user) that cannot by default login. 
So if you want to have the user login, either use CREATE USER, or CREATE 
ROLE username WITH LOGIN ...

For more detail, as always, check the excellent documentation: 
http://www.postgresql.org/docs/8.2/interactive/sql-createrole.html

How do you know what users are on the system? The magic of the view (a 
view is a select statement that looks like a table. More on views 
later.) 'pg_shadow' is your friend (you probably have to do this using 
the postgres user):

   postgres=# select * from pg_shadow;
     usename  | usesysid | usecreatedb | usesuper | usecatupd | 
       passwd                | valuntil | useconfig
 
----------+----------+-------------+----------+-----------+-------------------------------------+----------+-----------
     postgres |       10 | t           | t        | t         | 
                             |          |
     mpm      |    16385 | f           | f        | f         | 
md5d39a95c4c027f65ce869ac24bcfb2f5d |          |
    (2 rows)

So the postgres user can create new databases, is a superuser, and can 
update system catalogs (superusers can't unless this flag is set.)

So you have a user (or several). When a user creates a database (if they 
are allowed) they have control over that database, and all of its 
tables. But other users need to be given permissions to use that 
database. In general (this is true of any database management 
system)only give users as much permission as they need, never more.

In order to give users permission over database objects, you need to use 
the "GRANT" command of SQL. Lets say a user created a database, and had 
several tables in it. But they only wanted to give a particular user 
read access to one table.

   GRANT SELECT ON table_name TO user_name;

GRANT statements include SELECT, INSERT, UPDATE, CREATE, etc. See: 
http://www.postgresql.org/docs/8.2/interactive/sql-grant.html

== Postgresql system catalogs ==

PG has a complex set of system catalogs, most of which are impossible to 
really read or understand unless you really grok the way pg works. 
However, there are a set of "views" that put this information together 
in a fairly accessible way.

----

First, a digression. What is a view?

A view is basically a SELECT statement that is turned into a table you 
can read using SELECT statements. It is a **very** handy tool. If you 
have a very complex set of related tables, you can create a view with a 
complex SQL statement, and then forever onward access that data using 
standard select statements.

Lets take an earlier example, with the customers and books:

    CREATE VIEW customer_orders AS SELECT 
customer_first,customer_last,city,date from orders,customers where 
orders.customer_id=customers.customer_id;

This would result in a table. If you did:

    SELECT * FROM customer_orders;

You'd get:

^ customer_first   ^ customer_last   ^ city               ^ date |
|  Schmo 	   | Joe 	      |  New York          | 2008-01-02 |
|  Murrain 	   | Michelle 	      |  Shelburne Falls   | 2008-01-02 |
|  Sunflower 	   | Penelope 	      |  San Francisco     | 2008-01-03 |
|  Murrain 	   | Michelle 	      |  Shelburne Falls   | 2008-01-02 |

----

OK, back to pg's system views. They include views like pg_tables, 
pg_shadow, pg_settings, etc. You can't change data in a view, you need 
to change data in a table. In most instances, the pg internal tables are 
for information only - you won't want to change them too often, but they 
are good to know about.

== Assignments ===

Create a set of tables, and a new user. Test out giving that user 
limited privileges. What happens when you try to do something that 
you're not allowed to do?

Create a view. Destroy a view (how do you do that?)
-- 
Michelle Murrain
Coordinator, Nonprofit Open Source Initiative
http://nosi.net

Skype: pearlbear
AIM: pearlbear0