In postgresql, what's the difference a "database" and a "relation"? ('error relation x does not exist', 'error database x already exists')

Question

I see the juxtaposition of these two errors and, given the dearth of Google search results, had to ask. What is the difference and what do I need to be doing here?

deploy=# GRANT SELECT ON angel_research_production TO angel_research;
ERROR:  relation "angel_research_production" does not exist
deploy=# create database angel_research_production;
ERROR:  database "angel_research_production" already exists

My guess is that I need to be doing this grant select business from some other user...

So I run this on postgres (dbroot) and get this:

postgres=# GRANT SELECT ON angel_research_production TO angel_research;
ERROR:  relation "angel_research_production" does not exist

So it does exist as a database, but not as a relation. How might I rectify this and what are the underlying issues here? I'm a little overwhelmed. Thanks

Answer 1

My guess is that you really want to recursively GRANT the SELECT right to every relation (table and view) within the database angel_research_production. Correct?

How to grant on all tables in a database

If so, in PostgreSQL 9.0 and above you have:

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

from the manual for GRANT. Note the ALL TABLES IN SCHEMA clause. Usage:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO angel_research;

If all your user-defined objects are in the public schema (see below) that'll do the trick.

In prior versions there is no such feature, but user defined functions exist as workarounds.

Pg 9.0 also has ALTER DEFAULT PRIVILEGES, which changes the default privileges assigned to newly created objects. It does not affect existing objects.

What does the error message mean?

As noted by TokenMacGuy, a relation is a table or view, not a database.

GRANT SELECT ON angel_research_production TO angel_research;

can be thought of as shorthand for:

GRANT SELECT ON TABLE angel_research_production TO angel_research
                ^^^^^

and that table(relation) doesn't exist, so you're getting the error reported above.

In the manual for GRANT or the psql \h GRANT output you'll see:

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
    ON DATABASE database_name [, ...]
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

This shows that the privileges you can GRANT to a database are CREATE, CONNECT and TEMPORARY. There is no SELECT right on a database.

Relations? Schema? Huh?

There are four levels of organisation in Pg:

• Cluster - controlled by the postmaster, accepts connections on a given IP/port combo, contains one or more databases including the built-in template0, template1 and postgres databases. Controlled by postgresql.conf and pg_hba.conf. Your DB cluster is often created for you by an installer or package. Not to be confused with the normal meaning of cluster as a compute cluster or the general english language meaning.

• Database - contains one or more schemata or schemas. You connect to a specific database when connecting to Pg.

• Schema - contains objects including relations. If you don't specify otherwise, anything user-created goes into the public schema. Queries can reference objects in multiple schema explicitly or, via search_path, implicitly.

• Objects - Somewhat PostgreSQL specific, anything (including a relation) that exists in a schema.

  • Relations - Things that look and behave like tables, like views and tables

  • Other objects also reside in schemas, like functions, casts, indexes, sequences, operators, aggregates, etc.