Psql: Difference between revisions

From annawiki
No edit summary
 
(19 intermediate revisions by the same user not shown)
Line 1: Line 1:
==Connection==
tobiasco@main:/root$ psql
psql: FATAL:  database "tobiasco" does not exist
Who asked or claimed that?
tobiasco@main:/root$ psql postgres
psql (9.3.1)
Type "help" for help.
postgres=>
postgres=> \c tidb_all
You are now connected to database "tidb_all" as user "tobiasco".
tidb_all=>
tidb_all=>
==Create database==
==Create database==
  tobiasco@main:/root$ psql CREATE DATABASE test2;
  tobiasco@main:/root$ psql CREATE DATABASE test2;
  psql: warning: extra command-line argument "test2" ignored
  psql: warning: extra command-line argument "test2" ignored
psql: FATAL:  Peer authentication failed for user "DATABASE"
Try without test2
tobiasco@main:/root$ psql CREATE DATABASE;
  psql: FATAL:  Peer authentication failed for user "DATABASE"
  psql: FATAL:  Peer authentication failed for user "DATABASE"


Line 14: Line 32:
  test->
  test->


==List roles==
Try with semicolon:
test=> CREATE TABLE testtable;
ERROR:  syntax error at or near ";"
LINE 1: CREATE TABLE testtable;
 
==Create DB and CREATEDB==
  test-> \dg
  test-> \dg
                               List of roles
                               List of roles
Line 25: Line 48:
But http://www.postgresql.org/docs/9.3/static/sql-createdatabase.html says
But http://www.postgresql.org/docs/9.3/static/sql-createdatabase.html says
  "To create a database, you must be a superuser or have the special CREATEDB privilege."
  "To create a database, you must be a superuser or have the special CREATEDB privilege."
Where to find "privilege"-listing?
Where to find out what "privilege" one has? 
* The word "privilege" is not listed at http://www.postgresql.org/docs/9.3/static/index.html
* http://www.postgresql.org/search/?u=%2Fdocs%2F9.3%2F&q=privilege - the search result listing comes with little texts, the first contains " See Section 5.6 for more information about privileges."
* Clicking that result leads to http://www.postgresql.org/docs/9.3/static/functions-info.html, the first result for in-page search repeats the above sentence, with hyperlink to the http://www.postgresql.org/docs/9.3/static/ddl-priv.html
* On that page it says 'PostgreSQL 9.3.1 Documentation [...] 5.6. Privileges', the word section is only found in " The following sections and chapters will also show you how those privileges are used.". So maybe "5.6" here is the "Section 5.6"
There are different kinds of privileges: SELECT, INSERT, UPDATE,
DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, and USAGE.
No "Create DB" nor "CREATEDB", but it is not claimed that this list is complete.
For complete information on the different types of privileges
supported by PostgreSQL, refer to the GRANT reference page.
Refer to? It would be nice to have a complete list of all "different types of privileges supported by PostgreSQL". Anyway, look at http://www.postgresql.org/docs/9.3/static/sql-grant.html
* "CREATEDB" not found on that page
* Try http://www.postgresql.org/search/?u=%2Fdocs%2F9.3%2F&q=CREATEDB
* first result links to http://www.postgresql.org/docs/9.3/static/app-createdb.html
createdb -- create a new PostgreSQL database
[...]
createdb creates a new PostgreSQL database.
[...]
createdb is a wrapper around the SQL command CREATE DATABASE.
So, that is not the privilege "CREATEDB" here, but a wrapper? No interest in wrapper.
 
* second result links to http://www.postgresql.org/docs/9.3/static/tutorial-createdb.html - no interest in tutorial. Wanted: documentation. Update: By accident found that the Tutorial is part of the documentation.

Latest revision as of 2013-11-21T13:20:22

Connection

tobiasco@main:/root$ psql
psql: FATAL:  database "tobiasco" does not exist

Who asked or claimed that?

tobiasco@main:/root$ psql postgres
psql (9.3.1)
Type "help" for help.

postgres=>
postgres=> \c tidb_all
You are now connected to database "tidb_all" as user "tobiasco".
tidb_all=>
tidb_all=>

Create database

tobiasco@main:/root$ psql CREATE DATABASE test2;
psql: warning: extra command-line argument "test2" ignored
psql: FATAL:  Peer authentication failed for user "DATABASE"

Try without test2

tobiasco@main:/root$ psql CREATE DATABASE;
psql: FATAL:  Peer authentication failed for user "DATABASE"

Create table

tobiasco@main:/root$ psql test
psql (9.3.1)
Type "help" for help.
 
test=> CREATE TABLE testtable
test-> \dt test
No matching relations found.
test->

Try with semicolon:

test=> CREATE TABLE testtable;
ERROR:  syntax error at or near ";"
LINE 1: CREATE TABLE testtable;

Create DB and CREATEDB

test-> \dg
                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 gis       |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 tobiasco  | Create DB                                      | {}

But http://www.postgresql.org/docs/9.3/static/sql-createdatabase.html says

"To create a database, you must be a superuser or have the special CREATEDB privilege."

Where to find out what "privilege" one has?

There are different kinds of privileges: SELECT, INSERT, UPDATE, 
DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, and USAGE.

No "Create DB" nor "CREATEDB", but it is not claimed that this list is complete.

For complete information on the different types of privileges 
supported by PostgreSQL, refer to the GRANT reference page.

Refer to? It would be nice to have a complete list of all "different types of privileges supported by PostgreSQL". Anyway, look at http://www.postgresql.org/docs/9.3/static/sql-grant.html

createdb -- create a new PostgreSQL database
[...]
createdb creates a new PostgreSQL database.
[...]
createdb is a wrapper around the SQL command CREATE DATABASE.

So, that is not the privilege "CREATEDB" here, but a wrapper? No interest in wrapper.