A new database is created by cloning an existing one. If you don't specify which one to clone, PostgreSQL clones the hidden-by-default database template1
. All objects in template1
are owned by user postgres
.
So you're not the owner of any of the objects in the database at the time it's cloned from template0
or template1
; you only own the database its self.
New objects are created with your ownership because you created them. Existing objects retain their existing ownership.
See \dn *
in psql
to see the namespaces defined, \dp *.*
to see table/view/sequence privileges, \df+ *
to see function definitions with owners, \dT *
to see data types, etc. You'll see that in a new blank DB they're all owned by user postgres
.
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
PostgreSQL isn't MySQL. You can't drop databases or users without being attached to some database. Based on the error message, the database you are attached to in order to execute the DROP USER statement is the one whose public schema has privileges granted to the doomed user.
Schema grants are not attributes of a role, so don't show up in
\du
. To see them, you would use\dn+
.