Only the owner (and superusers) can drop objects. Per documentation:
The right to drop an object, or to alter its definition in any way, is
not treated as a grantable privilege; it is inherent in the owner, and
cannot be granted or revoked. (However, a similar effect can be
obtained by granting or revoking membership in the role that owns the
object; see below.) The owner implicitly has all grant options for the
object, too.
So, make administrator
own such objects that users should be able to drop.
ALTER FUNCTION foo() OWNER TO administrator;
ALTER TABLE foo OWNER TO administrator;
And you remembered to actually grant group membership, right?
GRANT administrator TO _administrator;
The backslash commands in psql are shortcuts for a query or queries that look through the system catalogs. The \l
command looks at information in pg_catalog.pg_database
, specifically, this query:
SELECT d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
d.datcollate as "Collate",
d.datctype as "Ctype",
pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
You can make psql
show what it is using for the backslash commands by passing the -E
flag to it when you invoke it on the command line.
If the permissions on a database or other object are the defaults that PostgreSQL creates them with, the *acl
column will be NULL
. If you change the defaults, as you have, the ACL column will be populated with information related to the GRANT
and/or REVOKE
statements you have ran.
You can see the permissions/ACLs specifically via either \z
or \dp
If you read further here:
http://www.postgresql.org/docs/9.4/static/sql-grant.html
If you scroll down, (or search for the word psql
), you can look at the table that shows you how to interpret the ACLs that you see with \l
or in an ACL column.
For example:
=Tc/vagrant
means that PUBLIC (the implicit role that contains all roles) has permissions to create temporary tables T
and connect c
, because the ACL line =xxxxx
denotes permissions applied to PUBLIC, while rolname=xxxx
applies to that specific role.
This presentation from Dalibo should also help clarify this further: Managing Rights in PostgreSQL
Hope that helps. =)
Best Answer
Sequences are separate objects with separate privileges.
Indexes belong to the table. To
DROP
orCREATE
an index you must be the owner of the table (or superuser).Columns go with table privileges. Granting privileges on the table grants applicable privileges to all columns automatically.
For DDL commands (
ALTER TABLE
...) you must own the table again.Since PostgreSQL 9.1 there are column level DML-grants additionally. But:
More details in the manual about
GRANT
, where the quotes are from.