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';