Learning Postgres

Learning how to configure, use and backup Postgres databases.

Learning Postgres#

At the moment this is less an article and more of a dumping zone for tidbits I would like to include in an article… And since I published it from the start of my website, I’ll leave it here and let it grow.

Note: Any name or variable that need to be provided is wrapped in curly brackets, example: {user_name}

psql#

  • \x show expanded rows
  • \c {database_name} connect to database named database_name
  • \l list databases
  • \dt list tables
  • \du list users and access privileges
  • \dp list sequences and access privileges
  • \dn+ list schemas and access privileges
  • \s client command history

SQL Hints#

  • Use single quotes for strings WHERE "callSign" = 'hooky'
  • Use double quotes around column names "User"."callSign"
-- Delete complete data from an existing table
TRUNCATE TABLE {table_name};
-- Update Roles to Racer and Super Admin
update "public"."User" set roles = 65664 where "User"."callSign" = 'hooky';

Users, Roles, and Privileges#

Create new user#

CREATE USER {user_name} WITH PASSWORD {password};

Grant Privileges to Database#

References#

“Granting access to all tables for a user”
“ERROR: permission denied for sequence…”

Reminder: Switch to the database on which you want to grant privileges.

Example#
-- Grant connect to the database
GRANT CONNECT ON DATABASE {database_name} TO {user_name};

-- Grant to a schema
GRANT USAGE ON SCHEMA {schema_name} TO {user_name};

-- Grant all tables
GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA {schema_name} TO {user_name};

-- !Important
-- Grant permission to sequences
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO {user_name};

-- !Important
-- Grant permission on future sequences
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO {user_name};

Login in to psql as a different user to a specific database.#

psql: FATAL: database “<user>” does not exist

psql -U {user_name} {database_name}

List Privileges on a Database#

References#

List the database privileges using psql

SELECT grantee, privilege_type, table_name FROM information_schema.role_table_grants
WHERE NOT (grantee='postgres' OR grantee='PUBLIC');

Backup#

pg_dump dbname > infile

Restoring#

Databases are not automatically created when restoring from a dump. Before restoring with psql create the new database from template0. Also all users who own own or have permissions on objects in the dumped database must already exist before restoring. Otherwise it will not be restored with the same ownerships or permissions.

createdb -T template0 dbname
psql dbname < infile

References#