12.03.2015 Autor Mike 54 194

In Postgres you have to take a deeper look at the user management. You have much more options to secure it for different users than in MySql. But this comes also at a price, i think the easy way of managing access to a role is somehow lost in Postgres. So here are some commands that are important:
The commands under "psql" can be used in the shell if you open a connection with psql. The other is the equivalent for postgres for completeness the MySql command is also shown. 
 
 
To list all databases you can use these commands:
mysql: SHOW DATABASES
psql: \l
postgresql: SELECT datname FROM pgdatabase;
 
 
To list all tables in a database (connect with \c database
name in psql) you can use these commands:
 
mysql: SHOW TABLES
psql: \d
postgresql: SELECT tablename FROM informationschema.tables WHERE tableschema = 'public';
 
 
To change the owner on a table to another user:
ALTER TABLE table
name OWNER TO user_name;