My PostgreSQL Cheat Sheet

My PostgreSQL Cheat Sheet

1. Show tables

The command \dt stands for describe tables and shows all tables in the database, equivalent to show tables in MySQL.

\dt

2. Create user

Run the following SQL command to create a user. Replace and 'password' with the desired username and password for the new user:

CREATE ROLE <username> WITH LOGIN PASSWORD 'mySecurePassword';

Grant specific privileges to this user.

GRANT ALL PRIVILEGES ON SCHEMA schema_name TO <username>;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA schema_name TO <username>;
GRANT ALL PRIVILEGES ON SCHEMA public TO <username>;
GRANT USAGE,SELECT,UPDATE ON SEQUENCE <db_sequence> TO <username>;

Don't forget to save your changes using the COMMIT; command if you're not in auto-commit mode.

3. Show all users

\du stands for describe users and shows all users in the database.

\du
SELECT usename FROM pg_user;

4. Show all privileges of a specific user

Note: The '' are needed for the username.

SELECT table_schema, table_name, privilege_type 
FROM information_schema.table_privileges 
WHERE grantee = 'username';