Database Reference
In-Depth Information
To read more about default privileges, see
ALTER DEFAULT PRIVILEGES
.
Privilege Idiosyncrasies
Before we unleash you to explore privileges on your own, we do want to point out a few
quirks that may not be apparent.
Unlike in other database products, being the owner of a PostgreSQL database does not
give you access to all objects in the database, but it does grant you privileges to whatever
objects you create and allows you to drop the database. Another role can create objects
that you can't access in your owned database. Interestingly, though, you can still drop
the whole database.
People often forget to set
GRANT USAGE ON SCHEMA
or
GRANT ALL ON SCHEMA
. Even if
your tables and functions have rights assigned to a role, these tables and functions will
still not be accessible if the role has no
USAGE
rights to the schema.
Extensions
Extensions, formerly called contribs, are add-ons that you can install in a PostgreSQL
database to extend functionality beyond the base offerings. They exemplify the best of
open source software: people collaborating, building, and freely sharing new features.
Since version 9.1, the new PostgreSQL extension model has made adding extensions a
cinch.
As a note on terminology, older add-ons outside the extension mod‐
el should still be called
contribs
, but with an eye toward the future,
we'll call them all
extensions
.
Not all extensions need to be in all databases. You should install extensions to your
individual database on an as-needed basis. If you want all your databases to have a certain
set of extensions, you can develop a template database, as discussed in
“Template Da‐
tabases” on page 27
, with all the the extensions installed, and then beget future databases
from that template.
Occasionally prune extensions that you no longer need, to avoid bloat. Some extensions
take up quite a bit of space.
To see which extensions you have already installed on your server, run the query in
Example 2-7
. Your list could vary significantly from ours.
