Database Reference
In-Depth Information
Default search path
It is always a good practice to use a fully qualified name when revoking or granting rights, as
you may otherwise inadvertently be working with the wrong table.
To see effective database path, run the following:
pguser=# show search_path ;
search_path
----------------
"$user",public
(1 row)
To see which table would be affected if you omit the schema name, run the following in psql:
pguser=# \d x
Table "public.x"
Column | Type | Modifiers
--------+------+-----------
The table name
public.x
in the response contains the full name including the schema.
Granting user access to a table
A user needs to have access to a table in order to perform any action on it. Although the default
behavior of PostgreSQL database is to give full access to all users through role
PUBLIC
, a
security-conscious database setup revokes rights from
PUBLIC
after table creation.
Getting ready
Make sure that you have appropriate "roles" defined, and that privileges are "revoked"
from role
PUBLIC
.
How to do it...
Grant access to schema containing the table to:
GRANT ALL ON someschema TO somerole;
GRANT SELECT, INSERT, UPDATE, DELETE ON someschema.sometable TO
somegroup;
GRANT somerole TO someuser, otheruser;
