PGCon2009 - Final Release
PGCon 2009
The PostgreSQL Conference
| Speakers | |
|---|---|
|
Stephen Frost |
| Schedule | |
|---|---|
| Day | Tutorials - first day - 2009-05-19 |
| Room | DMS 1110 |
| Start time | 09:00 |
| Duration | 03:00 |
| Info | |
| ID | 132 |
| Event type | Other |
| Track | Tutorial |
| Language used for presentation | English |
PostgreSQL Access Controls (AuthN, AuthZ, Perms)
Controlling Access to your database- Roles; Kerberos, LDAP integration; Database, Schema, Table, and Column-level(!) permissions
PostgreSQL offers many options for controlling access, from authentication and log in to the role system and finally the hierarchy of authorization to specific resources. System Administrators and Database Administrators need to understand these complexities to ensure their system is both robust and secure. With 8.4 there have been many changes to existing options (Kerberos mapping support, finally!) as well as whole new capabilities (column-level privileges, TRUNCATE privilege!). We will go through all of the authentication options that PostgreSQL offers, followed by how to integrate with existing single-sign-on and other infrastructure (Kerberos, LDAP), then walk through setting up roles following best practices and privilege separation, and finally go through the privilege system from database-level down to column-level.
An introduction and thorough review of access control in PostgreSQL. All access control will be covered, but special attention will be paid to the new features in 8.4.
PostgreSQL offers many options in pghba.conf for how users can be authenticated to the database, including the ability to be integrated with existing authentication systems. We will go over the basics and then go through how to integrate PostgreSQL into an existing Kerberos implementation (including MIT Kerberos, Microsoft Active Directory, both, and the pitfalls) and how to use an LDAP directory for authentication. We will also cover changes to the options in postgresql.conf and pghba.conf which are in 8.4, including the new ability to map Kerberos principals to PostgreSQL users and cross-realm support.
PostgreSQL also has a robust Roles system for user and group management. We will go through some of the complexities involved in using roles (and how you can get yourself into trouble with them) as well as best practices for administrators and application developers. Stored Procedures using Security Definer will also be covered, what they are, and what to be careful of when using them. Also in this talk, we will cover the distinction between the actions an Owner can perform and those which can be granted to others, and how you can have multiple Owners of a single object.
PostgreSQL has recently gone through some changes to the privilege/GRANT system, especially with 8.4. The whole gambit will be covered in this discussion, including database, schema, table, and the new-in-8.4 column-level privileges. We'll go over the default permissions, the privileges which can be GRANT'd and REVOKE'd (including the new TRUNCATE permission and why it's a seperate and distinct permission), and the implications and side-effects of certain commands. We will also discuss what can, and can't, be done with column-level permissions including what happens in JOINs and partial INSERTs.