Database Reference
In-Depth Information
database asset. For example,
TRUNCATE
for functions and
EXECUTE
for tables make no
sense.
Getting Started
So, you successfully installed PostgreSQL; you should have one superuser, whose pass‐
word you know by heart. Now you should take the following additional steps to set up
additional roles and assign privileges:
1. PostgreSQL creates one superuser and one database for you at installation, both
named
postgres
. Log into your server as
postgres
.
2. Before creating your first database, create a role that will own the database and can
log in, such as:
CREATE
ROLE
mydb_admin
LOGIN
PASSWORD
'
something
'
;
3. Create the database and set the owner:
CREATE
DATABASE
mydb
WITH
owner
=
mydb_admin
;
4. Now log in as the
mydb_admin
user and start setting up additional schemas and
tables.
GRANT
The
GRANT
command assigns privileges to others. The basic usage is:
GRANT
some_privilege
TO
some_role
;
A few things to keep in mind when it comes to
GRANT
:
• You need to be the holder of the privilege that you're granting and you must have
grant privilege yourself. You can't give away what you don't have.
• Some privileges always remain with the owner of an object and can never be granted
away. These include
DROP
and
ALTER
.
• The owner of an object already has all privileges. Granting an owner privilege in
what it already owns is unnecessary.
• When granting privileges, you can add
WITH GRANT OPTION
. This means that the
grantee can grant onwards:
GRANT
ALL
ON
ALL
TABLES
IN
SCHEMA
public
TO
mydb_admin
WITH
GRANT
OPTION
;
• To grant all relevant privileges on an object use
ALL
instead of the specific privilege:
GRANT
SELECT
,
REFERENCES
,
TRIGGER
ON
ALL
TABLES
IN
SCHEMA
my_schema
TO
PUB
LIC
;
• The
ALL
alias can also be used to grant for all objects within a database or schema: