=== 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.
== 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.