8/15/2017 - 11:36 PM

sql queries in postgres

sql queries in postgres

# Procedures to add new user and grant SELECT on a certain table
create user <username> with password '<password>';
grant connect on database <database-name> to <username>;
grant select on <tablename> to <username>;

# an user tries to check his permission on a specific table
select * from information_schema.role_table_grants where grantee='youruser';
# list all users 
select usename from pg_user;

# whoami
select current_user;

# take off all permission from a specific user on a specific database
revoke all on database <db-name> from <user>;

# revoke and grant connect to database to specifc user
revoke connect on database <db-name> from public;
grant connect on database <db-name> to <specific-user>;

# must revoke `create` on specific user so they can't create tables
revoke create on schema public from <specific-user>;

# view the limit number of connection an user can make to a psql database:
select rolname, rolconnlimit
from pg_roles
where rolconnlimit <> -1;

# to create user with password
create user <username> with password '<password>'

# to drop user, need to revoke all privileges first before drop
drop user <username>

# view all privileges of an user
select *  FROM information_schema.table_privileges where grantee='<user-name>';

# a 'database schema' contains all tables (think about it as namespace to table)
# to list all schemas in a postgres
select schema_name from information_schema.schemata

# check current database I am on
select current_database()

# list all databases in postgres
select datname from pg_database where datistemplate = false;

# list all tables in current database
SELECT table_schema,table_name
FROM information_schema.tables
ORDER BY table_schema,table_name;

# check capacity in MB of a table such as voterfile in schema public
SELECT pg_size_pretty(pg_total_relation_size('"public"."voterfile"'));

# check database size
SELECT pg_size_pretty(pg_database_size('geekdb'))

# find out number of records in a large table
SELECT reltuples::bigint AS estimate FROM pg_class where relname='<table-name>'