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:
Search WWH ::




Custom Search