Database Reference
In-Depth Information
server. If they all swarm into the
public
schema, it gets cluttered. For example, the entire
PostGIS suite of extensions will together add more than a thousand functions. If you've
already created a few tables and functions of your own in the
public
schema, imagine
how frustrating it would be to scan a list of tables and functions trying to find your own
among the thousands.
To create some useful structure, before you install any extensions, create a new schema:
CREATE
SCHEMA
my_extensions
;
Then add your new schema to the search path:
ALTER
DATABASE
mydb
SET
search_path
=
'
"$user"
,
public
,
my_extensions
'
;
When you install extensions, be sure to indicate your new schema as their new home.
The
SET search_path
change will not take effect for existing con‐
nections. You'll need to reconnect to experience the change.
Privileges
Privileges (often called permissions) can be tricky to administer in PostgreSQL because
of the fine granular control at your disposal. Security can bore down to the object level.
You could assign different privileges to each column of your table, if that ever becomes
necessary. Teaching you all there's to know about privileges could take a few chapters.
What we'll aim for in this section instead is to give you enough information to get up
and running and to guide you around some of the more nonintuitive land mines that
could either lock you out completely or expose your server inappropriately.
See
Privileges
for an overview of privileges.
Privilege management in PostgreSQL is no cakewalk. The pgAdmin graphical admin‐
istration tool can ease some of the tasks or, at the very least, paint you a picture of your
privilege settings. You can accomplish most, if not all, of your privilege assignment tasks
in pgAdmin. If you're saddled with the task of administering privileges and are new to
PostgreSQL, start with pgAdmin. Jump to
“Creating Database Assets and Setting Priv‐
ileges” on page 62
if you can't wait.
Types of Privileges
Some of the object-level privileges you find in PostgreSQL are
SELECT
,
INSERT
,
UP
DATE
,
ALTER
,
EXECUTE
,
TRUNCATE
, and a qualifier to those called
WITH GRANT
. You can
infer the privilege from the name alone with the exception of
GRANT
, which we cover in
“GRANT” on page 30
. Note that privileges are relevant only with respect to a particular
