I try to answer your question - to be correct, I try to answer the question I think you've asked (see the comments). Until then I'll assume that a virtual table is simply a table and you want to count the tables a user has SELECT
privilege on.
First, you have to collect all roles which the given user belongs to. This will include the user itself, all roles that are granted to it, all roles that are granted to the roles granted to your user etc., and finally, but usually most importantly, the PUBLIC role.
You can collect these from the information_schema.applicable_roles
view with a query like this:
WITH RECURSIVE privs(grantee, role_name) AS (
SELECT grantee, role_name
FROM information_schema.applicable_roles
WHERE grantee = 'my_user'
UNION ALL
SELECT ar.grantee, ar.role_name
FROM
information_schema.applicable_roles ar
JOIN privs ON ar.grantee = privs.role_name
)
SELECT role_name FROM privs
UNION
SELECT 'my_user'
UNION
SELECT 'PUBLIC'
Then you have to consult the information_schema.table_privileges
view. In order to see all the rows belonging to all grantees, you have to be logged in as a superuser. With an ordinary user you will see only the roles granted to your user (including indirectly granted roles, ie. if the grants are user -> user1 -> user2
, you will see user -> user1
and user1 -> user2
if logged in as user
, and only user1 -> user2
if logged in as user1
.)
Here you can look for tables with SELECT
privilege like
SELECT table_name
FROM information_schema.table_privileges
WHERE
privilege_type = 'SELECT'
AND grantee IN (... the recursive query above goes here ...)
;
Turning this into a count is left as an exercise for the reader :)
When you create a new database, any role is allowed to create objects in the public
schema. To remove this possibility, you may issue immediately after the database creation:
REVOKE ALL ON schema public FROM public;
Edit: after the above command, only a superuser may create new objects inside the public
schema, which is not practical. Assuming a non-superuser foo_user
should be granted this privilege, this should be done with:
GRANT ALL ON schema public TO foo_user;
To know what ALL
means for a schema, we must refer to GRANT in the doc, (in PG 9.2 there are no less than 14 forms of GRANT statements that apply to different things...). It appears that for a schema it means CREATE
and USAGE
.
On the other hand, GRANT ALL PRIVILEGES ON DATABASE...
will grant CONNECT
and CREATE
and TEMP
, but CREATE
in this context relates to schemas, not permanent tables.
Regarding this error: ERROR: no schema has been selected to create in
, it happens when trying to create an object without schema qualification (as in create table foo(...)
) while lacking the permission to create it in any schema of the search_path
.
Best Answer
TL;DR: New users can create tables in the
public
schema because people complained that it was too hard when they couldn't.If you dislike the defaults, you should probably create a new template database with the initial configuration that you want. For example, you might:
or
in your template.
If you wish the
public
user to have no rights on a database, you should additionally:so that the
public
user cannot create schemas or use temp tables.Personally, if I was designing this, I'd give users the
TEMP
right on the database by default, but notCREATE
(schemas in database) orCREATE
(tables in thepublic
schema). I'd reserve those for the owner.They're choices that were made a long time ago, though, and it's pretty hard to change them now.
As it is, there are regular complaints that it's too hard to get started with PostgreSQL because you have to create a user account and often want to create a database too. Why don't we just auto-create them and default to 'trust' as the auth-mode to make it easy? Why doesn't the
postgres
user default to having the passwordpostgres
? Why don't we just auto-create users if they exist in the OS? etc.There are some genuine usability problems for new users - in particular, most people have no idea what
peer
auth is, or why just runningpsql
after installing PostgreSQL tells them there's no user by the name they're logged in as.It's also messy that
pg_hba.conf
is a config file, but users are created at the SQL level. This split confuses users.Lots of things, though, are compromises between secure defaults and easy defaults where the project isn't ever going to make everybody happy.