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 clientdbThese 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