Postgresql syntax

From wikinotes

Outside of users, permissions, and database-specific things, postgresql uses normal SQL.

Comments

/*  a
 *  multiline
 * comment
 */

-- an inline comment

Choose Database

\c <database>  # connect to a different database

Users

CREATE USER testuser PASSWORD 'mypassword';   # create user
ALTER USER testuser PASSWORD 'mypassword';    # change pass
DROP   USER testuser;                         # delete user <testuser>
SELECT * FROM pg_user;                        # show all users
SELECT user;                                  # show currently logged in user

Permissions

Postgres allows you to assign privileges to users, or roles (users can be added to roles).

Create Users/Roles

# create a user with superuser permissions
CREATE USER root PASSWORD 'test' SUPERUSER;

Remove Permissions

# remove all perms
REVOKE ALL ON DATABASE example_db FROM example_user;

Table Permissions

GRANT SELECT, INSERT, UPDATE, DELETE    # or: ALL PRIVILEGES
  ON ALL TABLES 
  IN SCHEMA public 
  TO user;

ALTER DEFAULT PRIVILEGES 
  IN SCHEMA public 
  GRANT SELECT, INSERT, UPDATE, DELETE  # or: ALL PRIVILEGES
  ON TABLES 
  TO example_user;

Sequence Permissions


# grant all on sequences in public schema to user/role
#
# (first-psql:   query builds several GRANT statements)
# (second psql:  executes GRANT statements)
echo $'SELECT \'GRANT ALL ON \'||quote_ident(schemaname)||\'.\'||quote_ident(relname)||\' TO youruser;\' \
  FROM pg_statio_all_sequences \
  WHERE schemaname = \'public\'' \
  | psql -U postgres clientdb -t
  | psql -U postgres clientdb

These commands, while valid, did not work for me.

GRANT USAGE, SELECT, UPDATE 
  ON ALL SEQUENCES 
  IN SCHEMA public 
  TO user;

ALTER DEFAULT PRIVILEGES
  IN SCHEMA PUBLIC
  GRANT USAGE, SELECT, UPDATE
  ON SEQUENCES
  TO tmarestapi;

Checking User Permissions


# login as user whose permissions you are checking
\z <table-or-seq-name>

# Access priviliges will list __GRANTed__ privileges, but 
# *NOT* implied table-owner privileges

#
#                                      Access privileges
#  Schema |     Name      |   Type   |    Access privileges    | Column privileges | Policies
# --------+---------------+----------+-------------------------+-------------------+----------
#  public | domain_id_seq | sequence | postgres=rwU/postgres  +|                   |
#         |               |          | tmarestapi=rwU/postgres |                   |
# (1 row)
# table permissions only (not sequences)
SELECT *
  FROM information_schema.role_table_grants
  WHERE grantee = 'willp'  # user/role name
Docs
grant docs https://www.postgresql.org/docs/9.3/sql-grant.html
Links
tut on permissions/traps https://marcyes.com/2016/0922-messing-with-postgresql-users-and-permissions/
digitalocean tut https://www.digitalocean.com/docs/databases/postgresql/how-to/modify-user-privileges/

Version

SELECT version();

Schemas

A schema represents one node in a database cluster. A schema can contain several databases (which can contain multiple tables).

SELECT schema_name FROM information_schema.schemata;   # list all schemas
\dn+  # list all schemas and permissions

Databases

SELECT current_database();  ## get current database
SELECT * FROM pg_database;  ## list all databases
\l                          ## list all databases

Tables

# show tables
SELECT table_name 
  FROM information_schema.tables 
  WHERE table_schema = 'public';  

# create table
CREATE TABLE mytable (id INT, value TEXT);

# add column to existing table
ALTER TABLE table_name 
  ADD COLUMN mycolumn TEXT
  NOT NULL 
  UNIQUE;

# modify column
ALTER TABLE table_name
  ALTER COLUMN column_name
  SET NOT NULL;
ALTER TABLE table_name
  ADD UNIQUE (column_name);

\d      # list tables/sequences in current database
\dt     # list tables in current database
\dt *.  # list tables in all databases

Sequences

A sequence is created automatically for every table in the database. Sequence tables are single-row sequence-number generators.

They share the same name as the table, with the suffix _id_seq.

# list sequences acessible to current user
SELECT * FROM information_schema.sequences;
Docs
sequence docs https://www.postgresql.org/docs/9.3/sql-createsequence.html