Database Reference
In-Depth Information
GRANT
SELECT
,
UPDATE
ON
ALL
SEQUENCES
IN
SCHEMA
my_schema
TO
PUBLIC
;
• To grant privileges to all roles, you can use the alias
PUBLIC
:
GRANT
USAGE
ON
SCHEMA
my_schema
TO
PUBLIC
;
that you take the time to study the few pages before you inadvertently knock a big hole
in your security wall.
Some privileges are by default granted to
PUBLIC
. These are
CONNECT
and
CREATE TEMP
TABLE
for databases,
EXECUTE
for functions, and
USAGE
for languages. In many cases you
might consider revoking some of defaults for your own safety. Use the
REVOKE
command:
REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA
my_schema
FROM PUBLIC;
Default Privileges
PostgreSQL 9.0 introduced
default privileges
, which allow users to set privileges on all
database assets within a particular schema or database, as well as in advance of their
creation. This will ease your management of privileges, provided you keep default priv‐
ileges up to date.
Let's suppose we want all users of our database to have
EXECUTE
and
SELECT
access to
all future tables and functions in a schema. We can define privileges as shown in
Example 2-6
.
Example 2-6. Defining default privileges on a schema
GRANT
USAGE
ON
SCHEMA
my_schema
TO
PUBLIC
;
ALTER
DEFAULT
PRIVILEGES
IN
SCHEMA
my_schema
GRANT
SELECT
,
REFERENCES
ON
TABLES
TO
PUBLIC
;
ALTER
DEFAULT
PRIVILEGES
IN
SCHEMA
my_schema
GRANT
ALL
ON
TABLES
TO
mydb_admin
WITH
GRANT
OPTION
;
ALTER
DEFAULT
PRIVILEGES
IN
SCHEMA
my_schema
GRANT
SELECT
,
UPDATE
ON
SEQUENCES
TO
public
;
ALTER
DEFAULT
PRIVILEGES
IN
SCHEMA
my_schema
GRANT
ALL
ON
FUNCTIONS
TO
mydb_admin
WITH
GRANT
OPTION
;
ALTER
DEFAULT
PRIVILEGES
IN
SCHEMA
my_schema
GRANT
USAGE
ON
TYPES
TO
PUBLIC
;
Adding or changing default privileges won't affect current privilege
settings.