I'm new at creating databases, and this error has me dumbfounded, as I am super new with DB admin things (I mostly do reporting type queries). I created a new database through pgAdmin3 GUI, and I'm trying to create DB objects in there using SQL but am getting a:
ERROR: relation "replays" does not exist SQL state: 42P01
I looked through the manual but did not find anything very helpful, though I suspect it may have to do with search_path
somehow. Here is a screenshot. Any idea what I'm doing wrong please?
Best Answer
What you had originally was a correct syntax - for tables, not for schemas. As you did not have a table (dubbed 'relation' in the error message), it threw the not-found error.
I see you've already noticed this - I believe there is no better way of learning than to fix our own mistakes ;)
But there is something more. What you are doing above is too much on one hand, and not enough on the other.
Running the script, you
SELECT
on all tables in the schema created in (1.) to this new role_CREATE
andUSAGE
) on the new schema to the new roleThe problem lies within point (3.) You granted privileges on tables in
replays
- but there are no tables in there! There might be some in the future, but at this point the schema is completely empty. This way, theGRANT
in (3.) does nothing - this way you are doing too much.But what about the future tables?
There is a command for covering them:
ALTER DEFAULT PRIVILEGES
. It applies not only to tables, but:There is one important limitation, too:
This means that a table created by
alice
, who is neither you nor a role than you are a member of (can be checked, for example, by using\du
inpsql
), will not take the prescribed access rights. The optionalFOR ROLE
clause is used for specifying the 'table creator' role you are a member of. In many cases, this implies it is a good idea to create all database objects using the same role - likemydatabase_owner
.A small example to show this at work:
As you can see,
alice
has no explicit rights on the latter table. (In this case, she can stillSELECT
from the table, being a member of thepublic
pseudorole, but I didn't want to clutter the example by revoking the rights frompublic
.)