Postgresql

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 databasename 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 tablename OWNER TO user_name;

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *