On 8/9/2013 10:59 AM, nandan wrote:
Hello All ;
Please help me in knowing below queries which are in Mysql to Postgresql.
1.
SELECT user,host,password
FROM mysql.user
WHERE password = '';
SET PASSWORD FOR <user>@<host> =
PASSWORD ('newpass');
postgres has no concept of user@host. host restrictions can be
implemented in the pg_hba.conf file.
ALTER ROLE user WITH PASSWORD 'newpass';
2.
SELECT user,host,password
FROM mysql.user
WHERE user = '';
DELETE FROM mysql.user
WHERE user = '';
FLUSH PRIVILEGES;
no such concept in postgres. you would drop a user with..
DROP ROLE username;
but there are no empty/null users.
3.
SELECT user,host,password
FROM mysql.user
WHERE user = 'root' AND host = '%';
DROP USER root@'%';
FLUSH PRIVILEGES;
no root user in postgres.
4.
SELECT user,host,password
FROM mysql.user
WHERE length(password) <> 41;
SET PASSWORD FOR <user>@<host> =
PASSWORD ('newpass');
not even sure why you would set all users who's password is not 41 chars
long to a specific password, so I'm not going there.
5.
Do not enable insecure password generation option
Setting can be verified by viewing the MySQL config file as per the
Recommended settings, OR by issuing the following command:
mysqladmin var | grep old_passwords
6.
Enable secure password authentication option by blocking connections from
all accounts that have passwords stored in it.
Setting can be verified by viewing the MySQL config file as per the
Recommended settings, OR by issuing the following command:
mysqladmin var | grep secure_auth
these two don't make any sense in postgres.
7.
Binary logging should be enabled to log all completed transactions, and
allow for point-in-time recovery. This can be enabled via the log-bin
status variable in the mysql configuration file. As an example, the
following entry will place all binary log files in the /var/lib/mysql/logs
directory, and use 'binlog' as the filename prefix to get binary log files
names such as binlog.000001:
log-bin=/var/lib/mysql/logs/binlog
Setting can be verified by viewing the MySQL config file as per the
Recommended settings, OR by issuing the following command:
mysqladmin var | grep log_bin
read the section on WAL Archiving in the postgres user manual, if done
correctly, this allows Point In Time Recovery.
8.
Prevent the grant statement from creating new users unless a non-empty
password is specified (v5.0.2+)
Setting can be verified by viewing the MySQL config file as per the
Recommended settings, OR by issuing the following command:
mysqladmin var | grep sql_mode
the GRANT statement doesn't create roles, CREATE ROLE (or CREATE USER) does.
9.
Do not allow new user creation by a user that does not have write access to
the mysql.user table. Note that this setting may not appear when viewing
status variables via "show variables" or "mysqladmin var", and should
therefore be validated by checking the MySQL config file.
only users/roles with the specific CREATEUSER or SUPERUSER attributes
can create/modify users.
9.
Identify and remove privileges on non-existent database objects by issuing
the following sample SQL statements:
...
nonexisting objects don't have any privileges to identify or remove.
10.
SUPER privileges can be verified by issuing the following SQL statement:
SELECT user, host,
super_priv AS 'SUPER'
FROM mysql.user
WHERE super_priv = 'Y';
easy way:
in psql comand line shell, use the \du metacommand to display all
users, note who has superuser.
harder way:
there's probably a query of the pg_catalog metadata but I'm not looking
it up.
11.
Additional global admin privileges can be reviewed by issuing the following
sample SQL statement:
SELECT user, host,
super_priv AS 'SUPER',
shutdown_priv AS 'SHUTDOWN',
process_priv AS 'PROCESS',
grant_priv AS 'GRANT',
create_user_priv AS 'CREATE_USER',
file_priv AS 'FILE',
reload_priv AS 'RELOAD',
show_db_priv AS 'SHOW_DATABASES',
lock_tables_priv AS 'LOCK_TABLES',
repl_slave_priv AS 'REPL_SLAVE',
repl_client_priv AS 'REPL_CLIENT'
FROM mysql.user
WHERE super_priv = 'Y' OR shutdown_priv = 'Y'
OR process_priv = 'Y' OR grant_priv = 'Y'
OR create_user_priv = 'Y' OR file_priv = 'Y'
OR reload_priv = 'Y' OR show_db_priv = 'Y'
OR lock_tables_priv = 'Y'
OR repl_slave_priv = 'Y'
OR repl_client_priv = 'Y';
see answer to previous. postgres is shutdown from the system shell,
not from within postgres. most of those other global privileges don't
exist on postgres, just SUPERUSER, CREATEUSER, CREATEDB attributes.
12.
SHUTDOWN privileges can be verified by issuing the following SQL statement:
no such privilege.
What is the SQL Query or command for Postgres to check the following
points?
1.No blank passwords
2.No anonymous-user accounts
3.No remotely-accessible root accounts
4.No insecure passwords
5.MySQL config file setting:
old-passwords = 0 OR
old_passwords = 0
6.MySQL config file setting:
secure-auth [= 1] OR
secure_auth [= 1]
7.MySQL config file setting:
log-bin [= /path/to/log/file-prefix] OR
log_bin [= /path/to/log/file-prefix]
8.MySQL config file setting:
sql-mode = no_auto_create_user OR
sql_mode = no_auto_create_user
9.MySQL config file setting:
safe-user-create [=1] OR
safe_user_create [=1]
10.No global access to test% databases
11.Remove obsolete privileges
thats mostly mysql specific stuff not applicable to postgres, and highly
redundant with what you asked above.
--
john r pierce 37N 122W
somewhere on the middle of the left coast
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
