nordicpgday 2014 stockholm, sweden intro to postgresql...
TRANSCRIPT
![Page 1: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/1.jpg)
Intro to PostgreSQL Security
NordicPGDay 2014Stockholm, Sweden
Stephen [email protected]
Resonate, Inc. • Digital Media • PostgreSQL • Hadoop • [email protected] • http://www.resonateinsights.com
![Page 2: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/2.jpg)
Stephen Frost•PostgreSQL
•Major Contributor, Committer•Implemented Roles in 8.3•Column-Level Privileges in 8.4•Contributions to PL/pgSQL, PostGIS
•Resonate, Inc.•Principal Database Engineer•Online Digital Media Company•We're Hiring! - [email protected]
![Page 3: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/3.jpg)
Do you read...•planet.postgresql.org
![Page 4: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/4.jpg)
Security in PostgreSQL•Role system
•Role-level Privileges•Authentication
![Page 5: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/5.jpg)
Security in PostgreSQL•Authorization
•Containers•GRANT / REVOKE•Defaults
![Page 6: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/6.jpg)
Security in PostgreSQL•Use-cases
•Web-based•Enterprise DB / DW
![Page 7: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/7.jpg)
Roles•Identities inside PostgreSQL•Each connection is assiged specific role•Roles encompass both users and groups•Nearly all objects are "owned" by a specific role•Shared across entire cluster (not per-DB)
![Page 8: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/8.jpg)
Roles•Objects in PG with owners:* Databases * Schemas* Tables (Local and Foreign) * Functions* Aggregates * Collations* Conversions * Domains* Event Triggers * Foreign Data Wrappers* Languages * Large Objects* Sequences * Foreign Servers* Tablespaces * Types* Views (Normal and Materialized)* Operators (and Classes and Families)* Text Search Configuration and Dictionaries
![Page 9: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/9.jpg)
Role Membership•Roles can be members of other roles•GRANT used to add a role to another role•Loops are forbidden•WITH ADMIN allows the role to grant the role
![Page 10: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/10.jpg)
Role Membership•inherit / noinherit
•inherit - privileges (not attributes) automatic•noinherit - "SET ROLE ..." required•Great for sudo-like DB administration•Create "barrier" role- eg: "admin", with noinherit•Grant "admin" to, uh, admins, postgres to "admin"
•Supports traditional "User/Group", and then some
![Page 11: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/11.jpg)
Changing Roles•"SET ROLE" SQL command
•Allows gaining "noinherit" privileges•Can be used to drop privileges too•DISCARD ALL; will reset role too•"$user" in search_path follows SET ROLE
![Page 12: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/12.jpg)
Changing Roles•Security Definer Functions run as owner
•Need to be careful with search_path•Strongly recommend against superuser owned
•Views also run as owner•Need to mark view 'security_barrier'
![Page 13: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/13.jpg)
Role Privileges•SUPERUSER
•Bypass ALL security (and some sanity..) checks•Use very sparingly•Never login to SUPERUSER role directly•Require "SET ROLE postgres;" to be superuser
![Page 14: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/14.jpg)
Role Privileges•What's wrong with SUPERUSER?=# delete from pg_database;DELETE 3
![Page 15: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/15.jpg)
Role Privileges•What's wrong with SUPERUSER?=# delete from pg_class;DELETE 295
![Page 16: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/16.jpg)
Role Privileges•What's wrong with SUPERUSER?=# COPY pg_class TO '/home/sfrost/pg/src/clean/install/data/postmaster.conf' WITH CSV;COPY 295
![Page 17: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/17.jpg)
Role Privileges•What's wrong with SUPERUSER?=# COPY pg_class TO PROGRAM 'cat > postgresql.conf';COPY 295
![Page 18: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/18.jpg)
Role Privileges•CREATEDB
•Allows creating new databases•Give out sparingly- DBs are not free•User becomes database owner
![Page 19: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/19.jpg)
Role Privileges•CREATEROLE
•Allows creating new roles•ALSO allows modifying EXISTING roles•Can add CREATEDB to roles, et al•Non-superuser can't modify superuser•Use with caution
![Page 20: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/20.jpg)
Role Privileges•REPLICATION
•Use can connect to "replication" database•Only grant to dedicated replication accounts•Can read every file in the cluster
![Page 21: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/21.jpg)
Role Privileges•LOGIN
•Role is allowed to connect to PG•Roles with LOGIN will show up in "pg_user"•Roles with NOLOGIN will show up in "pg_group"
![Page 22: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/22.jpg)
Role Privileges•CONNECTION LIMIT
•Concurrent connection limit•Changing this will impact existing connections
![Page 23: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/23.jpg)
Role Privileges•VALID UNTIL
•Can't connect after this time•Does not impact existing connections
![Page 24: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/24.jpg)
Authentication•Connection parameters
•Database•PostgreSQL Role•Client IP / Unix Socket•SSL vs. non-SSL
![Page 25: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/25.jpg)
Authentication•Based on parameters, auth method is chosen•Auth method can provide "system" username•System username can be mapped to PG role
![Page 26: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/26.jpg)
pg_hba.conf•Processed top-to-bottom, first match wins•"User" can be "+role" to mean "member of role"•Database can be "all", "replication", "sameuser"# TYPE DATABASE USER ADDRESS METHOD# "local" is for Unix domain socket connections onlylocal all all peer map=unixmap# IPv4 local connections:host all all 127.0.0.1/32 md5# IPv6 local connections:host all all ::1/128 md5# Allow replication connections from localhost, by a user with the# replication privilege.#local replication repl_user md5#host replication repl_user 127.0.0.1/32 md5#host replication repl_user ::1/128 md5
![Page 27: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/27.jpg)
pg_ident.conf•Also processed top-to-bottom, by map name•Regexps can be used with "/" and "1"# MAPNAME SYSTEM-USERNAME PG-USERNAMEunixmap root postgresunixmap /^(.*)$ \1localrealm /^([^@]*)@MYREALM\.COM$ \1localrealm [email protected] otherjoeclientcert "cn=Stephen P. Frost" sfrostclientcert "cn=John Doe" jdoe
![Page 28: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/28.jpg)
Auth Methods•peer
•Unix socket based- uses the unix username•punts on the authentication issue to the unix layer•(ident covers this but also identd, do not use)
![Page 29: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/29.jpg)
Auth Methods•gss / sspi / krb5 (krb5 deprecated)
•Kerberos / Active Directory based authentication•Perfect for Enterprise deployments•Supports cross-realm, princ-based identification•SSL required only for data encryption (not authN)•No option for Kerberos/GSS data encryption today
![Page 30: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/30.jpg)
Auth Methods•cert
•Client-side SSL certificates•Useful with OpenSSL support, eg: Smart Cards•SSL required for SSL certificates, of course•Requires full PKI setup, CAs, etc
![Page 31: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/31.jpg)
Auth Methods•md5
•Normal password-based authentication•("password" exists, but PW is sent in the clear)•Should use SSL with this
![Page 32: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/32.jpg)
Auth Methods•radius
•RADIUS servers- relatively rare / special case•Need to use SSL to PG, and RADIUS encryption
•reject•Special case- reject if matched
![Page 33: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/33.jpg)
Auth Methods•ldap
•Allows for simple-bind, or LDAP lookup•Need to use SSL to PG, and TLS with LDAP
•trust•Allows any connection to connect as any user
![Page 34: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/34.jpg)
Authorization•Container objects
•Databases•Schemas
•To access objects inside containers-•Must have CONNECT privs on the database•Must have USAGE privs on the schema
![Page 35: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/35.jpg)
GRANT / REVOKE•GRANT <privs> ON <object> TO <roles>;•REVOKE <privs> ON <object> FROM <roles>;•GRANT ... ON ALL <objtype> IN <schema> ...•"PUBLIC" means "everyone"•WITH GRANT OPTION allows role to re-grant priv
![Page 36: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/36.jpg)
GRANT / REVOKE•Owning the object grants all rights, and then some•Only owner of object can DROP the objectGRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } [, ...] | ALL [ PRIVILEGES ] } ON { [ TABLE ] table_name [, ...] | ALL TABLES IN SCHEMA schema_name [, ...] } TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
...
![Page 37: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/37.jpg)
Database Privileges•CREATE (Able to create schemas)•CONNECT (Granted to PUBLIC by default)•TEMPORARY (Able to create temporary objects)•Owners can use ALTER DATABASE to
•RENAME•OWNER•SET TABLESPACE•SET other config options
![Page 38: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/38.jpg)
Schema Privileges•CREATE (Able to create objects in the schema)•USAGE (Able to see objects- need rights on them)•"public" schema defaults with CREATE to PUBLIC•Owners can use ALTER SCHEMA to
•RENAME•OWNER
![Page 39: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/39.jpg)
Table Privileges•SELECT (SELECT any/all columns)•INSERT (INSERT any/all columns)•UPDATE (UPDATE any/all columns)•DELETE•TRUNCATE (Not the same as DELETE FROM ...)•REFERENCES (Can create a FK to the table)•TRIGGER (Can create a trigger on the table)
![Page 40: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/40.jpg)
Table Privileges•Table owners can use ALTER TABLE to
•ADD/DROP COLUMN•ADD/DROP Constraints•OWNER•CLUSTER•INHERIT / NOINHERIT•Lots of stuff...
![Page 41: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/41.jpg)
Column Privileges•SELECT (Only select out specified column)•INSERT (Can only insert non-default values)•UPDATE (Can only update these columns)•REFERENCES (Can only reference specified column)•Table owners can ALTER TABLE .. ALTER COLUMN to
•SET/DROP DEFAULT expression•SET STATISTICS (target)•SET DATA TYPE•SET STORAGE
![Page 42: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/42.jpg)
Sequence Privileges•USAGE (currval && nextval)•SELECT (Only currval)•UPDATE (nextval && setval / reset sequence)
![Page 43: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/43.jpg)
Function Privileges•EXECUTE•Granted to "PUBLIC" by default!•Use caution with SECURITY DEFINER
![Page 44: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/44.jpg)
Tablespace Privileges•CREATE•User allowed to create objects in tablespace•Any kind of object allowed•Can be temp or non-temp (even if temp tablespace)•Database Default Tablespace
•Skips tablespace priv checking•Only for connections to that DB
![Page 45: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/45.jpg)
Usage Privileges•Objects with just USAGE privs•DOMAIN•FOREIGN DATA WRAPPER•FOREIGN SERVER•LANGUAGE•TYPE
![Page 46: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/46.jpg)
Web-Scale•Roles exist in a PG shared catalog•Common across all DBs•Unable to be partitioned•Could be sharded..
•Unable to set CHECK constraints•No triggers•etc..
•BUT- use roles also
![Page 47: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/47.jpg)
Roles for Web-Scale•Use tables for website users•Use roles for permissions management•Independent roles for ETL, daemon, etc
![Page 48: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/48.jpg)
Roles for Web-Scale•Read-only role
•Only has read access•Useful for scaling out with read slaves
•Read/write role(s)•Possibly more than one (eg: per site)•Minimize access to what code "should" do
![Page 49: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/49.jpg)
Enterprise Deployment•Individual logins per user•Roles for permissions management•Roles to manage access to databases•Kerberos / GSS / AD integration / Pass-thru
![Page 50: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/50.jpg)
Enterprise Deployment•Views
•Limit rows individual users can see•Security Barrier
•PL/PgSQL Functions•Control writes- include auditing•Security Definer
![Page 51: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/51.jpg)
Security Labels•Defines labels for objects in PG•Hooks for security providers (eg: sepgsql)SECURITY LABEL [ FOR provider ] ON{ TABLE object_name | COLUMN table_name.column_name | AGGREGATE aggregate_name ( aggregate_signature ) | DATABASE object_name | DOMAIN object_name | EVENT TRIGGER object_name | FOREIGN TABLE object_name FUNCTION function_name ( [ [ argmode ] [ argname ] argtype [, ...] ] ) | LARGE OBJECT large_object_oid | MATERIALIZED VIEW object_name | [ PROCEDURAL ] LANGUAGE object_name | ROLE object_name | SCHEMA object_name | SEQUENCE object_name | TABLESPACE object_name | TYPE object_name | VIEW object_name} IS 'label'
![Page 52: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/52.jpg)
Additional Security•SELinux Integration
•sepgsql security provider•Works with SECURITY LABEL
•EVENT Triggers•Can prevent certain actions
•Row-Level Security being worked on•Updatable security-barrier views
![Page 53: NordicPGDay 2014 Stockholm, Sweden Intro to PostgreSQL ...snowman.net/slides/pgsecurity-nordicpgday14_20140320.pdf · Security in PostgreSQL •Role system •Role-level Privileges](https://reader034.vdocuments.us/reader034/viewer/2022042412/5f2b043cd2a4094f2165a33e/html5/thumbnails/53.jpg)
Questions?